Propel2; how to use querybuilder for subselect query

Question

Problem:

I'm having trouble finding a solution building a query with QueryBuilder (perhaps getting it done with regular sql query first will help):

Trying to retrieve all customers for a user (has shop credits at one of the shops user is linked to), need the total credits (sum of credits at shops belonging to that user) as virtual column (to be able to order on), using paginate().

Database structure:

Table customers

  id           email       other_fields
   1        1@email.com        f
   2        2@email.com        o
   3        3@email.com        o

Table users

  id           email       other_fields
   1        1@user.com        b
   2        2@user.com        a
   3        3@user.com        r

Table shops

  id           name       other_fields
   1          Shop 1        m
   2          Shop 1        o
   3          Shop 1        o

Table user_shops

  user_id     shop_id
   1             1       
   1             2       
   3             3

Table customer_shop_credits

  customer_id        shop_id       credits
   1                   1             55
   1                   2             45
   2                   2             3
   3                   3             44

Expected result:

When retrieving customers for user 1, I'd expect to get back customer 1 with 100 credits and customer 2 with 3 credits

Closest I got:

$credits_query = CustomerShopCreditQuery::create()
        ->useShopQuery()
            ->useUserShopQuery()
                ->filterByUserId($user->getId())
            ->endUse()
        ->endUse()
        ;


    $customers = CustomerQuery::create()
        ->addSelectQuery($credits_query, 'credits_alias', false)
        ->useCustomerShopCreditQuery()
            ->useShopQuery()
                ->useUserShopQuery()
                    ->filterByUserId($user->getId())
                ->endUse()
            ->endUse()
        ->endUse()
        ->withColumn('sum(credits_alias.credits)', 'credits')
        ->groupById()
        ->orderBy($order_by_column, $direction)
        ->paginate($page, $page_size);

Which results in the following query:

SELECT customers.id, customers.email, sum(credits_alias.credits) AS credits 
FROM customers 
CROSS JOIN (
    SELECT customer_shop_credits.id, customer_shop_credits.customer_id, customer_shop_credits.shop_id, customer_shop_credits.credits 
    FROM customer_shop_credits 
    INNER JOIN shops ON (customer_shop_credits.shop_id=shops.id) 
    INNER JOIN user_shops ON (shops.id=user_shops.shop_id) 
    WHERE user_shops.user_id=159
    ) AS credits_alias 
INNER JOIN customer_shop_credits ON (customers.id=customer_shop_credits.customer_id) 
INNER JOIN shops ON (customer_shop_credits.shop_id=shops.id) 
INNER JOIN user_shops ON (shops.id=user_shops.shop_id) 
WHERE user_shops.user_id=159 
GROUP BY customers.id 
ORDER BY customers.id DESC 
LIMIT 25

But gives me results with wrong sum of credits. Not to sure about the CROSS JOIN. When I edit this query and make it a JOIN and use ON (credits_alias.customer_id = customers.id) as a condition, the sum of credits is better, but seems to have the classic join problem of doubling the sum

Propel2; how to use querybuilder for subselect query

0 Answers0