Jump to content

[SOLVED]How to: shows in stock products first


Cuneyt Oral

Recommended Posts

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 by Cuneyt Oral (see edit history)
Link to comment
Share on other sites

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;
        }

 

  • Like 1
Link to comment
Share on other sites

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());
        }

 

 

  • Like 1
Link to comment
Share on other sites

  • Cuneyt Oral changed the title to [SOLVED]How to: shows in stock products first
  • 1 year later...
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());
        }

  • Like 2
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...