I have a SQL query that does what I need
SELECT ipi.*, COALESCE(unique_purchases, 0) AS unique_purchases FROM mst_inventory_planner_inventory AS ipi LEFT JOIN ( SELECT product_id, COUNT(DISTINCT order_id) AS unique_purchases FROM sales_order_item GROUP BY product_id ) AS soi ON ipi.product_id = soi.product_id;
I have trouble implementing an equivalent in Grid ResourceModel.
I tried using another join as a reference, but it didn’t work
customers_subscribed join
)->joinLeft(
['pas' => $this->getTable('product_alert_stock')],
'main_table.product_id=pas.product_id',
[
'customers_subscribed' => new Zend_Db_Expr("COUNT(pas.customer_id)"),
]
my attempt to do the same for unique_purchases
)->joinLeft(
['soi' => $this->getTable('sales_order_item')],
'main_table.product_id = soi.product_id',
['unique_purchases' => new Zend_Db_Expr('COUNT(DISTINCT soi.order_id)')]
The site and grid stopped working after this. What am I doing wrong?