[SOLVED]How to: shows in stock products first

[Cuneyt Oral]

Hi,

I want to show in Stock products first and then out of stocks on CATEGORY LISTS

I think, if I can add the following line to the SQL line that's enough. but I don't know which files I need to change?

ORDER BY (CASE WHEN ps_product.quantity > 0 THEN 1 ELSE 0 END) DESC, ......... Prestashop's standard SQL string will continue

 

 

Edited February 13, 2021 by Cuneyt Oral (see edit history)

[Cuneyt Oral]

I replaced SQL string in the Category.php (line 1048) like below, and It worked but this change effected only featured products list on HOME PAGE and featured product list on PRODUCT page. How can I list in stocks products first and then out of stock products in the category product list?

		...
		LEFT JOIN `' . _DB_PREFIX_ . 'stock_available` sa ON (p.`id_product` = sa.`id_product`) 
		...
		if ($random === true) {
            $sql .= ' ORDER BY (CASE WHEN sa.quantity > 0 THEN 1 ELSE 0 END) DESC, RAND() LIMIT ' . (int) $randomNumberProducts;
        } else {
            $sql .= ' ORDER BY (CASE WHEN sa.quantity > 0 THEN 1 ELSE 0 END) DESC, ' . (!empty($orderByPrefix) ? $orderByPrefix . '.' : '') . '`' . bqSQL($orderyBy) . '` ' . pSQL($orderWay) . '
            LIMIT ' . (((int) $p - 1) * (int) $n) . ',' . (int) $n;
        }

 

[Cuneyt Oral]

Finally I found the correct SQL string location. Maybe someone wants to know. Now, In stock products are always visible front pages and out of stock products are always visible back pages in my shop.

I changed the below line in the \modules\ps_facetedsearch\src\Adapter\MYSQL.php line 129.

Orginal:

        if ($orderField) {
            $query .= ' ORDER BY ' . $orderField . ' ' . strtoupper($this->getOrderDirection());
        }

Modified:

        if ($orderField) {
            $query .= ' ORDER BY (CASE WHEN quantity > 0 THEN 1 ELSE 0 END) DESC, ' . $orderField . ' ' . strtoupper($this->getOrderDirection());
        }

 

 

[Richman]

On 2/13/2021 at 11:37 AM, Cuneyt Oral said:

Modified:

        if ($orderField) {
            $query .= ' ORDER BY (CASE WHEN quantity > 0 THEN 1 ELSE 0 END) DESC, ' . $orderField . ' ' . strtoupper($this->getOrderDirection());
        }

In PS 1.7.8 it must be modified to

Modified:

        if ($orderField) {
            $query .= ' ORDER BY (CASE WHEN p.quantity > 0 THEN 1 ELSE 0 END) DESC, ' . $orderField . ' ' . strtoupper($this->getOrderDirection());
        }