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 Commented Dec 31, 2019 at 4:56
  • @NareshRupareliya Its not he issue here I've updated my question with more detail. Commented Dec 31, 2019 at 11:11
  • Is your store migrated from m1 to m2 ? Commented Dec 31, 2019 at 13:51

2 Answers 2


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;

I have exactly the same problem (just migrate from m1 to last magento 2.4.3 version ) All my configurable product are at the end of the list and I'm customer of many Amasty extension who interact in that case :

Amasty Shopby but ALSO AMASTY advanced Sorting ... Configurable are at the end and doens't respect the position

Waiting their support for answer , but if someone can tell who use their extension can tell me what is the situation at this time ?

Also, i don't have the function addIndexCategoriesFilter in the last version of their shopby extension ... so if someone can help me to find a fix ...

Thaks a lot

PS : if this is the same bug, not solved in their extension since a so long time, i'll suggest everybody to avoid amasty ! => i'll give you feedback about the situation when they reply to my support ticket

  • This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review
    – Brett
    Commented Oct 2, 2021 at 3:52
  • @ANGEL addIndexCategoriesFilter was removed by Amasty in Magento 2.3.5-p2 Commented Oct 4, 2021 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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