Commerce 2: View all orders of a specific product?

Question

I'm looking to generate a sales report for a specific product

It should not be linked to a user's orders but just a general list of sales for that product and its variants

Is this possible?

Thanks

Answer 1

Yes it is possible but I don't think there is currently a plugin that will do it for you. There are a couple of reporting plugins in the store though I'm not sure whether they work with Commerce elements. Either way, I imagine you would need to write your own query.

There is a Commerce reporting plugin in the works from Foster Commerce but it appears to have been delayed. Going by the previous version that one should include the sort of report you want.

You could also roll your own plugin to generate the report or even, to be honest, create a Twig template which will output the data you want as a csv (or whatever).

Answer 2

For those wondering, I rolled my own.

Started off by receiving a comma separated string of product ID's

Then executed this query to the 'snapshot' json column in commerce_lineitems like so:

$fetchLineQuery = 'SELECT * FROM commerce_lineitems WHERE snapshot->"$.productId" IN ('.$productIdList.')';

$fetchLineExec = Craft::$app->db->createCommand($fetchLineQuery)->queryAll();

Then for each result, ran a foreach loop. Inside the foreach loop we call Order::find() with isComplete(), isPaid(), and also id() where the ID will be received from the foreach loop from $fetchLineExec. As a side, the order info is stored into a cache variable to make sure we don't run Order::find() multiple times for the same order.

I then put all the data I need into an array.

To export it as a CSV was fairly simple using fputcsv

I can't share the code as it was a client project, but hopefully this should be enough to get you all going.