1

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

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: 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')],
            '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:

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...

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

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;
    }
0

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

2

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.