Prestashop: Product order is random, when ordering key is the same - product lost or visible on multiple pages

[Hlavtox]

Describe the bug

On several product ordering settings, products are positioned randomly. During browsing the category, you can see SINGLE product MULTIPLE times. Or, you cannot see some products at all. Merchants are loosing money there.

How to reproduce the bug

1. Make some products. Make enough so there are at least 2 pages. Set the same price to all of them.
2. Setup faceted search.
3. Go to category with faceted search. Order by price.
4. Hit F5, see products appear in random order.
5. Browse pages back and forth and you will see one product appear on multiple pages.

Other cases

• It also happens if you set ordering to "position" and don't have the proper positions set in the category.
• I think it will also happen if you set ordering to "quantity" and have several products with the same quantity.

Screenshots

I screenshotted 2 passes. I marked the duplicate products. You can see that 3 and 4 products are completely lost during browsing.

Possible solution

I think a possible solution to all of this problems would be to add a "fallback" ordering to every order way. It would sort it by position, quantity, price etc. AND then sort it by product_id. The position would then stay the same all the time.

I think simply adding , p.id_product ASC at the end of SQL queries would solve this problem.

Additional information

• PrestaShop version: 1.7.6.2, 1.7.7
• PHP version: 7.2
• Faceted searchversion: 3.4.0, 3.6.0

---

Steps to reproduce

First of all you need MariaDB to reproduce it with PS1.7.7.0

1- Create a category
2- Create 15 products belonging to this category (enough for having two pages in FO), give one of this product a quantity of 1, let the quantity field empty for the others
3- Go to module management, the menu module, and add your new category to the menu
4- In shop parameters > products parameters: set order to
4- In fo, go to your new category's page, products > and set order to Quantity in stock, Descending

1st problem: See that the first product is the one with a quantity, which is normal, but other products are in a random order.
2nd problem: Sometimes there's products that show up multiple times in different pages

Additional information

• PrestaShop version: 1.7.7.0
• PHP version: 7.1
• MariaDB version: 10.1

[Hlavtox]

UPDATE 17.4.2020 1:38

I am testing a solution:
Adding $query .= ', p.id_product ASC';
After $query .= ' ORDER BY ' . $orderField . ' ' . strtoupper($this->getOrderDirection());
In /modules/ps_facetedsearch/src/Adapter/MySQL.php
Seems to solve the issue in Faceted search. I will test the change in other controllers.

[szabeszg]

I was having similar issues in PrestaShop 1.7.6.4. Looks like the proposed pull request does solve my issues as well.

EDIT: while standard product listings of categories seem to work now, search results are not sorted by price.

[Hlavtox]

@szabeszg That's probably a different issue. This bug happens only in faceted search. Everywhere else, it's not random.

Your search sort problem could be this - https://github.com/PrestaShop/PrestaShop/issues/17212

[szabeszg]

Thanks @Hlavtox! I will read that thread properly and perforn more tests.

[matthieu-rolland]

Hi @Hlavtox ,

I reproduced your issue on 1.7.6.x, I could not reproduce in 1.7.7.x

How to reproduce on 1.7.6.x:

1- Create a category
2- Create 15 products belonging to this category (enough for having two pages in FO), give one of this product a quantity of 1, let the quantity field empty for the others
3- Go to module management, the menu module, and add your new category to the menu
4- In shop parameters > products parameters: set order to
4- In fo, go to your new category's page, products > and set order to Quantity in stock, Descending

See that the first product is the one with a quantity, which is normal, but other products are in a random order.

[Hlavtox]

@matthieu-rolland Good! :-)

I did not manage to reproduce in any other controller or in a category without faceted search.

Can you ping the dev team and ask, if it wouldn't be good to introduce this "secondary ORDER BY" in all controllers? Can we be 100% sure, that the sorting from MySQL will be always the same?

[colinegin]

@matthieu-rolland did you reproduce it on 1765 or only on the branch 176x ?

[marionf]

It's not labeled as a regression @matthieu-rolland could you confirm it's not a regression of 1.7.6.x ?

[matthieu-rolland]

I reproduced this bug on 1.7.6.x, but I didn't manage to reproduce it on 1.7.7.x

[colinegin]

Alright thanks @matthieu-rolland, then I think we can close this issue as we have no 1.7.6.6 expected.

[Hlavtox]

@colinegin Do think you can reopen it as 1.7.6.6 will be available? I think it's quite important for people to actually browse products...

[Hlavtox]

@PierreRambaud @matthieu-rolland Reopen pls, reproduced by khouloudbelguith here: https://github.com/PrestaShop/PrestaShop/issues/19810#issuecomment-700576564

It's not fixed in 1.7.7

[PierreRambaud]

