Problem changing product positions in catagory

[jayjay09]

Hi, I am wanting to re-arrange the products in my catagories so certain items are at the top that are currently at the bottom. At the moment im moving the items in the back office but they aren't showing on the site. This maybe very simply but its driving me crazy.

[bboyspy]

You need to go to Backoffice -> Preferences -> Products

and change the sorting of the item to position within category.

[heidijean22]

Hello. I am having the same issue. I changed it to "position inside category". However, when I actually go to the items and drag to put in the order I would like, it doesn't update when I go to the actual live site.

Next to each item is a "position" number. Regardless of where I drag or position them myself, the "position" number doesn't change.

Does anyone know how to actually change the position??

Thanks!

[angel359s]

The same problem. Here is how it works:
You need to go to Backoffice -> Preferences -> Products and change the sorting of the item to position within category, but you need also to set:
Backoffice -> Preferences -> Products -> Default order way: Ascending
If this is set to Decreasing - it is not working correctly.

[heidijean22]

GENIUS!!! You saved the day! Thank you, thank you!

[delite]

Is there any way to make the default prestashop sorting to be made by position in category and not by product ID ascending? At the moment my shop default sorting is by quantity / descending but if there are no products with quantity above 0 the sorting is by product ID. From what i can see the the prestashop sorting engine is working in 2 ways, what you set in Back Office > Preferences > Products > Default order by: ( what you set ) and in the same time by product ID ascending.

[angela80]

I have the same problem. I keep arranging my products in an order that I want, then when I go to view the shop they revert back to the original order. In preferences I have ascending and position within category but it has not fixed the problem. Is there something else I am missing??

Thank you.

[delite]

I have the same problem. I keep arranging my products in an order that I want, then when I go to view the shop they revert back to the original order. In preferences I have ascending and position within category but it has not fixed the problem. Is there something else I am missing??

Thank you.

Try setting in Back Office > Preferences > Products > Default order by: Position inside category

[angela80]

I have done that already.

[ville]

Click on the position to sort the products as you want, then view your shop and it will display as your sorting. Once you click ID again, it is actually re-sorting product in the FO, not only sorting in BO.

 

This solves FO sorting, but will be good if anyone knows how to separate sorting between FO and BO.

[mlarino]

Have the same problem, ordering products is broken in Prestashop.

I have over 200 products in one category that need custom organized, and I can drag the ones in the first page and fix their order, but if i set to view 300 or drag in other pages, they dont get organized.

[Mel Byron]

Hi all,

 

I have this problem too - I haven't cracked it yet, but I might have found the cause.

A tip o' the hat to Ville (post 10), who set me on the right track.

 

Observations:

1. if the default product sort order is set to 'position within category'

(preferences->products), it's possible to move products using drag & drop or the

positioning arrows.

 

2. this seems to work, but after a bit of rearrangement it becomes clear that some

products switch to the wrong position, and are very difficult to correct.

 

3. the reason: drag & drop and the positioning arrows change the position number of the

product being moved, but leave the other product position numbers in the category

unaltered. It is thus fairly easy to have duplicate numbers appear (the position

numbers can be seen by setting 'position down' on the Admin page, or using phpMyAdmin

to examine field 'position' in the table 'ps_category_product' for the relevant

category).

 

4. when duplicate position numbers are present, the front end will sort products of

the same position number by (I think) ID. This may or may not be the order you want,

which is why only some products seem to be affected.

 

5. Ville suggested that setting the ID sort on the Admin page will reassign the

position numbers. Unfortunately this doesn't work for me, the duplicate numbers

remain unchanged.

 

6. I tried correcting the position numbers using phpMyAdmin, but rearranging the

products caused duplicate numbers to reappear almost immediately.

 

Conclusion:

the Ajax program used by drag & drop and the positioning arrows must renumber

the product positions for the entire category whenever a product is moved.

 

I hope that the Prestashop developers can address this problem soon - it's

a minor flaw, but one that is highly visible to shop visitors.

[studioneko]

Hi all,

 

I have this problem too - I haven't cracked it yet, but I might have found the cause.

A tip o' the hat to Ville (post 10), who set me on the right track.

 

Observations:

1. if the default product sort order is set to 'position within category'

(preferences->products), it's possible to move products using drag & drop or the

positioning arrows.

 

2. this seems to work, but after a bit of rearrangement it becomes clear that some

products switch to the wrong position, and are very difficult to correct.

 

3. the reason: drag & drop and the positioning arrows change the position number of the

product being moved, but leave the other product position numbers in the category

unaltered. It is thus fairly easy to have duplicate numbers appear (the position

numbers can be seen by setting 'position down' on the Admin page, or using phpMyAdmin

to examine field 'position' in the table 'ps_category_product' for the relevant

category).

 

4. when duplicate position numbers are present, the front end will sort products of

the same position number by (I think) ID. This may or may not be the order you want,

which is why only some products seem to be affected.

 

5. Ville suggested that setting the ID sort on the Admin page will reassign the

position numbers. Unfortunately this doesn't work for me, the duplicate numbers

remain unchanged.

 

6. I tried correcting the position numbers using phpMyAdmin, but rearranging the

products caused duplicate numbers to reappear almost immediately.

 

Conclusion:

the Ajax program used by drag & drop and the positioning arrows must renumber

the product positions for the entire category whenever a product is moved.

 

I hope that the Prestashop developers can address this problem soon - it's

a minor flaw, but one that is highly visible to shop visitors.

 

I confirm all of the above. Moving products with the drag & drop completely screwed up one of my shops and forced me to unistall/reinstall prestashop

[Mel Byron]

Hi studioneko,

 

I have some good news: with a bit of effort this problem can be solved completely.

 

The cause was (in my case) importing the shop database from osCommerce (using Cart2Cart). Everything seemed to go well, but all the product position numbers were set to 1. Prestashop requires these numbers to be consecutive, so any attempt to move products with drag & drop leads to a mess, duplicate position numbers are created and the required order is impossible to achieve.

 

The solution is to renumber the positions consecutively. This would normally be done on a product delete, but there is an error in the php file classes/Product.php. It can be corrected by modifying the code as follows (at approx. line 707):

 

Original:

public function deleteCategory($id_category, $cleanPositions = true)

{

$result = Db::getInstance()->ExecuteS('SELECT `id_category` FROM `'._DB_PREFIX_.'category_product` WHERE `id_product` = '.(int)($this->id) . ' AND id_category = '.(int)$id_category .'');

$return = Db::getInstance()->Execute('DELETE FROM `'._DB_PREFIX_.'category_product` WHERE `id_product` = '.(int)($this->id).' AND id_category = '.(int)$id_category .'' );

if ($cleanPositions === true)

foreach ($result AS $row)

$this->cleanPositions((int)$row['id_category']);

return $return;

}

 

