Skip to content

Issue with implementing unique_purchases column in main table using leftJoin in Grid ResourceModel

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?