[Product list] putting out of stock products at the end

[Rulian]

Hi,

I currently have a PS shop where the products are sorted by 'reference DESC' by default.

I'd like to bring out of stock products at the end of the result list, may it be from a search query, or a category listing.

 

I took a look at the SQL, and one of the solution would be to duplicate the SQL query, making an UNION.

 

Somthing like:

select p.* ... from ps_product where quantity > 0 order by reference DESC
UNION ALL 
select p.* ... from ps_product where quantity = 0 order by reference DESC

...like described here: https://www.prestashop.com/forums/topic/201563-solvedsort-products-by-price-and-put-out-of-stock-products-at-the-end-of-list/

 

I'm a little bit concerned by performance, I'm not a "SQL master", so is there any SQL trick in order to avoid the UNION, and keep a single SQL statement ?

 

Thank you in advance.

 

 

[musicmaster]

I don't know for sure, but it would work if you could find a Mysql operator that reduces the quantity to a binary value: either 1 or zero.

 

In that case you could write: Order by binary_quantity DESC, reference.