@PierreRambaud @matthieu-rolland Reopen pls, reproduced by khouloudbelguith here: #19810 (comment)

It's not fixed in 1.7.7

If it's the same problem, it's a duplicate issue :thinking:

[Hlavtox]

@PierreRambaud No, she managed to reproduce it by accident in a different issue 😄

[marionf]

@hibatallahAouadni Could you please try to reproduce this issue on 1.7.7.0 and add appropriates labels ?

[hibatallahAouadni]

Hello @Hlavtox ,

2A. Hit F5, see products appear in random order.

1. I didn't managed to reproduce the issue with PS1.7.7.0 and ps_facetesearch disabled, see the attached screen record below:

https://drive.google.com/file/d/1jqAaGVFrI6Btu9UREBqm4GF694A9rIJl/view

1. PS1.7.7.0 and ps_facetesearch enabled v3.6.0, the products disappeared :sweat: :

https://drive.google.com/file/d/1LnuQOz8-PkZb__hzmEukqBXHQ2n1AcpD/view

2B. Browse pages back and forth and you will see one product appear on multiple pages.

This one, I didn't managed to reproduce with PS1.7.7.0 and ps_facetesearch disabled.
Please check and feedback.

Thanks!

[Hlavtox]

@hibatallahAouadni @PierreRambaud @marionf

Just reproduced it on clean Prestashop 1.7.7.0 and Faceted search 3.6.0. Order of products is absolutely random, if they have samy quantity or price.

You cannot see some products and you see some of them multiple times. This is an absolute conversion killer. If this is not critical, it's major at least... 😞

https://drive.google.com/file/d/1Uga47_vp1T7iEGRJVw2xnP7egG-Y7wey/view?usp=sharing

or live here
https://test177.trendo.cz/gb/3-clothes?order=product.price.asc

[hibatallahAouadni]

@Hlavtox I will try it with your currency

[hibatallahAouadni]

Sorry @Hlavtox even after installing the Czech republic, I didn't manage to reproduce it :sweat:
On your test website, I managed to reproduce it.
But the problem is, at first, there's no product with name Product 20, after filtering, this product appeared!!!

[Hlavtox]

@hibatallahAouadni Your bug fixing computer again 😄😄

Yeah, sometimes you just miss some products at all... :(

[hibatallahAouadni]

Well @marionf I have tried several times to reproduce the issue but I couldn't :confused:
I'm really sorry @Hlavtox

[Hlavtox]

@hibatallahAouadni @marionf @PierreRambaud It all comes down to the database. Maybe you have a different version. My DB is latest MariaDB with default configuration.

https://drive.google.com/file/d/1Uga47_vp1T7iEGRJVw2xnP7egG-Y7wey/view?usp=sharing

I opened a PR in April, which should fix it, but it fails checks because I did not update tests. Pierre, would you finish it? https://github.com/PrestaShop/ps_facetedsearch/pull/168

[hibatallahAouadni]

@Hlavtox My DB is MySQL version: 8.0.22
Hope it helps :wink:

[Hlavtox]

https://dba.stackexchange.com/a/6053

[PierreRambaud]

FYI, this issue is not major, it's purely trivial, it doesn't break anything or prevents people from buying something in your shop.
If you think you really need this, please take time to find a solution and create a pull request

[marionf]

Hello guys,

Before fixing an issue we need to reproduce it, if we can't reproduce it, then we can't fix it
Maybe some steps are missing if @hibatallahAouadni can't reproduce it
We need more informations to be able to reproduce it

[Hlavtox]

@marionf Your colleague reproduced it here - https://github.com/PrestaShop/PrestaShop/issues/19810#issuecomment-700576564. I reproduced it on two completely different servers and LAMP combinations.

@PierreRambaud I have it fixed for my clients, and on my PR, but I don't want to imagine how many people are losing sales. I think it does prevent people buying stuff. You have 2 pages of products. You browse page 1, you click page 2 and half of the products that should have been there are moved to page 1. You say "They don't have it here." and leave.

[marionf]

@Hlavtox yes, with PS1.7.6.8 and PS1770beta2 & ps_facetesearch v3.6.0
Now there is PS 1.7.7.0 and ps_facetesearch v3.7.0 and we need to reproduce issues on the last version(s)

[hibatallahAouadni]

Hello @Hlavtox

I ask @SD1982 to reprodude it with MariaDB, and he managed to reproduce it only once and then he couldn't, even with fresh install :confused:

As discussed with @marionf if we managed to reproduce at least one time, so we should add the label To Do :tada:
Waiting for your PR :stuck_out_tongue:

Thanks!

[PierreRambaud]

I manage to reproduce the issue, it happens when you disable the query cache in MySQL.
Here's a possible fix https://github.com/PrestaShop/ps_facetedsearch/pull/295