Replace the above with:

public function deleteCategory($id_category, $cleanPositions = true)

{

$result = Db::getInstance()->ExecuteS('SELECT `id_category` FROM `'._DB_PREFIX_.'category_product` WHERE `id_product` = '.(int)($this->id) . ' AND id_category = '.(int)$id_category .'');

if ($cleanPositions === true)

foreach ($result AS $row)

$this->cleanPositions((int)$row['id_category']);

$return = Db::getInstance()->Execute('DELETE FROM `'._DB_PREFIX_.'category_product` WHERE `id_product` = '.(int)($this->id).' AND id_category = '.(int)$id_category .'' );

return $return;

}

 

As you can see, the line $return = Db... etc. has been moved down.

 

Once you have uploaded the modified Product.php file you should create a temporary product in all your categories and subcategories, then delete it. This will renumber the positions within the category consecutively. All your problems should then disappear, the drag & drop function will work perfectly. N.b. once this has been done, the problem does not recur - provided no duplicates are present, Prestashop handles position numbers correctly.

 

i hope this works for you. It's painful to create and delete a product in every category (it took me several hours), but it's worth it,

I now have perfect positioning.

 

The PHP mod above is from this post:

http://forge.prestashop.com/browse/PSCFI-5694

[gebryl]

Thanks a lot Mel this helped! PHEW.

 

However will like to point out that it is on line 722 onwards where:

public function deleteCategories($cleanPositions = false)

 

All the best all

 

Hi studioneko, I have some good news: with a bit of effort this problem can be solved completely. The cause was (in my case) importing the shop database from osCommerce (using Cart2Cart). Everything seemed to go well, but all the product position numbers were set to 1. Prestashop requires these numbers to be consecutive, so any attempt to move products with drag & drop leads to a mess, duplicate position numbers are created and the required order is impossible to achieve. The solution is to renumber the positions consecutively. This would normally be done on a product delete, but there is an error in the php file classes/Product.php. It can be corrected by modifying the code as follows (at approx. line 707): Original: public function deleteCategory($id_category, $cleanPositions = true) { $result = Db::getInstance()->ExecuteS('SELECT `id_category` FROM `'._DB_PREFIX_.'category_product` WHERE `id_product` = '.(int)($this->id) . ' AND id_category = '.(int)$id_category .''); $return = Db::getInstance()->Execute('DELETE FROM `'._DB_PREFIX_.'category_product` WHERE `id_product` = '.(int)($this->id).' AND id_category = '.(int)$id_category .'' ); if ($cleanPositions === true) foreach ($result AS $row) $this->cleanPositions((int)$row['id_category']); return $return; } Replace the above with: public function deleteCategory($id_category, $cleanPositions = true) { $result = Db::getInstance()->ExecuteS('SELECT `id_category` FROM `'._DB_PREFIX_.'category_product` WHERE `id_product` = '.(int)($this->id) . ' AND id_category = '.(int)$id_category .''); if ($cleanPositions === true) foreach ($result AS $row) $this->cleanPositions((int)$row['id_category']); $return = Db::getInstance()->Execute('DELETE FROM `'._DB_PREFIX_.'category_product` WHERE `id_product` = '.(int)($this->id).' AND id_category = '.(int)$id_category .'' ); return $return; } As you can see, the line $return = Db... etc. has been moved down. Once you have uploaded the modified Product.php file you should create a temporary product in all your categories and subcategories, then delete it. This will renumber the positions within the category consecutively. All your problems should then disappear, the drag & drop function will work perfectly. N.b. once this has been done, the problem does not recur - provided no duplicates are present, Prestashop handles position numbers correctly. i hope this works for you. It's painful to create and delete a product in every category (it took me several hours), but it's worth it, I now have perfect positioning. The PHP mod above is from this post: http://forge.prestashop.com/browse/PSCFI-5694

[nandosan]

Same problem here, also due to a cart2cart migration.

In this case from old prestashop to new one..

 

I'm running prestashop 1.5.4.1 and bug is still present, but this solution work for me.

 

Thanks

[tatamimi]

Hello.

 

I'm trying to modify the bug and changed the classes/Product.php file, delete all products and re-make, and delate again.

But still I can't move (sort) our products in the list by drug and drop....

 

I use version 1.5.4.1, I changed the lines as below ( line 890 )...

 

 

public function deleteCategory($id_category, $cleanPositions = false)

{

$result = Db::getInstance()->ExecuteS('SELECT `id_category` FROM `'._DB_PREFIX_.'category_product` WHERE `id_product` = '.(int)($this->id) . ' AND id_category = '.(int)$id_category .'');

if ($cleanPositions === true)

foreach ($result AS $row)

$this->cleanPositions((int)$row['id_category']);

$return = Db::getInstance()->Execute('DELETE FROM `'._DB_PREFIX_.'category_product` WHERE `id_product` = '.(int)($this->id).' AND id_category = '.(int)$id_category .'' );

return $return;

}

 

Somewhere is wrong or other some reason? Please help me...

[Mel Byron]

Hi tatamimi,

 

you could try correcting the product positions manually using phpMyAdmin. This might not be practical for owners of huge shops, but I see you only have about 100 items in your store.

 

In my Prestashop version (1.4.7.3) the relevant table is called ps_category_product, you should check to see if there are any duplicate values under position. As you can see from the snapshot below, the positions assigned don't have to be consecutive but they must be unique (i.e. product positions for each id_category can have any value, but there must be no duplicates).

 

Bonne Chance

 

Mel.

 

[tatamimi]

Hi Mel, Thank you and sorry to be late to respond.

 

I'll try your advice (by phpAdmin) as the final way.

As not a professional for Database, I'm afraid to use phpAdmin easily...

 

I hope the bug will be modify...

Maybe there is some way to do it as nandosan says...

 

Anyway thanks a lot! I'll check this forum sometimes.

 

Tatamimi

[nandosan]

Maybe I found a prestashop but (hope not so..) in the position rearranging process.

 

Rearranging is normally working but:

 

if you move a product to another position and immediately enter one product (the same or another) to edit it, when go back to the list product order hasn't changed..

 

Someone knows the exact moment (or event) the ajax call to update position is called?

 

Maybe I found a prestashop but (hope not so..) in the position rearranging process.

 

Rearranging is normally working but:

 

