Catalog search randomized results

[luismaldonadov]

Catalog results from a search within magento 2.1.1 appear to be randomized when expected to be the same ,even with page refresh or page navigation. There's other isue related to this one (4132) that appear to be an extension problem, but in this case i contacted the template creator and they told me this:

Actually, our theme is using the Search module- core functionality of Magento default. So, this is how the Search of Magento works. If you want to get the better appearance and results, then you should use an advanced Search extension

So, appears not to be an extension related problem.

Preconditions

1. Magento 2.1.1
2. PHP 7.0.13
3. MySQL 5.6.34-79.1

Steps to reproduce

1. Realize a search for any product
2. Navigate or refresh the page

Expected result

1. Consistent results for the generated page for the search term

Actual result

Result in this GIF. I'm refreshing the page, and as you can see different order for results.
https://gifyu.com/image/SoU6

[maghamed]

This happens because MySQL randomly returns documents which weights are the same.

For example,
providing generally used search term (like "bag" on a Bag Store )
Will always produce different order of results. Because most of products will contain this word, and MySQL assigns equivalent relevancy for all of them.

But if the search is more specific like Duffle Bag .
Even applying refresh many times -
First document is always the same (because its weight is higher than others).

What Magento could do here is to add additional sorting by entity_id, so all documents (read products) with the same weight would be sorted by product_id as well.

magento2/lib/internal/Magento/Framework/Search/Adapter/Mysql/Mapper.php

Line 167 in 0328a45

$select->order('relevance ' . Select::SQL_DESC);

instead of
$select->order('relevance ' . Select::SQL_DESC);

have something like:

$select->order('relevance ' . Select::SQL_DESC . ', entity_id ' . Select::SQL_DESC) ;

What @wicho91 you could do now, not changing Core code is to pluginize
MySQL\Mapper::buildQuery(RequestInterface $request) method

magento2/lib/internal/Magento/Framework/Search/Adapter/Mysql/Mapper.php

Line 133 in 0328a45

public function buildQuery(RequestInterface $request)

with afterPlugin and add ordering which I mentioned above.
It's pretty easy to do, because current method returns $select object.

[luismaldonadov]

Hello @maghamed thank you for the detailed response, i've just changed this part of the code. Im fairly new to web development as i spent 2+ years in desktop development. So will look into jQuery afterPlugin because im really blank here. But thank you again for the awesome help! Ill keep posted!

[choukalos]

Hi @wicho91 - could you put in a PR for this? Thanks!

[maghamed]

@wicho91 I meant not jQuery plugins, because jQuery is front-end JavaScript framework, but Magento Plugins, which is extension mechanism provided by Magento Framework. You can read the documentation of how Plugins could be used following the link above.

You are welcome to prepare Pull Request for Core changes.

[luismaldonadov]

@maghamed Even beter. You've guide me the correct way. I'll dig deep into this. For me it's a pretty and obvious need to have consistent information. Thank you so much!

[richievalsalan]

i am working on this