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
- Find the
- Inside the if statement
- We are changing this line
$query .= ' ORDER BY ' . $orderField . ' ' . strtoupper($this->getOrderDirection());
' 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
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
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
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:
|id||name||quantity||quantity = 0|
|2||Great Ball||0||1 (TRUE)|
|4||Master Ball||0||1 (TRUE)|
|1||Poke Ball||100||0 (FALSE)|
|3||Ultra Ball||30||0 (FALSE)|
|5||Dive Ball||20||0 (FALSE)|
This is why we put
p.quantity = 0 ASC there to add one more sorting parameter to achieve the goal