if you move a product to another position and immediately enter one product (the same or another) to edit it, when go back to the list product order hasn't changed..

 

Someone knows the exact moment (or event) the ajax call to update position is called?

 

Maybe I found a prestashop but (hope not so..) in the position rearranging process.

 

Rearranging is normally working but:

 

if you move a product to another position and immediately enter one product (the same or another) to edit it, when go back to the list product order hasn't changed..

 

Someone knows the exact moment (or event) the ajax call to update position is called?

[Liofey]

how to solve this problem i have change the php file classes/Product.php. But it does not effect in my database

 

My question, if i delete the temporary products, all product position will be reset into 0,1,2,3,xxx or it just re ordering ??? Because when i check in my database, its not reset the positions. 

[vekia]

if you remove temporary products, all other products will not be reordered, just position change, nothing more

[Liofey]

hi vekia. my friend told me the position number will be re arrange from 0,1,2,etc..but at my website it's not rearrange, when i delete the temporary product the clean position method not update the position number from 0..

[hkao]

Featured Products´ products position in Multistore:

 

Hello,

 

I am with Featured Products 0.9 in the Prestashop 1.5.3.1 Multistore, what I´d like to do is to show different products in its respective store, but I cant get it.

 

For example: I have 3 sub stores ( selling: LED lights, women clothes, security cameras). The main store is for marketing.

 

All products are setup at the Home (all store), for centralized distribution purpose. I try to change Featured Products´ position order by going to: Product Tab -> filter -> Home with its sub store Context. For example, Store: LED.  And the Featured Products on its sub store page DOES change according to my modification. BUT when I change next sub store position order, the rest of stores also change its position on its sub store page.

 

What I have tried: setup all the sub stores position order on Products Tab, and reinstall Featured Products Module, and it DID work for all the stores.  But I just can´t rearrange these position which will mess up again.

 

I suspect it is the cache problem, and I deleted all the cache, but it does not help.

 

Need help from someone out there, or someone can suggest me some module can server this purpose.

 

Thanks in advance. From Brasil

 

[Centio]

Thanks for great help. Just to be clear though as sorting is not working in my 1.5.6.0 shop.

 

Does the position id need to be unique across the whole shop or just within a category. 

My shop currently looks like

Category A : Position 1,2,3,.....

Category B : Position 1,2,3,4,.....

 

Any ideas?

[Michel2015]

Hi there, this problem has been bugging me for years, and it seems I'm not the only one,

 

can't understand why PS does not address such an important issue for years!!

 

Isn't there a module where you can set all product positions manually?

 

thanks

[RosesCCRS]

I agree with Michel2015 above.  Is there an alternative to this?  I have PrestaShop™ 1.5.3.1. 

[musicmaster]

Try this free script (not a module!). It has a good sorting section.

 

http://www.prestashop.com/forums/topic/185401-free-script-product-mass-edit-category-multi-edit-order-edit/

[intandem]

Just a quick note here which might not apply to any of you but in case someone else comes across this same topic. I am just off the phone with a client who was experiencing very frustrating issues with changing of product positions. She would drag and drop a product into a new order, for a moment she could see that ajax adjusted the position numbers accordingly but once she manually refreshed the page, the product would jump back to its old position. She would have to redo this multiple times per product before int might "stick".

 

I was trying to replicate her issue on my computer but everything was working perfectly fine first try. I am working on Chrome on a Mac, she's on Chrome on Windows. I asked her to try logging into Prestashop via Internet Explorer and go for it again. And yes, it now worked perfectly without any issues!! I'm not sure about her Chrome version but the point is that simply using a different browser got her back to working efficiently. So I thought I'd just add this here in case it is of help to anybody. At least until this gets fixed properly. 

[Elise B.]

Try this free script (not a module!). It has a good sorting section.

 

http://www.prestashop.com/forums/topic/185401-free-script-product-mass-edit-category-multi-edit-order-edit/

Hello, thank you. So far, this option is working well for me. Not quite as easy, perhaps, as the drag-n-drop (so certainly would be nice if drag-n-drop worked properly) I already had this script installed so this was very easy to go to as a work around. The visual sort helps a lot. Thanks again for the suggestion. I was not up to tackling modifying my files etc. etc. So am very grateful there was an easy straight forward solution to help get the job done!

[Elise B.]

Just a quick note here which might not apply to any of you but in case someone else comes across this same topic. I am just off the phone with a client who was experiencing very frustrating issues with changing of product positions. She would drag and drop a product into a new order, for a moment she could see that ajax adjusted the position numbers accordingly but once she manually refreshed the page, the product would jump back to its old position. She would have to redo this multiple times per product before int might "stick".

 

I was trying to replicate her issue on my computer but everything was working perfectly fine first try. I am working on Chrome on a Mac, she's on Chrome on Windows. I asked her to try logging into Prestashop via Internet Explorer and go for it again. And yes, it now worked perfectly without any issues!! I'm not sure about her Chrome version but the point is that simply using a different browser got her back to working efficiently. So I thought I'd just add this here in case it is of help to anybody. At least until this gets fixed properly. 

Hello, thank you, this is the solution I was looking for. Simply switching browsers (Chrome to IE) solved my issue. Thanks again, very happy to be able to use the drag and drop product sorting function again.

[Jadzia DeForest]

I have Prestashop 1.6.0.14 and the products do not stay where I put them when I arrange them. Can someone help with this?

 

By the way it doesn't work if I use a different browser either.

Edited July 3, 2015 by Jadzia DeForest (see edit history)

[jetx]

Just a quick note here which might not apply to any of you but in case someone else comes across this same topic. I am just off the phone with a client who was experiencing very frustrating issues with changing of product positions. She would drag and drop a product into a new order, for a moment she could see that ajax adjusted the position numbers accordingly but once she manually refreshed the page, the product would jump back to its old position. She would have to redo this multiple times per product before int might "stick".

 

I was trying to replicate her issue on my computer but everything was working perfectly fine first try. I am working on Chrome on a Mac, she's on Chrome on Windows. I asked her to try logging into Prestashop via Internet Explorer and go for it again. And yes, it now worked perfectly without any issues!! I'm not sure about her Chrome version but the point is that simply using a different browser got her back to working efficiently. So I thought I'd just add this here in case it is of help to anybody. At least until this gets fixed properly. 

 

 

ugg yeah totally frustrating. Chrome ignores position changes, Explorer actually does make the change.

[Elise B.]

Hello, another solution I have found is go to Preferences/Products in BO scroll to pagination. Select Default order by Position inside category / Ascending. Save changes. This was my real solution and should work when you sort with any browser.

 

 

