How to Sort and Place Out of Stock Item to The Last in Category Page

Sometime if you browse the category page when you have so many out of stock items, there may be a lot of out of stock products are dominate the page and this looks bad to a customer perhaps.

Let’s see how can we place them to the last page.

Please note the below

• For now, the only way to achieve this is to modify the offical module Faceted Search. Please ONLY do this if you have coding concept
• The modification may be removed upon updating PrestaShop or the Faceted Search Module

1. Open modules/ps_facetedsearch/src/Adapter/MySQL.php
2. Find the getQuery() function
3. Inside the if statement if ($orderField)
4. We are changing this line $query .= ' ORDER BY ' . $orderField . ' ' . strtoupper($this->getOrderDirection());
5. Change ' ORDER BY ' to ' ORDER BY p.quantity = 0 ASC, '

Below we will talk about the details

You may have you own search provider in your shop, if you have written your own search module or bought from the marketplace. In this case, the solution introduce here may not be helpful for you.

However, in default and commonly, the offical search provider Faceted Search module is used and we are modifying it.

As you may know that, in category page, there are few sorting sequence for customer to choose, which are highlighted on the above picture.

In terms of programming, each of the options is a SortOrder class, which you can take the below file in your shop as a reference

src/Core/Product/Search/SortOrder.php

In short, it is a class for recording the sorting field and the ordering direction for each sorting option

You may notice that, ONE SortOrder is for ONE sorting option only and ONE sorting option is based on ONE field only.

But what we would like to do now, is to place out of stock products to the last and sort the remain products with selected order, where 2 field (quantity & other field) need to be used.

Basically, every searching action is actually executing a SQL statement to retrieve data from database. Therefore, what we are going to do is to find out that SQL in the php files and change it directly, which we found that it is in modules/ps_facetedsearch/src/Adapter/MySQL.php

We will mainly discuss about how to adjust the ORDER BY to achieve the goal

Consider the below example table:

SELECT * FROM balls

If we want ​to sort by quantity decreasingly, as you may know:

SELECT * FROM balls ORDER BY quantity DESC

But what if we put a logical statement in ORDER BY?

SELECT * FROM balls ORDER BY quantity = 0 DESC

What the statement doing is to apply the expression to each value of that column first, then sort it. So that:

This is why we put p.quantity = 0 ASC there to add one more sorting parameter to achieve the goal