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

2 Answers 2


Have you considered the Element API plugin 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.

  • 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
    Commented Jan 21, 2019 at 20:54
  • 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
    Commented Jan 23, 2019 at 9:21
  • Good to know! Feel free to give some love to my answer if you find it useful to your issue! Commented Jan 24, 2019 at 20:34
  • 1
    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
    Commented Jan 24, 2019 at 21:26
  • 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! Commented Jan 31, 2019 at 17:08

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())
      ->join('JOIN','craft_commerce_variants','craft_commerce_variants.id = craft_commerce_sale_purchasables.purchasableId')

      $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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.