-- forgot this was also about items reverting to old position upon refresh. This is where I think I had some trouble in Chrome. But also was finding my positions were correct in BO but not showing in FO, which is what I'm referring to above.

Edited November 26, 2015 by Elise B. (see edit history)

[Xtra Think]

Hi,

For those who still have this problem and if you have blocklayered module activated on your store then here is what i did to fix the problem.
1- Go to blocklayered.php file
2- Go to getProductByFilters function
3- line 2013/2014 add this "AND cp.id_category = '.(int)$id_parent.'"

4- Save and enjoy  

 

Rachid Chihabi  

[tesladisk]

I wanted my new products first inside the category prestashop 1.6

 

Preferences >Products >Pagination

Default Order >By Date Added

Default Order Method > Decending

 

Play around with this settings it may fit your needs

Regards

[bay_kolio]

 

Hi,

 

For those who still have this problem and if you have blocklayered module activated on your store then here is what i did to fix the problem.

1- Go to blocklayered.php file

2- Go to getProductByFilters function

3- line 2013/2014 add this "AND cp.id_category = '.(int)$id_parent.'"

4- Save and enjoy  

 

Rachid Chihabi  

 

 

Hi Rachid Chihabi where to put this code. My file have getProductByFilters on 1780 line?

Please give us correct code.

Thanks

[Xtra Think]

Hi Rachid Chihabi where to put this code. My file have getProductByFilters on 1780 line?

Please give us correct code.

Thanks

 

My solution was for the old version of "blocklayered" module (PS version 1.6.0.14), So the correction is compatible up to PS version 1.6.0.14.

 

If you have PS 1.6.1.x (the module is came out with the version of PS) here is what you have to do :

 

1- Go to blocklayered.php file

2- Go to getProductByFilters function

3 - Find this line of code "if (version_compare(_PS_VERSION_, '1.6.1', '>=') === true)"

4- Change all the ( IF and else ) statement  by :

 

if (version_compare(_PS_VERSION_, '1.6.1', '>=') === true)

{

$this->products = Db::getInstance()->executeS('

SELECT

p.*,

'.($alias_where == 'p' ? '' : 'product_shop.*,' ).'

'.$alias_where.'.id_category_default,

pl.*,

image_shop.`id_image` id_image,

il.legend,

m.name manufacturer_name,

'.(Combination::isFeatureActive() ? 'product_attribute_shop.id_product_attribute id_product_attribute,' : '').'

DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,

stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').'

FROM '._DB_PREFIX_.'cat_filter_restriction 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_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')

 

LEFT JOIN `'._DB_PREFIX_.'category_product` p_cat ON (p.id_product = p_cat.id_product)

 

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)$cookie->id_lang.')

LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)

'.Product::sqlStock('p', 0).'

WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog") 

 

AND p_cat.id_category = '.(int)$id_parent.'

 

ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.

' LIMIT '.(((int)$this->page - 1) * $n.','.$n));

}

else

{

$this->products = Db::getInstance()->executeS('

SELECT

p.*,

'.($alias_where == 'p' ? '' : 'product_shop.*,' ).'

'.$alias_where.'.id_category_default,

pl.*,

MAX(image_shop.`id_image`) id_image,

il.legend,

m.name manufacturer_name,

'.(Combination::isFeatureActive() ? 'MAX(product_attribute_shop.id_product_attribute) id_product_attribute,' : '').'

DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,

stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '').'

FROM '._DB_PREFIX_.'cat_filter_restriction 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` pa ON (p.`id_product` = pa.`id_product`)

'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop='.(int)$context->shop->id):'').'

LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')

 

LEFT JOIN `'._DB_PREFIX_.'category_product` p_cat ON (p.id_product = p_cat.id_product)

 

LEFT JOIN `'._DB_PREFIX_.'image` i  ON (i.`id_product` = p.`id_product`)'.

Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'

LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$cookie->id_lang.')

LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)

'.Product::sqlStock('p', 0).'

WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")

 

AND p_cat.id_category = '.(int)$id_parent.'

 

GROUP BY product_shop.id_product

ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.

' LIMIT '.(((int)$this->page - 1) * $n.','.$n));

}

 

 

5 - Go to Tools.php and find "getProductsOrder"

Change this :

 

} elseif ($value == 'position' || empty($value)) {

    $order_by_prefix = 'cp.';

}

 

By this :

 

} elseif ($value == 'position' || empty($value)) {

    $order_by_prefix = 'p_cat.';

}

 

Finally Save and don't foret to override your files.

 

Sorry for my english its not my language

 

Rachid Chihabi

[cagrie]

dude it worked .... do I need to say you saved my life?

 

thank you very much!

[Forgiven1]

I found kind of a cheesy way around this bug.    Disable all products in the category.   Re enable them in the order you want them to be.   Not great, but you have the "grid products module" its fairly quick.    You just go through and click one button to enable or disable a product and the load time is ok.   They changed to the order I wanted in BO an FO and stayed that way.

[distante]

 

My solution was for the old version of "blocklayered" module (PS version 1.6.0.14), So the correction is compatible up to PS version 1.6.0.14.

 

If you have PS 1.6.1.x (the module is came out with the version of PS) here is what you have to do :

 

1- Go to blocklayered.php file

2- Go to getProductByFilters function

3 - Find this line of code "if (version_compare(_PS_VERSION_, '1.6.1', '>=') === true)"

4- Change all the ( IF and else ) statement  by :

 

if (version_compare(_PS_VERSION_, '1.6.1', '>=') === true)
{
$this->products = Db::getInstance()->executeS('
SELECT
p.*,
'.($alias_where == 'p' ? '' : 'product_shop.*,' ).'
'.$alias_where.'.id_category_default,
pl.*,
image_shop.`id_image` id_image,
il.legend,
m.name manufacturer_name,
'.(Combination::isFeatureActive() ? 'product_attribute_shop.id_product_attribute id_product_attribute,' : '').'
DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').'
FROM '._DB_PREFIX_.'cat_filter_restriction 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_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')

LEFT JOIN `'._DB_PREFIX_.'category_product` p_cat ON (p.id_product = p_cat.id_product)

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)$cookie->id_lang.')
LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
'.Product::sqlStock('p', 0).'
WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog") 

AND p_cat.id_category = '.(int)$id_parent.'

ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.
' LIMIT '.(((int)$this->page - 1) * $n.','.$n));
}
else
{
$this->products = Db::getInstance()->executeS('
SELECT
p.*,
'.($alias_where == 'p' ? '' : 'product_shop.*,' ).'
'.$alias_where.'.id_category_default,
pl.*,
MAX(image_shop.`id_image`) id_image,
il.legend,
m.name manufacturer_name,
'.(Combination::isFeatureActive() ? 'MAX(product_attribute_shop.id_product_attribute) id_product_attribute,' : '').'
DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '').'
FROM '._DB_PREFIX_.'cat_filter_restriction 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` pa ON (p.`id_product` = pa.`id_product`)
'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop='.(int)$context->shop->id):'').'
LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')

