Magento 2.0.4 Layered Navigation Random Product Bug or Feature

[yagneshponkiya]

Steps to reproduce

1. Disable Full Page Cache From Magento Backend.
2. Go to Any Product Listing Page
3. Select any option from layered navigation which has multiple products available.
4. After selecting refresh the page 3-4 of times.

Make sure you have Order by "Position" selected. and i have tested this for all the product having "position value 1 or same for all the products"

Expected result

1. Product listing should not display random product order with each request.

Actual result

1. Product listing should stick with the order of products instead of random product with each refresh.

I have also tested the same thing without selection of layered navigation option. It stick with the product order return first time. If you choose any option from layered navigation it shows random order for filtered product with each page reload.

Example:

Let say,

1. I choose "color:yellow" from the layered navigation.
2. Filtered result return me 20 products.
3. Pager Limit display 10 product on each page. if i go to second page it will display couple of previous page products instead of new products.

Hope you get my concern.

Thanks

[yagneshponkiya]

Is there any updates on this issue ?

[piotrekkaminski]

@choukalos please check this one

[kanduvisla]

This problem still persists in Magento 2.1.2. Is there any update on this?

I noticed today that I cannot reproduce it locally, but only on the production environment. Could it be that it's some MYSQL setting that handles how entities with the same column value are sorted? (like, if you have 100 products in a category with position '0', are they ordered randomly or ordered by ID, or not at all and let the storage engine handle it (which seems the case right now)).

[kanduvisla]

Ok, so this is strange.

I thought that the sortorder issue might be database related. So I wrote a script that would update all the positions of the products in each category. Something like this:

$categoryIds = $this->connection->fetchCol(
    $this->connection->select()->distinct()->from('catalog_category_product', 'category_id'));

foreach ($categoryIds as $categoryId) {
    $entries = $this->connection->fetchAll(
        $this->connection->select()
            ->from('catalog_category_product')
            ->where('category_id = ?', $categoryId)
            ->order('position ASC'),
        [],
        \PDO::FETCH_ASSOC
    );

    foreach ($entries as $idx => $entry) {
        $this->connection->update(
            'catalog_category_product',
            ['position' => $idx],
            'entity_id = ' . $entry['entity_id']
        );
    }
}

I also flushed cached and re-indexed everything after this.

The strange part

When I go to the category in question, the products are still sorted randomly. However, when I reverse the sortorder by clicking the arrow next to the sorter, the URL gets appended with ?product_list_dir=desc. At this point, the products are sorted by position descending. Hurray so far!

When I click the arrow again, the ?product_list_dir=desc gets removed from the URL (removed, not replaced with ?product_list_dir=asc for example). The sorting works. It now sorts by the set position, ascending.

But... now when I reload the page, so the URL doesn't change, the products are sorted randomly again.

So there goes my theory about sorting that goes wrong when the positions are duplicate. I'll investigate it further...

[kanduvisla]

Okay, so I adjusted my script to filter out all products in my category that where set to be _'Not visibly Individually' by replacing the update script with the following:

foreach ($entries as $idx => $entry) {
    // Check if this product is allowed to be visible individually:
    $visiblity = $this->connection->fetchOne(
        $this->connection->select()
            ->from(['cpei' => 'catalog_product_entity_int'], 'value')
            ->join(['ea' => 'eav_attribute'], 'ea.attribute_id = cpei.attribute_id', null)
            ->where('cpei.entity_id = ?', $entry['product_id'])
            ->where('ea.attribute_code = ?', 'visibility')
            ->where('ea.entity_type_id = ?', 4)
    );

    if ((int) $visiblity === \Magento\Catalog\Model\Product\Visibility::VISIBILITY_NOT_VISIBLE) {
        // Delete this entry:
        $this->connection->delete('catalog_category_product', 'entity_id = ' . $entry['entity_id']);
    } else {
        // Update this entry:
        $this->connection->update(
            'catalog_category_product',
            ['position' => $idx],
            'entity_id = ' . $entry['entity_id']
        );
    }
}

But no luck there. Still the same behaviour is I write in my previous comment. But I guess we can now rule out the following:

• The actual set position in the database doesn't matter.
• Products that are set to 'not visible individually' do not matter.

I noticed that the sorting option on the catalog page (which shows the proper results) is done by an XHR request. I'm now going to analyse the SQL queries built by the template and by the XHR request to see if they can explain what might be going wrong.

[kanduvisla]

My previous comment made me wonder: Since I've noticed before that the URL changes, (with the addition of ?product_list_dir=desc), I wondered if reloading this page would show the correct results.

But no, reloading the page also resulted in the wrong results. It looks like the XHR request gets the sorting right, whereas the rendering from page initialisation doesn't.

[kanduvisla]

Another detail: sorting by page initialisation always seem to be by entity_id, ascending. Could it be that the sort direction is not taken into account at all?

[kanduvisla]

Hm, the only thing I notice when comparing SQL queries generated by ... is that when I sort the category with an XHR request, the line:

ORDER  BY `cat_index`.`position` ASC

gets added to the SQL queries. Which makes a lot of sense why they are sorted correctly. So the main question is, why isn't this sorting added when navigating to the category from an URL?

[kanduvisla]

ARGH! Kill me please...

Turns out there was an extension that did this to get the current product collection:

$categoryBlock = $this->getLayout()->getBlock('category.products.list');
$collection = $categoryBlock->getLoadedProductCollection();

Turns out that if you invoke getLoadedProductCollection() at this point, the sorting option is ignored. Why? I don't know. But at least that's the source of my problem. So it's not a Magento bug in my case...

Well I hope this keeps anyone else from wasting 3 hours of debugging and backtracking. Still not sure if this ticket can be closed though... @YaguPatel @piotrekkaminski can you confirm if this is still an issue or can this ticket be closed?

Edit: as for the 'why': the product list has some business logic in it's _beforeToHtml()-method, that handles the proper initializations of the sortable options and directions. Check out Magento\Catalog\Block\Product\ListProduct::_beforeToHtml() if you don't believe me. Trying to retrieve the product collection before _beforeToHtml() is triggered results in an unsorted product collection. And since the product collection is cached (see \Magento\Catalog\Block\Product\ListProduct::_getProductCollection()) any subsequent requests to fetch the collection will result the same - unsorted - collection.