[SOLVED] Problem with sorting multiple fields in category

[pskz]

I want to sort products in category by default filters (like: position, price...) and by quantity. Products with quantity = 0 must be on the end of the list.

 

Default query in Category.php (function getProducts() - line 688, presta 1.6.1.x)

 

I'm trying to add quantity to ORDER BY, but results are sorted only by position

if ($random === true) {
	$sql .= ' ORDER BY RAND(), stock.`quantity` DESC LIMIT '.(int)$random_number_products;
} else {
	$sql .= ' ORDER BY '.(!empty($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.bqSQL($order_by).'` '.pSQL($order_way).', stock.`quantity` DESC
	LIMIT '.(((int)$p - 1) * (int)$n).','.(int)$n;
}

I'm trying to change this query by adding subquery for avaliable_stock, but results are the same, sorted only by position

$sql = 'SELECT p.*, product_shop.*, real_quantity.*'.(Combination::isFeatureActive() ? ', IFNULL(product_attribute_shop.id_product_attribute, 0) AS id_product_attribute,
			product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').', pl.`description`, pl.`description_short`, pl.`available_now`,
			pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, image_shop.`id_image` id_image,
			il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default,
			DATEDIFF(product_shop.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00",
			INTERVAL '.(int)$nb_days_new_product.' DAY)) > 0 AS new, product_shop.price AS orderprice
		FROM `'._DB_PREFIX_.'category_product` cp
		LEFT JOIN `'._DB_PREFIX_.'product` p
			ON p.`id_product` = cp.`id_product`
		'.Shop::addSqlAssociation('product', 'p').
		(Combination::isFeatureActive() ? ' LEFT JOIN `'._DB_PREFIX_.'product_attribute_shop` product_attribute_shop
		ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop='.(int)$context->shop->id.')':'').'
		LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
			ON (product_shop.`id_category_default` = cl.`id_category`
			AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
		LEFT JOIN `'._DB_PREFIX_.'product_lang` pl
			ON (p.`id_product` = pl.`id_product`
			AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').')
		LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop
			ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop='.(int)$context->shop->id.')
		LEFT JOIN `'._DB_PREFIX_.'image_lang` il
			ON (image_shop.`id_image` = il.`id_image`
			AND il.`id_lang` = '.(int)$id_lang.')
		LEFT JOIN `'._DB_PREFIX_.'manufacturer` m
			ON m.`id_manufacturer` = p.`id_manufacturer`
		LEFT JOIN (SELECT * FROM `'._DB_PREFIX_.'stock_available` ORDER BY `quantity` DESC) AS real_quantity
			ON (real_quantity.id_product = p.id_product AND real_quantity.id_product_attribute = 0 AND real_quantity.id_shop = '.(int)$context->shop->id.')
		WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
			AND cp.`id_category` = '.(int)$this->id
			.($active ? ' AND product_shop.`active` = 1' : '')
			.($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
			.($id_supplier ? ' AND p.id_supplier = '.(int)$id_supplier : '');

if ($random === true) {
	$sql .= ' ORDER BY RAND(), real_quantity.`quantity` DESC LIMIT '.(int)$random_number_products;
} else {
	$sql .= ' ORDER BY '.(!empty($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.bqSQL($order_by).'` '.pSQL($order_way).', real_quantity.`quantity` DESC
	LIMIT '.(((int)$p - 1) * (int)$n).','.(int)$n;
}

Can anyone help/advise me with this problem?

Edited December 5, 2016 by pskz (see edit history)

[Martin Uker K]

I did try this once! Jsut a notice : Try disabling LayeredNavigation. This module bugged me out of my development. I was trying to develops a stable the "On Sale First" in the listing. Never worked because of LayeredNavigation.

 

I still don't know where his cache is or how it retrieve the products. I don't want ot touch such an important module or override it, sounds like a bad pitfall during an update.

 

Martin.

[pskz]

Layered module is off

[Martin Uker K]

This code :

'.(!empty($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.bqSQL($order_by).'` '.pSQL($order_way).',

Must return the sort selected no? If you remove it, it should go directly to your quantity order. Althought, if you do that, your "sorting selector" won't work on category, it will always go to quantity.

 

Hmmm. you need to add Quantity to the select on the page and work with it as a condition too...

 

Martin.

[pskz]

I can't remove this code, because it is respons for the default sorting within the category.

 

I want to sort by default filters and quantity like:

ORDER BY cp.`position` ASC, stock.`quantity` DESC

or:

ORDER BY pl.`name` ASC, stock.`quantity` DESC 

but this not working

[Martin Uker K]

Oh! Okay, that sounds better.

 

so you want to have FIRST : The position (Which is default) and then the Quantity. The problem is that the position is numeric from 1 to # of products. So, let'S say you have 9 products:  The first will be #1,#2,#3,#4,etc. The problem is that

ORDER BY cp.`position` ASC, stock.`quantity` DESC

Doesn't have any situation to use the quantity sort. None of the position are equals. The first sort is fully discriminating, the second sort (Quantity) is never checked.

 

With the name

ORDER BY pl.`name` ASC, stock.`quantity` DESC 

Quantity will be used only if both product have the exact same name. This doesn't happen a lot in my clients cases...

 

Try naming 3 products the same way and have them have different quantities. Pretty sure they'll be sorted in the right way.

 

Martin.

[pskz]

The first sort is fully discriminating, the second sort (Quantity) is never checked.

 

 

You're right, I forgot about that

I will create two statements: for products with quantity > 0, and the other one for products with quantity = 0, and merge results.

Edited December 5, 2016 by pskz (see edit history)

[Martin Uker K]

Weird...What is your result you want? I mean, what you described could work with jsut inversing your order by result.

 

I mean, either you want it ordered by ID or by Quantity, not both? Why merging...

 

Martin

[pskz]

I need both. As I wrote in the first post:

 

I want to sort products in category by default filters (like: position, price...) and by quantity. Products with quantity = 0 must be on the end of the list.

 

Not excluding pagination.

 

For example:

- first 3 pages have products with quantity > 0,

- 4th page have 2 products with quantity > 0 and 2 products with quantity = 0

- next pages only products with quantity = 0

 

So two sql queries and merging results solves my problem.

 

If you have a better solution please reply

[Martin Uker K]

2 queries is too long, 1 query is always better.

$sql .= ' ORDER BY real_quantity.`quantity` > 0 DESC, '.(!empty($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.bqSQL($order_by).'` '.pSQL($order_way).'
	LIMIT '.(((int)$p - 1) * (int)$n).','.(int)$n;

Quantity > 0 will return 1 if in stock, 0 if oos. Putting DESC means, takes the one that are in stock first, then the one oos. Order same value of stock with default data.

 

Could also be quantity = 0 ASC. You get the point.

 

Martin.

Edited December 5, 2016 by Martin Uker K (see edit history)

[pskz]

I agree, but if you sort by the price, this solution unfortunately does not work, because after query there are this code:

if ($order_by == 'price') {
	Tools::orderbyPrice($result, $order_way);
}

[Martin Uker K]

Man do I hate sorting in the control layer...Welp. your way work too. I didn't check the flow before suggesting 1 query.

 

Bah, too bad! Would have been beautiful with one.

 

Martin.

[pskz]

In that way PrestaShop sorts products by the price, this is related to promotions, etc., and it is not dependent on me.

Thank you for suggestions on using a single query, I didn't know that in the ORDER BY I can use the comparison, this works. For the price, I found another solution.

 

That's why I wrote to the forum, to find an optimal solution to a problem which I could not solve. I didn't ask for a judge of my work.

 

Anyway, thanks for your help.