LEFT JOIN `'._DB_PREFIX_.'category_product` p_cat ON (p.id_product = p_cat.id_product)

LEFT JOIN `'._DB_PREFIX_.'image` i  ON (i.`id_product` = p.`id_product`)'.
Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$cookie->id_lang.')
LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
'.Product::sqlStock('p', 0).'
WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")

AND p_cat.id_category = '.(int)$id_parent.'

GROUP BY product_shop.id_product
ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.
' LIMIT '.(((int)$this->page - 1) * $n.','.$n));
}

 

 

5 - Go to Tools.php and find "getProductsOrder"

Change this :

 

} elseif ($value == 'position' || empty($value)) {

    $order_by_prefix = 'cp.';

}

 

By this :

 

} elseif ($value == 'position' || empty($value)) {

    $order_by_prefix = 'p_cat.';

}

 

Finally Save and don't foret to override your files.

 

Sorry for my english its not my language

 

Rachid Chihabi

 

 

Excuse me, where is the Tools.php file? 

[Xtra Think]

Excuse me, where is the Tools.php file? 

 

The Tools.php file is located in classes folder

[distante]

The Tools.php file is located in classes folder

Thanks! BTW There's also another in /public_html/tools/profiling/Tools.php 

[Xtra Think]

Thanks! BTW There's also another in /public_html/tools/profiling/Tools.php 

 

Not this one, the other in the CLASSES folder, Here :

/public_html/classes/Tools.php

 

Or if you already ovvridded your "classes" folder, you will find the file here :

/public_html/override/classes/Tools.php

 

 

Rachid chihabi

[distante]

Not this one, the other in the CLASSES folder, Here :

/public_html/classes/Tools.php

 

Or if you already ovvridded your "classes" folder, you will find the file here :

/public_html/override/classes/Tools.php

 

 

Rachid chihabi

Thanks a lot! now the drag and drop in categories is working!!!!     

[distante]

Not this one, the other in the CLASSES folder, Here :

/public_html/classes/Tools.php

 

Or if you already ovvridded your "classes" folder, you will find the file here :

/public_html/override/classes/Tools.php

 

 

Rachid chihabi

 

A question related, do I have to do something else for this change to "spread" the changes or fix to other modules or similar? I use the "Cs Mega Menu Block" and it still shows the old category order. 

[Xtra Think]

A question related, do I have to do something else for this change to "spread" the changes or fix to other modules or similar? I use the "Cs Mega Menu Block" and it still shows the old category order. 

 

This solution is not for ordring categories, it's for fixing the problem of products positions inside categories !!

[distante]

This solution is not for ordring categories, it's for fixing the problem of products positions inside categories !!

Oh... but it fixed (I think) the problem of categories order... because I see them now updated inside the category list... damm, sorry.

 

I will need to search how to fix the other problem.

 

Thanks anyway

[Xtra Think]

Oh... but it fixed (I think) the problem of categories order... because I see them now updated inside the category list... damm, sorry.

 

I will need to search how to fix the other problem.

 

Thanks anyway

 

If you do not find anything about your other problem, just open a new topic and link it with this in a comment to let me know, if i can do something for you

Or send me your skype coords in PM

[distante]

If you do not find anything about your other problem, just open a new topic and link it with this in a comment to let me know, if i can do something for you

Or send me your skype coords in PM

Done, I think is better in public so another person with the same problem can find a fix 

the link here

[algisna]

rachid.antipodes - just wanted to say thanks... This issue was bugging me for years after migration from another cart

[bay_kolio]

This is not working for me version 1.6.1.4 
Nothing happaned 

[Forgiven1]

Hi bay_kolio,

I have 1.6.1.4 also.    The only way I can get items to stay in the correct place is like this.

Catalog>Products>Filter by category>Expand all> Select ONE category.    Once you are inside ONE single category you can move items around by grabbing the number arrow and moving it up or down.    Items will revert back to their old position if you do not select only one category before doing this.   If you do not see the movable arrows when you enter the category click the down arrow next to "position" and they will appear.

Hope this helps.

 

 

[R4Z]

Hi, just wanted to share some more info on this topic, theses solutions didn't work for me but one 'kinda' did.

 

It's a PS install with no migrations, imports or nothing, just one update (1.5.x to 1.6.1.1).

Fixing / modding Tools.php kept saying 'fatal error' through console (as a response to product sorting update on BO)

 

The fix (for me) was to comment out the line originally giving the error wich was in classes/cookie.php (around l.170) in function __set, commented out the die(...) from if (is_array)

public function __set($key, $value)
    {
        if (is_array($value)) {
            // die(Tools::displayError()); --> commented out!
        }
        if (preg_match('/¤|\|/', $key.$value)) {
            throw new Exception('Forbidden chars in cookie');
        }
        if (!$this->_modified && (!isset($this->_content[$key]) || (isset($this->_content[$key]) && $this->_content[$key] != $value))) {
            $this->_modified = true;
        }
        $this->_content[$key] = $value;
    }

I'm aware it's not a optimaal fix since it let's the door open for problem with sending arrays through cookies, but after one week of searching and trying different combinations (including disabling modules, modding files, ...) this is the change that allowed me to sort products by drag'n'drop without errors.

Edited March 22, 2016 by R4Z (see edit history)

[Derres]

Custom product order in manufacurer and supplier page

 

Hi i want the products have the same order as the cateogory in the manufacturer and supply page.

 

Idk why but since today they stay only in alphabetic order in manufacturer and supplier page, also if i change the settings no way to keep thes ein category order same as in past.

 

Someone know what bug is that and how to fixit ?

 

I have do also some combinations.

 

Maybe are these that have create the bug ?

 

Thanks

[cedric_charles]

Hi !

 

I Have the same problem as initial one... The problem appears after an import made by Cart2Cart (from older Prestashop). I'm now on 1.6.1.5. What do I have to do to re-order my product properly :s ?

 

Thank you in advance !

[cedric_charles]

This is sooo anoying... I can't find a way getting rid of this problem...

[zeisei]

' rachid.antipodes' you are the best. You saved me from one of the biggest headaches i had for the last 2 years.

Thank you very much.

