Jump to content

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


Recommended Posts

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 by Suthichai (see edit history)

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites
  • 4 months later...
  • 2 weeks later...

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 by Suthichai (see edit history)
  • Like 1

Share this post


Link to post
Share on other sites
  • 6 months later...
  • 2 years later...

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?

Share this post


Link to post
Share on other sites
  • 9 months later...

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

Share this post


Link to post
Share on other sites
  • 3 years later...
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.

 

  • Like 1

Share this post


Link to post
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...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More