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

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.

TL;DR

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

Search Provider

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.

SortOrder class

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

Problem Faced

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.

Solution

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

The SQL Statement

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

Consider the below example table:

SELECT * FROM balls

id name quantity
1 Poke Ball 100
2 Great Ball 0
3 Ultra Ball 30
4 Master Ball 0
5 Dive Ball 20

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

SELECT * FROM balls ORDER BY quantity DESC

id name quantity
1 Poke Ball 100
3 Ultra Ball 30
5 Dive Ball 20
2 Great Ball 0
4 Master Ball 0

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

SELECT * FROM balls ORDER BY quantity = 0 DESC

id name quantity
2 Great Ball 0
4 Master Ball 0
1 Poke Ball 100
3 Ultra Ball 30
5 Dive Ball 20

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



Start typing and press Enter to search