[cedric_charles]

Hello rachid.antipodes and Zeisei,

 

I've just try what you said there : https://www.prestashop.com/forums/topic/51840-problem-changing-product-positions-in-catagory/?p=2213897

 

So I've created "blocklayered.php" in /override/modules/blocklayered/blocklayered.php and "Tools.php" in /override/classes/Tools.php with the changes you gave us.

 

blocklayered.php :

<?php
/*
* 2007-2015 PrestaShop
*
* NOTICE OF LICENSE
*
* This source file is subject to the Academic Free License (AFL 3.0)
* that is bundled with this package in the file LICENSE.txt.
* It is also available through the world-wide-web at this URL:
* http://opensource.org/licenses/afl-3.0.php
* If you did not receive a copy of the license and are unable to
* obtain it through the world-wide-web, please send an email
* to [email protected] so we can send you a copy immediately.
*
* DISCLAIMER
*
* Do not edit or add to this file if you wish to upgrade PrestaShop to newer
* versions in the future. If you wish to customize PrestaShop for your
* needs please refer to http://www.prestashop.com for more information.
*
*  @author PrestaShop SA <[email protected]>
*  @copyright  2007-2015 PrestaShop SA
*  @license    http://opensource.org/licenses/afl-3.0.php  Academic Free License (AFL 3.0)
*  International Registred Trademark & Property of PrestaShop SA
*/

if (!defined('_PS_VERSION_'))
	exit;

class BlockLayeredExtends extends BlockLayered
{
	public function getProductByFilters($selected_filters = array())
	{
		global $cookie;

		if (!empty($this->products))
			return $this->products;

		$home_category = Configuration::get('PS_HOME_CATEGORY');
		/* If the current category isn't defined or if it's homepage, we have nothing to display */
		$id_parent = (int)Tools::getValue('id_category', Tools::getValue('id_category_layered', $home_category));
		if ($id_parent == $home_category)
			return false;

		$alias_where = 'p';
		if (version_compare(_PS_VERSION_,'1.5','>'))
			$alias_where = 'product_shop';

		$query_filters_where = ' AND '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")';
		$query_filters_from = '';

		$parent = new Category((int)$id_parent);

		foreach ($selected_filters as $key => $filter_values)
		{
			if (!count($filter_values))
				continue;

			preg_match('/^(.*[^_0-9])/', $key, $res);
			$key = $res[1];

			switch ($key)
			{
				case 'id_feature':
					$sub_queries = array();
					foreach ($filter_values as $filter_value)
					{
						$filter_value_array = explode('_', $filter_value);
						if (!isset($sub_queries[$filter_value_array[0]]))
							$sub_queries[$filter_value_array[0]] = array();
						$sub_queries[$filter_value_array[0]][] = 'fp.`id_feature_value` = '.(int)$filter_value_array[1];
					}
					foreach ($sub_queries as $sub_query)
					{
						$query_filters_where .= ' AND p.id_product IN (SELECT `id_product` FROM `'._DB_PREFIX_.'feature_product` fp WHERE ';
						$query_filters_where .= implode(' OR ', $sub_query).') ';
					}
				break;

				case 'id_attribute_group':
					$sub_queries = array();


					foreach ($filter_values as $filter_value)
					{
						$filter_value_array = explode('_', $filter_value);
						if (!isset($sub_queries[$filter_value_array[0]]))
							$sub_queries[$filter_value_array[0]] = array();
						$sub_queries[$filter_value_array[0]][] = 'pac.`id_attribute` = '.(int)$filter_value_array[1];
					}
					foreach ($sub_queries as $sub_query)
					{
						$query_filters_where .= ' AND p.id_product IN (SELECT pa.`id_product`
						FROM `'._DB_PREFIX_.'product_attribute_combination` pac
						LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
						ON (pa.`id_product_attribute` = pac.`id_product_attribute`)'.
						Shop::addSqlAssociation('product_attribute', 'pa').'
						WHERE '.implode(' OR ', $sub_query).') ';
					}
				break;

				case 'category':
					$query_filters_where .= ' AND p.id_product IN (SELECT id_product FROM '._DB_PREFIX_.'category_product cp WHERE ';
					foreach ($selected_filters['category'] as $id_category)
						$query_filters_where .= 'cp.`id_category` = '.(int)$id_category.' OR ';
					$query_filters_where = rtrim($query_filters_where, 'OR ').')';
				break;

				case 'quantity':
					if (count($selected_filters['quantity']) == 2)
						break;

					$query_filters_where .= ' AND sa.quantity '.(!$selected_filters['quantity'][0] ? '<=' : '>').' 0 ';
					$query_filters_from .= 'LEFT JOIN `'._DB_PREFIX_.'stock_available` sa ON (sa.id_product = p.id_product '.StockAvailable::addSqlShopRestriction(null, null,  'sa').') ';
				break;

				case 'manufacturer':
					$query_filters_where .= ' AND p.id_manufacturer IN ('.implode($selected_filters['manufacturer'], ',').')';
				break;

				case 'condition':
					if (count($selected_filters['condition']) == 3)
						break;
					$query_filters_where .= ' AND '.$alias_where.'.condition IN (';
					foreach ($selected_filters['condition'] as $cond)
						$query_filters_where .= '\''.pSQL($cond).'\',';
					$query_filters_where = rtrim($query_filters_where, ',').')';
				break;

				case 'weight':
					if ($selected_filters['weight'][0] != 0 || $selected_filters['weight'][1] != 0)
						$query_filters_where .= ' AND p.`weight` BETWEEN '.(float)($selected_filters['weight'][0] - 0.001).' AND '.(float)($selected_filters['weight'][1] + 0.001);
				break;

				case 'price':
					if (isset($selected_filters['price']))
					{
						if ($selected_filters['price'][0] !== '' || $selected_filters['price'][1] !== '')
						{
							$price_filter = array();
							$price_filter['min'] = (float)($selected_filters['price'][0]);
							$price_filter['max'] = (float)($selected_filters['price'][1]);
						}
					}
					else
						$price_filter = false;
				break;
			}
		}

		$context = Context::getContext();
		$id_currency = (int)$context->currency->id;

