SELECT DISTINCT `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`, IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, IFNULL(review_summary.rating_summary, 0) AS `rating_summary`, CASE WHEN encora_rc_prices.start_dt <= '2025-09-25' AND (encora_rc_prices.end_dt >= '2025-09-25' OR encora_rc_prices.end_dt IS NULL) THEN encora_rc_prices.price ELSE encoranav_warehouse_item.price END AS `sort_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=172 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '1' LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product')) LEFT JOIN `mst_sorting_index` AS `mst_sorting_index_1` ON mst_sorting_index_1.product_id = e.entity_id AND mst_sorting_index_1.store_id = 1 LEFT JOIN `mst_sorting_index` AS `mst_sorting_index_0` ON mst_sorting_index_0.product_id = e.entity_id AND mst_sorting_index_0.store_id = 0 LEFT JOIN `catalog_category_product_index_store1` AS `sorting_position` ON sorting_position.product_id = e.entity_id AND sorting_position.store_id = 1 AND sorting_position.category_id IN (172) LEFT JOIN `catalog_product_entity` ON e.entity_id = catalog_product_entity.entity_id LEFT JOIN `encoranav_warehouse_item` ON catalog_product_entity.sku = encoranav_warehouse_item.sku AND encoranav_warehouse_item.price > 0 AND encoranav_warehouse_item.site_id = '' LEFT JOIN `encora_rc_prices` ON encoranav_warehouse_item.sku = encora_rc_prices.sku AND encora_rc_prices.price > 0 AND encora_rc_prices.rc_code = '' 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 (encoranav_warehouse_item.id IS NOT NULL) AND (e.entity_id IN (3465, 3466, 3683, 3692, 3693, 10468, 10486, 10495, 10498, 10499, 10500, 10534, 10552, 10553, 10578, 10740, 10743, 11367, 3643, 6066, 1797, 6849, 6527, 7871, 8258)) AND ((CASE WHEN (encora_rc_prices.start_dt <= '2025-09-25') AND ((encora_rc_prices.end_dt >= '2025-09-25') OR (encora_rc_prices.end_dt IS NULL)) THEN encora_rc_prices.price ELSE encoranav_warehouse_item.price END) > 0) ORDER BY sorting_position.position desc, sort_price asc, FIELD(e.entity_id,3465,3466,3683,3692,3693,10468,10486,10495,10498,10499,10500,10534,10552,10553,10578,10740,10743,11367,3643,6066,1797,6849,6527,7871,8258) LIMIT 12