CakePHP 3.x - SUM() on ManyToMany

Question

I am getting stuck on using SQL functions queries made in CakePHP 3 in combinations with associations.

The situation is as follows: I have three tables, a 'products' table, an 'orders' table and a join table called 'orders_products'.

In the index of OrdersController I would like to add the total price (= sum of relevant product prices) to the table of orders. In SQL this exactly can be done with the following query:

SELECT orders.id, SUM(products.price)
FROM orders
LEFT JOIN orders_products
ON orders.id = orders_products.order_id
LEFT JOIN products
ON orders_products.product_id = products.id
GROUP BY orders.id;

I figured to following controller code should do the trick:

$orders = $this->Orders->find('all')->contain(['Products']);

$orders
        ->select(['total_price' => $orders->func()->sum('Products.price')])
        ->group('Orders.id');

However, when the query object is executed, I get an error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Products.price' in 'field list'

...Even though the association between orders and products is defined.

Calling only $orders = $this->Orders->find('all')->contain(['Products'])->all(); does return an array of orders with each order a number of products, the model has to be set up correctly. Any ideas what might be wrong? Thanks in advance!

From OrdersTable:

$this->belongsToMany('Products', [
    'foreignKey' => 'order_id',
    'targetForeignKey' => 'product_id',
    'joinTable' => 'orders_products'
]);

And from ProductsTable:

    $this->belongsToMany('Orders', [
        'foreignKey' => 'product_id',
        'targetForeignKey' => 'order_id',
        'joinTable' => 'orders_products'
    ]);

score 3 · Accepted Answer · answered Aug 13 '16 at 22:56

3

One way to do it:

$orders = $this->Orders->find()
    ->select([
        'order_id' =>'orders.id',
        'price_sum' => 'SUM(products.price)'
     ])
    ->leftJoin('orders_products', 'orders.id = orders_products.order_id'),
    ->leftJoin('products', 'orders_products.product_id = products.id')
    ->group('orders.id');

answered Aug 13 '16 at 22:56

bill

1,646
1
18
27

Thanks, this works. However, isn't it strange you would manually use the join table? Isn't this what the `->contain` function and the models are for? I mean, the join table and relevant fields have been specified. – Roberto Aug 14 '16 at 09:58
Depending on the relation between tables, a `contain` may cause a join, or it may cause a second query to be issued after the first one completes. In the latter case, you have to force the join if your query relies on all tables being referenced in the same query. – Greg Schmidt Aug 14 '16 at 17:53
Thank you for clarifying. It still seems a bit arbitrary which approach is necessary though. – Roberto Aug 14 '16 at 18:37
1

As of CakePHP 3.1 you could [**use `leftJoinWith()`**](https://book.cakephp.org/3.0/en/orm/query-builder.html#using-leftjoinwith), which can simpy be fed with the association alias only, and supports query callbacks similar to `contain()`. – ndm Feb 15 '17 at 17:02

CakePHP 3.x - SUM() on ManyToMany

1 Answers1

Linked