		$price_filter_query_in = ''; // All products with price range between price filters limits
		$price_filter_query_out = ''; // All products with a price filters limit on it price range
		if (isset($price_filter) && $price_filter)
		{
			$price_filter_query_in = 'INNER JOIN `'._DB_PREFIX_.'layered_price_index` psi
			ON
			(
				psi.price_min <= '.(int)$price_filter['max'].'
				AND psi.price_max >= '.(int)$price_filter['min'].'
				AND psi.`id_product` = p.`id_product`
				AND psi.`id_shop` = '.(int)$context->shop->id.'
				AND psi.`id_currency` = '.$id_currency.'
			)';

			$price_filter_query_out = 'INNER JOIN `'._DB_PREFIX_.'layered_price_index` psi
			ON
				((psi.price_min < '.(int)$price_filter['min'].' AND psi.price_max > '.(int)$price_filter['min'].')
				OR
				(psi.price_max > '.(int)$price_filter['max'].' AND psi.price_min < '.(int)$price_filter['max'].'))
				AND psi.`id_product` = p.`id_product`
				AND psi.`id_shop` = '.(int)$context->shop->id.'
				AND psi.`id_currency` = '.$id_currency;
		}

		$query_filters_from .= Shop::addSqlAssociation('product', 'p');

		Db::getInstance()->execute('DROP TEMPORARY TABLE IF EXISTS '._DB_PREFIX_.'cat_filter_restriction', false);
		if (empty($selected_filters['category']))
		{
			/* Create the table which contains all the id_product in a cat or a tree */
			Db::getInstance()->execute('CREATE TEMPORARY TABLE '._DB_PREFIX_.'cat_filter_restriction ENGINE=MEMORY
														SELECT cp.id_product, MIN(cp.position) position FROM '._DB_PREFIX_.'category_product cp
														INNER JOIN '._DB_PREFIX_.'category c ON (c.id_category = cp.id_category AND
														'.(Configuration::get('PS_LAYERED_FULL_TREE') ? 'c.nleft >= '.(int)$parent->nleft.'
														AND c.nright <= '.(int)$parent->nright : 'c.id_category = '.(int)$id_parent).'
														AND c.active = 1)
														JOIN `'._DB_PREFIX_.'product` p USING (id_product)
														'.$price_filter_query_in.'
														'.$query_filters_from.'
														WHERE 1 '.$query_filters_where.'
														GROUP BY cp.id_product ORDER BY position, id_product', false);
		} else {
			$categories = array_map('intval', $selected_filters['category']);

			Db::getInstance()->execute('CREATE TEMPORARY TABLE '._DB_PREFIX_.'cat_filter_restriction ENGINE=MEMORY
														SELECT cp.id_product, MIN(cp.position) position FROM '._DB_PREFIX_.'category_product cp
														JOIN `'._DB_PREFIX_.'product` p USING (id_product)
														'.$price_filter_query_in.'
														'.$query_filters_from.'
														WHERE cp.`id_category` IN ('.implode(',', $categories).') '.$query_filters_where.'
														GROUP BY cp.id_product ORDER BY position, id_product', false);
		}
		Db::getInstance()->execute('ALTER TABLE '._DB_PREFIX_.'cat_filter_restriction ADD PRIMARY KEY (id_product), ADD KEY (position, id_product) USING BTREE', false);

		if (isset($price_filter) && $price_filter) {
			static $ps_layered_filter_price_usetax = null;
			static $ps_layered_filter_price_rounding = null;

			if ($ps_layered_filter_price_usetax === null) {
				$ps_layered_filter_price_usetax = Configuration::get('PS_LAYERED_FILTER_PRICE_USETAX');
			}

			if ($ps_layered_filter_price_rounding === null) {
				$ps_layered_filter_price_rounding = Configuration::get('PS_LAYERED_FILTER_PRICE_ROUNDING');
			}

			if (empty($selected_filters['category'])) {
				$all_products_out = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
				SELECT p.`id_product` id_product
				FROM `'._DB_PREFIX_.'product` p JOIN '._DB_PREFIX_.'category_product cp USING (id_product)
				INNER JOIN '._DB_PREFIX_.'category c ON (c.id_category = cp.id_category AND
					'.(Configuration::get('PS_LAYERED_FULL_TREE') ? 'c.nleft >= '.(int)$parent->nleft.'
					AND c.nright <= '.(int)$parent->nright : 'c.id_category = '.(int)$id_parent).'
					AND c.active = 1)
				'.$price_filter_query_out.'
				'.$query_filters_from.'
				WHERE 1 '.$query_filters_where.' GROUP BY cp.id_product');
			} else {
				$all_products_out = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
				SELECT p.`id_product` id_product
				FROM `'._DB_PREFIX_.'product` p JOIN '._DB_PREFIX_.'category_product cp USING (id_product)
				'.$price_filter_query_out.'
				'.$query_filters_from.'
				WHERE cp.`id_category` IN ('.implode(',', $categories).') '.$query_filters_where.' GROUP BY cp.id_product');
			}

			/* for this case, price could be out of range, so we need to compute the real price */
			foreach($all_products_out as $product) {
				$price = Product::getPriceStatic($product['id_product'], $ps_layered_filter_price_usetax);
				if ($ps_layered_filter_price_rounding) {
					$price = (int)$price;
				}
				if ($price < $price_filter['min'] || $price > $price_filter['max']) {
					// out of range price, exclude the product
					$product_id_delete_list[] = (int)$product['id_product'];
				}
			}
			if (!empty($product_id_delete_list)) {
				Db::getInstance()->execute('DELETE FROM '._DB_PREFIX_.'cat_filter_restriction WHERE id_product IN ('.implode(',', $product_id_delete_list).')');
			}
		}
		$this->nbr_products = Db::getInstance()->getValue('SELECT COUNT(*) FROM '._DB_PREFIX_.'cat_filter_restriction');

		if ($this->nbr_products == 0)
			$this->products = array();
		else
		{
			$product_per_page = isset($this->context->cookie->nb_item_per_page) ? (int)$this->context->cookie->nb_item_per_page : Configuration::get('PS_PRODUCTS_PER_PAGE');
			$default_products_per_page = max(1, (int)Configuration::get('PS_PRODUCTS_PER_PAGE'));
		        $n = $default_products_per_page;
		        if (isset($this->context->cookie->nb_item_per_page)) {
		            $n = (int)$this->context->cookie->nb_item_per_page;
		        }
		        if ((int)Tools::getValue('n')) {
		            $n = (int)Tools::getValue('n');
		        }
			$nb_day_new_product = (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20);

			if (version_compare(_PS_VERSION_, '1.6.1', '>=') === true)
      {
      $this->products = Db::getInstance()->executeS('
      SELECT
      p.*,
      '.($alias_where == 'p' ? '' : 'product_shop.*,' ).'
      '.$alias_where.'.id_category_default,
      pl.*,
      image_shop.`id_image` id_image,
      il.legend,
      m.name manufacturer_name,
      '.(Combination::isFeatureActive() ? 'product_attribute_shop.id_product_attribute id_product_attribute,' : '').'
      DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
      stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity' : '').'
      FROM '._DB_PREFIX_.'cat_filter_restriction 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_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')
       
      LEFT JOIN `'._DB_PREFIX_.'category_product` p_cat ON (p.id_product = p_cat.id_product)
       
      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)$cookie->id_lang.')
      LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
      '.Product::sqlStock('p', 0).'
      WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog") 
       
      AND p_cat.id_category = '.(int)$id_parent.'
       
      ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.
      ' LIMIT '.(((int)$this->page - 1) * $n.','.$n));
      }
      else
      {
      $this->products = Db::getInstance()->executeS('
      SELECT
      p.*,
      '.($alias_where == 'p' ? '' : 'product_shop.*,' ).'
      '.$alias_where.'.id_category_default,
      pl.*,
      MAX(image_shop.`id_image`) id_image,
      il.legend,
      m.name manufacturer_name,
      '.(Combination::isFeatureActive() ? 'MAX(product_attribute_shop.id_product_attribute) id_product_attribute,' : '').'
      DATEDIFF('.$alias_where.'.`date_add`, DATE_SUB("'.date('Y-m-d').' 00:00:00", INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
      stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity'.(Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '').'
      FROM '._DB_PREFIX_.'cat_filter_restriction 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` pa ON (p.`id_product` = pa.`id_product`)
      '.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop='.(int)$context->shop->id):'').'
      LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')
       
      LEFT JOIN `'._DB_PREFIX_.'category_product` p_cat ON (p.id_product = p_cat.id_product)
       
      LEFT JOIN `'._DB_PREFIX_.'image` i  ON (i.`id_product` = p.`id_product`)'.
      Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
      LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$cookie->id_lang.')
      LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
      '.Product::sqlStock('p', 0).'
      WHERE '.$alias_where.'.`active` = 1 AND '.$alias_where.'.`visibility` IN ("both", "catalog")
       
      AND p_cat.id_category = '.(int)$id_parent.'
       
      GROUP BY product_shop.id_product
      ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' , cp.id_product'.
      ' LIMIT '.(((int)$this->page - 1) * $n.','.$n));
      }
		}

		if (Tools::getProductsOrder('by', Tools::getValue('orderby'), true) == 'p.price')
			Tools::orderbyPrice($this->products, Tools::getProductsOrder('way', Tools::getValue('orderway')));

		return $this->products;
	}
}

Tools.php :

<?php
/**
 * 2007-2016 PrestaShop
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://opensource.org/licenses/osl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to [email protected] so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
 * versions in the future. If you wish to customize PrestaShop for your
 * needs please refer to http://www.prestashop.com for more information.
 *
 *  @author  PrestaShop SA <[email protected]>
 *  @copyright  2007-2016 PrestaShop SA
 *  @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 *  International Registered Trademark & Property of PrestaShop SA
 */

class Tools extends ToolsCore
{
    /**
     * Get products order field name for queries.
     *
     * @param string $type by|way
     * @param string $value If no index given, use default order from admin -> pref -> products
     * @param bool|\bool(false)|string $prefix
     *
     * @return string Order by sql clause
     */
    public static function getProductsOrder($type, $value = null, $prefix = false)
    {
        switch ($type) {
            case 'by':
                $list = array(0 => 'name', 1 => 'price', 2 => 'date_add', 3 => 'date_upd', 4 => 'position', 5 => 'manufacturer_name', 6 => 'quantity', 7 => 'reference');
                $value = (is_null($value) || $value === false || $value === '') ? (int)Configuration::get('PS_PRODUCTS_ORDER_BY') : $value;
                $value = (isset($list[$value])) ? $list[$value] : ((in_array($value, $list)) ? $value : 'position');
                $order_by_prefix = '';
                if ($prefix) {
                    if ($value == 'id_product' || $value == 'date_add' || $value == 'date_upd' || $value == 'price') {
                        $order_by_prefix = 'p.';
                    } elseif ($value == 'name') {
                        $order_by_prefix = 'pl.';
                    } elseif ($value == 'manufacturer_name' && $prefix) {
                        $order_by_prefix = 'm.';
                        $value = 'name';
                    } elseif ($value == 'position' || empty($value)) {
                        $order_by_prefix = 'p_cat.';
                    }
                }

                return $order_by_prefix.$value;
            break;

            case 'way':
                $value = (is_null($value) || $value === false || $value === '') ? (int)Configuration::get('PS_PRODUCTS_ORDER_WAY') : $value;
                $list = array(0 => 'asc', 1 => 'desc');
                return ((isset($list[$value])) ? $list[$value] : ((in_array($value, $list)) ? $value : 'asc'));
            break;
        }
    }
}

But when I go in the FO on a product listing page, I have that : https://megabackup.com/z/569o

 

Zesei, could you please tell me exactly what you do ?

 

What's wrong ?

 

Thank you in advance !

[cedric_charles]

Ok it seems that we can't override blocklayered.php, it should be directly in /modules/blocklayered/blocklayered.php, not /override/modules/blocklayered/blocklayered.php ?

[cedric_charles]

Hello rachid.antipodes,

 

I have a problem with your code. After your changes, the pagination of the list of products is wrong : For example I have 34 pages but they are empty after 17. (and 17 is the right number of course...)

 

Thank you in advance !

[Minimalweb]

Hi, the rachid.antipodes, on 17 Dec 2015 solution worked for me, but only for last level categories. When i try to sort products in categories that have more categories inside it doesn't. Any idea?

Edited June 2, 2016 by Minimalweb (see edit history)

[Tahsin_85]

Hello Rashid,

 

very nice, thanks for your solution.

 

But it cuts the products from subcategories in frontend. When you clicking next pages you get empty page where products from subcategories have to be.

[Tahsin_85]

This solution seems working:

AND p_cat.id_category = '.(int)$id_parent.' OR p_cat.id_category = p.id_category_default

[borisprado]

 

Hi bay_kolio,

 

I have 1.6.1.4 also.    The only way I can get items to stay in the correct place is like this.

 

Catalog>Products>Filter by category>Expand all> Select ONE category.    Once you are inside ONE single category you can move items around by grabbing the number arrow and moving it up or down.    Items will revert back to their old position if you do not select only one category before doing this.   If you do not see the movable arrows when you enter the category click the down arrow next to "position" and they will appear.

 

Hope this helps.

 

 

 

 

 

This solution works for me for almost all the products, some products still dont want to move, so i force the position with phpmyadmin