Trying to sort products by price.

{% set products = craft.commerce.products({
  type:  'footwear',
  limit: shopSettings.productsPerPage,
  order: 'defaultPrice asc',
}) %} 

It works fine.

Please tell me how to sort products by Sale price?


  • I presume you have you tried just order: salePrice asc? Commented Aug 3, 2017 at 2:17
  • Using "order: salePrice asc" I get an error: CDbException CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'salePrice' in 'order clause'. link
    – Oleg
    Commented Aug 3, 2017 at 5:57

2 Answers 2


What about this:

{# Catch your products as you wish, here is just an example #}
{% set categoryProducts = craft.products().relatedTo({
    targetElement: currentCategory,
    field: 'productCategory'
}).limit(10).all() %}
{# Magic goes here #}
{% for product in categoryProducts|sort((a, b) => a.cheapestVariant.salePrice <=> b.cheapestVariant.salePrice) %}
    {% set salePrice = product.cheapestVariant.salePrice %}
    {{ salePrice }}
{% endfor %}

For DESC output just change to:

b.cheapestVariant.salePrice <=> a.cheapestVariant.salePrice
  • Make sense. But only for current page or for 1st 10 products.
    – Oleg
    Commented Nov 27, 2022 at 16:08
  • Why can't it be used on all products? Because of bad optimization? In any case, salePrice is not used as a ready-made value from the table and is always calculated at execution runtime.
    – RomanAvr
    Commented Nov 27, 2022 at 18:29

Any update on this one? Is it possible to sort the products based on the (default variant) salePrice instead of the (default variant) defaultPrice?

Thank you

  • Answer from Craft support (2017): Hi олег, salePrice is dynamically calculated after the products/variants have been retrieved from the database, which makes sense it is dependant on user, order, and a date-time context. This means ordering in the database query for salePrice is currently not possible. You would need to sort after getting the results. You could look at using a plugin like this to do so: github.com/victor-in/Craft-TwigBetterSort if the default twig sort doesn't work for you. Hope that helps. -Luke
    – Oleg
    Commented Nov 21, 2022 at 17:04
  • See also craftcms.stackexchange.com/questions/14917/…
    – Oleg
    Commented Nov 21, 2022 at 17:05

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.