mysql - 将复杂的 mysql 查询添加到 Yii2 中的搜索模型

Question

我想在yii2中使用默认的搜索和分页。但是查询很复杂，我不知道如何将它添加到搜索模型中!这是查询:

SELECT p.*,po_sum,rpo_sum,so_sum
FROMproduct p

LEFT JOIN (
SELECT id,product_id , IF(sum(quantity) IS NULL, 0, sum(quantity))  AS po_sum
FROM purchase_order_products inner join purchase_order on purchase_order.id = purchase_order_products.purchase_order_id
Where purchase_order.status = 'Approved'
GROUP BY product_id )
subcount ON p.id = subcount.product_id

LEFT JOIN (
SELECT id,product_id , sum(quantity)  AS rpo_sum
FROM return_purchase_order_products inner join return_purchase_order on return_purchase_order.id = return_purchase_order_products.purchase_order_id Where return_purchase_order.status = 'Approved'
GROUP BY product_id )
subcount2 ON p.id = subcount2.product_id

LEFT JOIN (
SELECT product_id , sum(quantity_ordered)  AS so_sum
FROM sales_order_item inner join sales_order on sales_order.id = sales_order_item.sales_order_id Where sales_order.order_status = 'complete'
GROUP BY product_id )
subcount3 ON p.id = subcount3.product_id
order by  po_sum DESC,rpo_sum DESC

有什么帮助吗？

Answer 1

如果您使用 MySql >= 5.7.7，最简单的方法是使用该查询创建一个 View 并在 tableName 方法中使用它。

您需要该版本的 MySql，因为您不能在以前版本的 View 创建期间在 from 子句中使用子查询。