[SOLVED]Sort products by price and put out of stock products at the end of list

[Suthichai]

Hi

In addition to sort by price, product nanme, quantity....etc. I'd like to display the product list and put those with quantity equal to zero at the very end of the list.

The following code will return result set of all the products with price highest first, and all products with qty=0 are put at the end:

 

SQL

select product_name, price as price1, quantity from myproduct where quantity > 0
union all
select product_name, price as price2, quantity from myproduct where quantity <= 0
order by price1 desc

 

Return

 

Product 1 $500 15 pcs

Product 2 $400 55 pcs

Product 3 $300 22 pcs

Product 4 $200 98 pcs

Product 5 $700 0 pcs

Product 6 $200 0 pcs

 

How can this be done in Prestashop 1.5 ? Any help would be appreciated.

Edited November 10, 2012 by Suthichai (see edit history)

[Suthichai]

Just solved it.

 

In case anyone intereted, eventhogh it's not 100% perfect, but enough to solve my problem. Here it is:

 

Since product quantity can either be greater than 0 or not grater than 0,

so query for product.quantity > 0 will either return 0 or 1 (True, False).

Therefore, I have 2 columns for sorting: first order by quantity>0 and then order by name, price etc.

 

Look for file classes/Category.php, after line 658 inside function getProducts($id_lang,....)

 

change from

 

else
$sql .= ' ORDER '.(isset($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).'
LIMIT '.(((int)$p - 1) * (int)$n).','.(int)$n;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
if ($order_by == 'orderprice')
	Tools::orderbyPrice($result, $order_way);

 

to

 

else
$sql .= ' ORDER BY stock.quantity>0 desc,'.(isset($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).'
LIMIT '.(((int)$p - 1) * (int)$n).','.(int)$n;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
       /*
if ($order_by == 'orderprice')
	Tools::orderbyPrice($result, $order_way);
*/

 

Notice I comment out the line that says if ($order_by == 'orderprice'), this is because I don't know how to implement this,

have tried for hours but failed. So anyone know how this thing works, please let me know, I really like to know this.

 

and product-sort.tpl need no change.

[Cvalya]

Hello Suthichai!

 

Can you write how you solve this task?

 

What change in files you do?

 

Thx!

[outlet.ee]

Thank you for this, I've been looking quite a while for exactly that!

Edited March 23, 2013 by outlet.ee (see edit history)

[Suthichai]

I override classes/Category.php and put it in override/classes.

 

<?php

class Category extends CategoryCore
{
public function getProducts($id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $random = false, $random_number_products = 1, $check_access = true, Context $context = null)
{
  	 .....
	.....
	.....
	if ($random === true)
	{
		$sql .= ' ORDER BY RAND()';
		$sql .= ' LIMIT 0, '.(int)$random_number_products;
	}
	else
		$sql .= ' ORDER BY stock.quantity>0 desc, '.(isset($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).'
	LIMIT '.(((int)$p - 1) * (int)$n).','.(int)$n;
  $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
	/*
	if ($order_by == 'orderprice')
		Tools::orderbyPrice($result, $order_way);
	*/
	if (!$result)
		return array();

	/* Modify SQL result */
	return Product::getProductsProperties($id_lang, $result);
}
}

I've also attached the file below.

hth

Category.php

Edited April 5, 2013 by Suthichai (see edit history)

[comprausa]

Can I use this override on version 1.4.6.2?

Thx.

[outlet.ee]

I need to move 'online_only' product to the last and retain the default sort order. So whatever sort order the user chooses or is set in BO, the online only shall be the last. I changed

$sql .= ' ORDER BY '.(!empty($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.bqSQL($order_by).'` '.pSQL($order_way).'

to

$sql .= ' ORDER BY p.online_only, '.(!empty($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.bqSQL($order_by).'` '.pSQL($order_way).'

but it doesn't work. These products visibility is also set as visibility=search, so it concerns the search results sort order only.

 

How can i get this to work in PS1.6?

[winresh24]

I need to move 'online_only' product to the last and retain the default sort order. So whatever sort order the user chooses or is set in BO, the online only shall be the last. I changed

$sql .= ' ORDER BY '.(!empty($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.bqSQL($order_by).'` '.pSQL($order_way).'

to

$sql .= ' ORDER BY p.online_only, '.(!empty($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.bqSQL($order_by).'` '.pSQL($order_way).'

but it doesn't work. These products visibility is also set as visibility=search, so it concerns the search results sort order only.

 

How can i get this to work in PS1.6?

hi did you solve this one? please reply thanks

[gray]

On 11/10/2012 at 7:02 AM, Suthichai said:

Just solved it.

 

In case anyone intereted, eventhogh it's not 100% perfect, but enough to solve my problem. Here it is:

 

Since product quantity can either be greater than 0 or not grater than 0,

so query for product.quantity > 0 will either return 0 or 1 (True, False).

Therefore, I have 2 columns for sorting: first order by quantity>0 and then order by name, price etc.

 

Look for file classes/Category.php, after line 658 inside function getProducts($id_lang,....)

 

change from

 

 

else
$sql .= ' ORDER '.(isset($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).'
LIMIT '.(((int)$p - 1) * (int)$n).','.(int)$n;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
if ($order_by == 'orderprice')
	Tools::orderbyPrice($result, $order_way);

 

 

 

to

 

 

else
$sql .= ' ORDER BY stock.quantity>0 desc,'.(isset($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).'
LIMIT '.(((int)$p - 1) * (int)$n).','.(int)$n;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
       /*
if ($order_by == 'orderprice')
	Tools::orderbyPrice($result, $order_way);
*/

 

 

 

Notice I comment out the line that says if ($order_by == 'orderprice'), this is because I don't know how to implement this,

have tried for hours but failed. So anyone know how this thing works, please let me know, I really like to know this.

 

and product-sort.tpl need no change.

 

Any ideas of how to integrate this into PS1.7.6.3

Thanks in advance.

 

[jamshidpour]

Thanks a lot Suthichai for sharing the solution.

I have tested your solution on PS 1.6 but it didn't work.

Can anybody guide me to use this solution in PS 1.6 ? 

[AddWeb Solution]

On 9/25/2021 at 3:42 PM, jamshidpour said:

Thanks a lot Suthichai for sharing the solution.

I have tested your solution on PS 1.6 but it didn't work.

Can anybody guide me to use this solution in PS 1.6 ? 

Hi @jamshidpour,

Goto Preferences -> Products -> and Scroll to the Pagination section 

Change Default order by value to `Product Quantity` and Default order method value to `Descending`.

Save it once you made above changes.

I hope it will help.

[AddWeb Solution]

For future reference,

In Prestashop 1.7

• Goto Shop Parameters -> Product Settings -> scroll down to Pagination section,
• Change Default order by value to `Product Quantity` and Default order method value to `Descending` (Ref img)
• Save it once you made above changes.

I hope this will help.