Developer forum

Hi Nuno,

One way is to execute a sql query from some custom code, though I don't know if this is the approach you're seeking.

SELECT
	*
FROM
(
	SELECT
		OrderLineProductID, 
		OrderLineProductVariantID,
		OrderLanguageID,
		SUM(OrderLineQuantity) AS QuantitySum
	FROM
		EcomOrderLines INNER JOIN EcomOrders
		ON OrderLineOrderID = OrderID
	WHERE
		OrderLineProductID <> ''
		AND OrderShopID = 'SHOP1'
	GROUP BY
		OrderLineProductID,
		OrderLineProductVariantID,
		OrderLanguageID
) AS TempTable1
WHERE
	QuantitySum > 0
ORDER BY
	QuantitySum DESC

Changing the ShopID to the relevant one, this should produce the list you're looking for. Depending on the size of the EcomOrderLines table, you may need to to tweak some indexes, though this query executed fairly quickly on the couple of test sites I tried it on.

- Jeppe

Hi Jeppe,

Thanks a lot. It's a good start. I will still need to filter by product groups but it seems like UI / Smart Searches won't give me what I need unfortunately.

Best Regards,

Nuno

I don't think SmartSearch definitions are granular enough for that task.

Here's an updated sql query:

SELECT
    *
FROM
(
    SELECT
        OrderLineProductID, 
        OrderLineProductVariantID,
        OrderLanguageID,
	GroupProductRelationGroupID,
        SUM(OrderLineQuantity) AS QuantitySum
    FROM
        EcomOrderLines
		INNER JOIN EcomOrders
			ON OrderLineOrderID = OrderID
		INNER JOIN EcomGroupProductRelation
			ON OrderLineProductID = GroupProductRelationProductID
    WHERE
        OrderLineProductID <> ''
        AND OrderShopID = 'SHOP1'
	AND GroupProductRelationGroupID = 'GROUP1'
    GROUP BY
        OrderLineProductID,
        OrderLineProductVariantID,
        OrderLanguageID,
	GroupProductRelationGroupID
) AS TempTable1
WHERE
    QuantitySum > 0
ORDER BY
    QuantitySum DESC

Hi Jeppe,

Thanks a lot. It's too bad Smart Searched don't quite cut it right now. Hopefully in the future.

Best Regards,

Nuno Aguiar