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 "
".$_productCollection->getSelect()->__toString();
And I got this query with class that goes to Amasty
AmastyShopbyModelResourceModelFulltextCollectionInterceptor
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 AmastyShopbyModelResourceModelFulltextCollection.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.
Thank You.