CakePHP 3.x-ManyToMany上的SUM() [英] CakePHP 3.x - SUM() on ManyToMany

问题描述

我被困在将CakePHP 3中进行的SQL函数查询与关联结合使用的情况.

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

情况如下:我有三个表，一个产品"表，一个订单"表和一个称为"orders_products"的联接表.

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

在OrdersController的索引中，我想将总价格(=相关产品价格的总和)添加到订单表中.在SQL中，这完全可以通过以下查询完成:

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:

错误:SQLSTATE [42S22]:找不到列:1054未知列 字段列表"中的"Products.price"

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

...即使定义了订单和产品之间的关联.

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

仅调用$orders = $this->Orders->find('all')->contain(['Products'])->all();确实会返回一个订单数组，每个订单都有许多产品，因此必须正确设置模型.任何想法可能有什么问题吗?预先感谢！

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!

来自OrdersTable:

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

并且来自ProductsTable:

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

推荐答案

一种方法:

$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');

这篇关于CakePHP 3.x-ManyToMany上的SUM()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！