<p>What about this:</p>
<pre><code>{# 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) =&gt; a.cheapestVariant.salePrice &lt;=&gt; b.cheapestVariant.salePrice) %}
    {% set salePrice = product.cheapestVariant.salePrice %}
    {{ salePrice }}
{% endfor %}
</code></pre>
<p>For DESC output just change to:</p>
<pre><code>b.cheapestVariant.salePrice &lt;=&gt; a.cheapestVariant.salePrice
</code></pre>

CC BY-SA 4.0
2022-11-27T16:08:05.707Z
Make sense. But only for current page or for 1st 10 products.
Oleg
CC BY-SA 4.0
2022-11-27T18:29:41.523Z
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
CC BY-SA 4.0
2022-11-26T15:40:23.127Z
2022-11-26T15:40:23.127Z
RomanAvr
<p>Any update on this one? Is it possible to sort the products based on the (default variant) <strong>salePrice</strong> instead of the (default variant) <strong>defaultPrice</strong>?</p>
<p>Thank you</p>

CC BY-SA 4.0
2022-11-21T17:04:54.777Z
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: https://github.com/victor-in/Craft-TwigBetterSort if the default twig sort doesn't work for you.

Hope that helps.

-Luke
Oleg
CC BY-SA 4.0
2022-11-21T17:05:15.287Z
See also https://craftcms.stackexchange.com/questions/14917/craft-commerce-sort-products-by-price
Oleg
CC BY-SA 4.0
2022-10-27T13:12:49.253Z
2022-10-27T13:13:11.423Z
2022-10-27T13:13:11.423Z
Thomas Goemaere
<p>Trying to sort products by price. </p>

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

<p>It works fine.</p>

<p>Please tell me how to sort products by <strong>Sale</strong> price?</p>

<p>Thanks.</p>

CC BY-SA 3.0
2017-08-03T02:17:13.140Z
I presume you have you tried just `order: salePrice asc`?
Jeremy Daalder
CC BY-SA 3.0
2017-08-03T05:57:42.823Z
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](https://pageshot.net/qurnTPyhrYVN27Ym/originals.dev)
Oleg
<plugin-craftcommerce><ordering><order><sort>
Craft Commerce: Sort products by Sale price
# Craft Commerce: Sort products by Sale price

(Asked by: username_0 on 2017-07-30)

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

**Tags:** plugin-craftcommerce, ordering, order, sort
> **Comments:**
> **username_1**: I presume you have you tried just `order: salePrice asc`?  
> **username_0**: 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](https://pageshot.net/qurnTPyhrYVN27Ym/originals.dev)  

---

## 2 answers

### Answer by username_2 on 2022-11-26. Score: 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

> **Comments:**
> **username_0**: Make sense. But only for current page or for 1st 10 products.  
> **username_2**: 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.  

---

### Answer by username_3 on 2022-10-27. Score: 1

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

> **Comments:**
> **username_0**: 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: https://github.com/victor-in/Craft-TwigBetterSort if the default twig sort doesn't work for you.  Hope that helps.  -Luke  
> **username_0**: See also https://craftcms.stackexchange.com/questions/14917/craft-commerce-sort-products-by-price  

---

