Sorting in a different way

[shimony]

Hi All,

 

I would like to change a bit the way products being presented.

I need two level of sorting:

1. same as we have today - user choose it and default by order or date...

2. products with quantity=0 (not in stock) will always show last.

 

Is it possible to do such sorting?

 

thanks,

[shimony]

Okay. i know now that this is the SQL ordering i would like to do, but i dont know where can i put the code so it will take affect:

 

 

				    ORDER BY (CASE `quantity`				    WHEN 0 THEN `quantity`				    ELSE `id_product` END) DESC'

 

[shimony]

Ok, found it myself.

Just added the following code into classes/Search.php at line 269

replace this:

 

               WHERE p.`id_product` '.$productPool.'               '.($orderBy ? 'ORDER BY  '.$orderBy : '').($orderWay ? ' '.$orderWay : '').'               LIMIT '.(int)(($pageNumber - 1) * $pageSize).','.(int)$pageSize;

 

with :

 

		    WHERE p.`id_product` '.$productPool.'		    '.'ORDER BY CASE p.`quantity` WHEN 0 THEN p.`quantity` END ASC , CASE p.`quantity` WHEN 80 THEN p.`'.$orderBy.'` ELSE p.`'.$orderBy.'` END '.$orderWay.' '.'		    LIMIT '.(int)(($pageNumber - 1) * $pageSize).','.(int)$pageSize;

 

 

One note - From some reason it doesnt work when ordering by name, so i just removed that option (works for me).

Also, This affect only on searches, but not when going to a category (and usually there you would like to keep the original ordering).

 

In case you know how to set the same in categories, please let me know.

 

Thanks

Edited September 11, 2012 by shimony (see edit history)

[Burhan BVK]

You should use this instead:

WHERE p.`id_product` '.$productPool.'
		    '.($orderBy ? 'ORDER BY p.`quantity`>0 DESC,  '.$orderBy : '').($orderWay ? ' '.$orderWay : '').'
		    LIMIT '.(int)(($pageNumber - 1) * $pageSize).','.(int)$pageSize;

 

It is simpler and faster.

[shimony]

Ill try and let you know soon if this works too.

Any idea where can i put this in order it to take effect on category vies as well?

 

Thanks.

[shimony]

Thank you whitelighter ! - your way works better (even without the problem i had with Name sorting).

Let me know if you find the place i need to change so it will take effect on category view as well.

 

Thanks again,

[shimony]

Hi

 

I finally found the location where to change the sorting in categories.

Line 537 in Classes/category.php

 

The original line to replace is

$sql .= ' ORDER BY '.(isset($orderByPrefix) ? $orderByPrefix.'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'

 

But i am not about the line that i need to put instead (as to whitelighter's suggestion) since the syntax here is different.

 

Please help

[shimony]

It works with this line:

$sql .= ' ORDER BY p.`quantity`>0 DESC,'.(isset($orderByPrefix) ? $orderByPrefix.'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay);

 

But, the only problem now is when i have in same page products in stock and out of stock - in this case the order is not correct - it goes by the selected order (price for example).

Edited September 28, 2012 by shimony (see edit history)