I'm facing a problem due to Amasty Extension. I have a category which has 1 simple product and some configurable products, but when I try to sort them, simple product always comes first although configurable product did sort as I need but simple product always comes first here are some screenshots:

enter image description hereenter image description here

You can see I sort simple product on 5th position but it always comes first, so I try to debug and echo the Magento_Catalog/templates/product/list.phtml

$_productCollection = $block->getLoadedProductCollection();
echo get_class($_productCollection);
echo "<br>".$_productCollection->getSelect()->__toString();

And I got this query with class that goes to Amasty


    `cat_index`.`position` AS `cat_index_position`,
    IF(price_index.tier_price IS NOT NULL,
        price_index.min_price) AS `minimal_price`,
    `stock_status_index`.`stock_status` AS `is_salable`
    `catalog_product_entity` AS `e`
        INNER JOIN
    `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id = e.entity_id
        AND cat_index.store_id = 1
        AND cat_index.visibility IN (2 , 4)
        AND cat_index.category_id = 59
        INNER JOIN
    `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id
        AND price_index.website_id = '1'
        AND price_index.customer_group_id = '2'
        LEFT JOIN
    `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id
        AND stock_status_index.website_id = 0
        AND stock_status_index.stock_id = 1
    (e.entity_id IN ((SELECT 
            `catalog_category_product_index_store1` AS `cat`
            (cat.category_id IN ('206')))))
ORDER BY `cat_index`.`position` ASC ,  `cat_index`.`position` ASC

I found out that WHERE is the problem here:

    (e.entity_id IN ((SELECT 
            `catalog_category_product_index_store1` AS `cat`
            (cat.category_id IN ('206')))))

So I replace this WHERE with INNER JOIN

    `catalog_category_product_index_store1` AS `cat` ON cat.product_id = e.entity_id
        AND cat.store_id = 1
        AND cat.visibility IN (2 , 4)
        AND cat.category_id IN ('206')

after that I got product sorting like this: enter image description here

You can see with the INNER JOIN I got all the product at the exact position as I want it to be. So I need to apply it in Magneto and I found that class I mention before Amasty\Shopby\Model\ResourceModel\Fulltext\Collection.php, I start debugging and found that this function made the WHERE query:

public function addIndexCategoriesFilter(array $categoriesFilter)
    foreach ($categoriesFilter as $conditionType => $values) {
        $categorySelect = $this->getConnection()->select()->from(
            ['cat' => $this->getTable('catalog_category_product_index')],
        )->where($this->getConnection()->prepareSqlCondition('cat.category_id', ['in' => $values]));
        $selectCondition = [
            $this->mapConditionType($conditionType) => $categorySelect
        $whereCondition = $this->getConnection()->prepareSqlCondition('e.entity_id', $selectCondition);
        $this->requestBuilder->bind(Category::ATTRIBUTE_CODE, $values);
    return $this;

I try to replace WHERE with INNER JOIN but I don't know how to do that, please help me out with this.

ALSO My sorting mess up if I check on multi categories option a feature Amasty Gave (MULTI SELECT), if I check on single category my URL generate something like this and my product doesn't sort I set it on back-end see the image below:

enter image description here

BUT IF I manually generate URL with single category, product sorting works fine, see image below:

enter image description here

Thank You...

  • magento sort product collection based on product min_price. so can u please check catalog_product_index_price this table in database and check all product min price. may be min_price of your simple product would be more than configured product. that's why may be your simple product would be displayed first – Naresh Rupareliya Dec 31 '19 at 4:56
  • @NareshRupareliya Its not he issue here I've updated my question with more detail. – Partab Saifuddin Zakir Dec 31 '19 at 11:11
  • Is your store migrated from m1 to m2 ? – Dinesh Rajput Dec 31 '19 at 13:51

I've tested it, please add this code:

    public function addIndexCategoriesFilter(array $categoriesFilter)
        foreach ($categoriesFilter as $values) {
            if(count($values) > 1)
                $categoriesInCondString = implode(',',$values);
                $categoriesInCondString = $values[0];

        $joinConditions = 'cat_custom.product_id = e.entity_id AND cat_custom.store_id=1 
        AND cat_custom.visibility IN(2, 4) AND cat_custom.category_id IN ('.$categoriesInCondString.')';
            ['cat_custom' => 'catalog_category_product_index_store1'],

        if($this->coreToolbar->getCurrentOrder() == 'position'){

        return $this;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.