<p>Have you considered the <a href="https://github.com/craftcms/element-api" rel="nofollow noreferrer">Element API plugin</a> as a in-between solution? I don’t know how it managed to take less ressources (if so) - or which data you need to use/search - but we use this plugin to search in large entries selection (not 4K, but still) and it works well.</p>

CC BY-SA 4.0
2019-01-21T20:54:53.240Z
Yes, have looked at the Elements API plugin... but trying hard to reduce the number of plugins used in the project... Really looking to add them to our own plugin...

But will keep it in mind for last resort...
Paul Eeles
CC BY-SA 4.0
2019-01-23T09:21:54.867Z
UPDATE: Took your advice and used the Elements API - now have the same query bashed out in 1 minute 7 seconds... 

Which is still unacceptable to clients viewing the website... 

There has to be another way for craft.products.hasVariant({hasSales: true}) on large amounts of data... 

Otherwise, this really does expose a big flaw with Craft Commerce not being able to handle large datasets.
Paul Eeles
CC BY-SA 4.0
2019-01-24T20:34:39.627Z
Good to know! Feel free to give some love to my answer if you find it useful to your issue!
Maxime Lafrenière
CC BY-SA 4.0
2019-01-24T21:26:50.957Z
unveiled some flaws with large data sets... but certainly pushed me in the right direction... 

as we're both new can't upvote :( 

But many thanks for the push to dig deeper!
Paul Eeles
CC BY-SA 4.0
2019-01-31T17:08:42.800Z
We had to use Element API for a similar project, we use the cache parameter. It boosts the speed like hell. You can think about that too! Maybe purge/warm up the cache with a task on every night or so!
Maxime Lafrenière
CC BY-SA 4.0
2019-01-21T16:53:23.133Z
2019-01-21T16:53:23.133Z
Maxime Lafrenière
<p>Okay for anyone who runs into issues with Craft Commerce and has large amounts of products (over 4K) with 8K's worth of variants...</p>

<p>You'll need to create a plugin and execute a function in the variables.php like so...</p>

<pre><code>public function getClearanceProductIds() {
      $rows = (new \craft\db\Query())
      -&gt;select('craft_commerce_variants.productId')
      -&gt;from('craft_commerce_sale_purchasables')
      -&gt;join('JOIN','craft_commerce_variants','craft_commerce_variants.id = craft_commerce_sale_purchasables.purchasableId')
      -&gt;where(array(
        'like',
        'craft_commerce_sale_purchasables.saleId',
        '2'
      ))
      -&gt;column();

      $rows = implode(",", $rows);

      return $rows;

    }
</code></pre>

<p>And then query the products in the normal way with the ids array</p>

<pre><code>{% set products   = craft.products.id(saleItems).all() %}
</code></pre>

<p>Otherwise, every your resources and CPU will overload by doing things the craft way i.e.</p>

<pre><code>{% set products = craft.products.hasVariant({hasSales: true}).all() %}
</code></pre>

<p>I hope this helps someone with a similar issue!</p>

CC BY-SA 4.0
2019-01-24T10:26:11.420Z
2019-01-24T22:29:21.227Z
2019-01-24T22:29:21.227Z
Paul Eeles
<p>Currently working on an e-commerce site, which currently has over 4000 products with a mass of variants (averaging 3 variants per product) - and hit a few hurdles on the way...</p>

<p>At present, craft.products.hasVariant({hasSales: true}) takes over 4 minutes to return the data of items on sale. And again running a product count against the brand (category) is a similar issue.</p>

<p>Now I understand the sheer amount of queries for the above is getting a little ridiculous around 300 are showing in the Logs each time this is run - so running these queries from twig is next to impossible with the overheads.</p>

<p>So now turned to use a plugin with a public function in order to try and ease the pressure from the front end...</p>

<p>Anyone any ideas on how get the same results with a craft\db\Query...</p>

<p>Thanks in advance...</p>

<p>I hope this makes sense...</p>

<plugin-development><craft3><plugin-craftcommerce2>
DB Queries in Craft Commerce 2 with large data
# DB Queries in Craft Commerce 2 with large data

(Asked by: username_0 on 2019-01-21)

Currently working on an e-commerce site, which currently has over 4000 products with a mass of variants (averaging 3 variants per product) - and hit a few hurdles on the way...

At present, craft.products.hasVariant({hasSales: true}) takes over 4 minutes to return the data of items on sale. And again running a product count against the brand (category) is a similar issue.

Now I understand the sheer amount of queries for the above is getting a little ridiculous around 300 are showing in the Logs each time this is run - so running these queries from twig is next to impossible with the overheads.

So now turned to use a plugin with a public function in order to try and ease the pressure from the front end...

Anyone any ideas on how get the same results with a craft\db\Query...

Thanks in advance...

I hope this makes sense...

**Tags:** plugin-development, craft3, plugin-craftcommerce2

---

## 2 answers

### Answer by username_1 on 2019-01-21. Score: 1 | Accepted answer

Have you considered the [Element API plugin](https://github.com/craftcms/element-api) as a in-between solution? I don’t know how it managed to take less ressources (if so) - or which data you need to use/search - but we use this plugin to search in large entries selection (not 4K, but still) and it works well.

> **Comments:**
> **username_0**: Yes, have looked at the Elements API plugin... but trying hard to reduce the number of plugins used in the project... Really looking to add them to our own plugin...  But will keep it in mind for last resort...  
> **username_0**: UPDATE: Took your advice and used the Elements API - now have the same query bashed out in 1 minute 7 seconds...   Which is still unacceptable to clients viewing the website...   There has to be another way for craft.products.hasVariant({hasSales: true}) on large amounts of data...   Otherwise, this really does expose a big flaw with Craft Commerce not being able to handle large datasets.  
> **username_1**: Good to know! Feel free to give some love to my answer if you find it useful to your issue!  
> **username_0**: unveiled some flaws with large data sets... but certainly pushed me in the right direction...   as we're both new can't upvote :(   But many thanks for the push to dig deeper!  
> **username_1**: We had to use Element API for a similar project, we use the cache parameter. It boosts the speed like hell. You can think about that too! Maybe purge/warm up the cache with a task on every night or so!  

---

### Answer by username_0 on 2019-01-24. Score: 2

Okay for anyone who runs into issues with Craft Commerce and has large amounts of products (over 4K) with 8K's worth of variants...
You'll need to create a plugin and execute a function in the variables.php like so...
public function getClearanceProductIds() {
      $rows = (new \craft\db\Query())
      ->select('craft_commerce_variants.productId')
      ->from('craft_commerce_sale_purchasables')
      ->join('JOIN','craft_commerce_variants','craft_commerce_variants.id = craft_commerce_sale_purchasables.purchasableId')
      ->where(array(
        'like',
        'craft_commerce_sale_purchasables.saleId',
        '2'
      ))
      ->column();

      $rows = implode(",", $rows);

      return $rows;

    }

And then query the products in the normal way with the ids array
{% set products   = craft.products.id(saleItems).all() %}

Otherwise, every your resources and CPU will overload by doing things the craft way i.e.
{% set products = craft.products.hasVariant({hasSales: true}).all() %}

I hope this helps someone with a similar issue!


---

