<p>For those wondering, I rolled my own.</p>

<p>Started off by receiving a comma separated string of product ID's</p>

<p>Then executed this query to the 'snapshot' json column in commerce_lineitems like so:</p>

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

$fetchLineExec = Craft::$app-&gt;db-&gt;createCommand($fetchLineQuery)-&gt;queryAll();
</code></pre>

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

<p>I then put all the data I need into an array.</p>

<p>To export it as a CSV was fairly simple using <code>fputcsv</code></p>

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

CC BY-SA 4.0
2019-04-25T20:39:02.817Z
2019-04-26T20:54:17.473Z
2019-04-26T20:54:17.473Z
XLR
<p>Yes it is possible but I don't think there is <em>currently</em> 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.</p>

<p>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.</p>

<p>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).</p>

CC BY-SA 4.0
2019-04-24T07:46:50.560Z
2019-04-24T07:46:50.560Z
foamcow
<p>I'm looking to generate a sales report for a specific product</p>

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

<p>Is this possible?</p>

<p>Thanks</p>

<plugin-craftcommerce2>
Commerce 2: View all orders of a specific product?
# Commerce 2: View all orders of a specific product?

(Asked by: username_0 on 2019-04-19)

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

**Tags:** plugin-craftcommerce2


---

## 2 answers

### Answer by username_0 on 2019-04-25. Score: 1 | Accepted answer

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.


---

### Answer by username_1 on 2019-04-24. Score: 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).


---

