Q:

Add complex mysql query to seach model in Yii2

I want to use the default search and pagination in yii2.  But the query is complex and I don't know how can I add it to the search model!  This is the query:
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

Any help? 

A:

If you use MySql >= 5.7.7 the easiest way is to create a view with that query and use it in the tableName method. 
You need that version of MySql because you cant use subquery in from clause during view creation in previous versions.