Magento 2: Multiple Product Type Not Sorting Due To Amasty Extension

Question

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:

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

Amasty\Shopby\Model\ResourceModel\Fulltext\Collection\Interceptor

SELECT 
    `e`.*,
    `cat_index`.`position` AS `cat_index_position`,
    `price_index`.`price`,
    `price_index`.`tax_class_id`,
    `price_index`.`final_price`,
    IF(price_index.tier_price IS NOT NULL,
        LEAST(price_index.min_price,
                price_index.tier_price),
        price_index.min_price) AS `minimal_price`,
    `price_index`.`min_price`,
    `price_index`.`max_price`,
    `price_index`.`tier_price`,
    `stock_status_index`.`stock_status` AS `is_salable`
FROM
    `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
WHERE
    (e.entity_id IN ((SELECT 
            `cat`.`product_id`
        FROM
            `catalog_category_product_index_store1` AS `cat`
        WHERE
            (cat.category_id IN ('206')))))
ORDER BY `cat_index`.`position` ASC ,  `cat_index`.`position` ASC
LIMIT 18

I found out that WHERE is the problem here:

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

So I replace this WHERE with INNER JOIN

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:

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')],
            'cat.product_id'
        )->where($this->getConnection()->prepareSqlCondition('cat.category_id', ['in' => $values]));
        $selectCondition = [
            $this->mapConditionType($conditionType) => $categorySelect
        ];
        $whereCondition = $this->getConnection()->prepareSqlCondition('e.entity_id', $selectCondition);
        $this->getSelect()->where($whereCondition);
        $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:

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

Thank You...

Answer 1

I've tested it, please add this code:

    public function addIndexCategoriesFilter(array $categoriesFilter)
    {
        foreach ($categoriesFilter as $values) {
            if(count($values) > 1)
                $categoriesInCondString = implode(',',$values);
            else
                $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.')';
        $this->getSelect()->join(
            ['cat_custom' => 'catalog_category_product_index_store1'],
            $joinConditions,
            []
        );

        if($this->coreToolbar->getCurrentOrder() == 'position'){
            $this->getSelect()->order('cat_custom.position');
        }

        return $this;
    }

Answer 2

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