Fulltext Collection modifies query, losing where clause

[OvalMedia]

Magento: 2.4.3-p1
ES: 2.10.23.2

I am not sure if this is a bug on your part or a bug at all, but...

Additionally to the ES module I am running a highly modified version of the mageplaza product finder module.
It comes with a custom landingpage using it's own controller. On that controller the core's productlist (Magento\Catalog\Block\Product\ListProduct) block is embedded. Via plugin the productlist collection is modified reflecting the module's special needs.

In afterGetProductCollection of the Magento\Catalog\Model\Layer class the plugin modifies the collection's query adding a list of SKUs:
$result->distinct(true)->addAttributeToFilter('entity_id', ['in' => $this->helperData->getIdsBySku($sku)]);

But in the frontend this limitation is gone, magento reverts back to an unfiltered list of ALL products only limited to a default page size of 12 (set by the product finder module).

Upon further investigation I found that the load() command in Magento\Catalog\Block\Product\ListProduct::_beforeToHtml() is somehow responsible.

I added this to the plugin to compare the queries before and after:

echo '<pre>' . $result->getSelect() . '</pre>';
$result->load();
echo '<pre>' . $result->getSelect() . '</pre>';

Before:

SELECT DISTINCT  `e`.*, `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` FROM `catalog_product_entity` AS `e`
 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' WHERE (`e`.`entity_id` IN('5725', '4337', '4303', '4392', '4598')) LIMIT 12

After (shortened):

`SELECT DISTINCT  `e`.*, `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_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 `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 (6446, 6445, 6444, 6443, [...])) ORDER BY FIELD(e.entity_id,6446,6445,6444,6443,[...])
 LIMIT 12`

The last query actually contains ALL entity_ids available in the store, not only the 5 from the first query.
I then disabled all Smile modules and went back to magento's core adapter for Elasticsearch 7.
While the first query did not change, this is the second one (shortened again):

SELECT DISTINCT  `e`.*, `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_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 `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('5725', '4337', '4303', '4392', '4598')) AND (e.entity_id IN (4211, 4212, 4213, 4214, 4215, [...])) ORDER BY FIELD(e.entity_id,4211,4212,4213,4214,4215,4223,[...])
 LIMIT 12

This time there are two where clauses, the first one being the one that limits the query to the five desired products. The second one again listing all SKUs in the store. But since both are add using AND the second one does not have any effect.
In the frontend this yields the desired outcome.

My questions are: what, why, where does your module mangle with the query? Could this be an unwelcome sideeffect for third party modules?

[romainruaud]

https://github.com/Smile-SA/elasticsuite/blob/2.11.x/src/module-elasticsuite-catalog/Model/ResourceModel/Product/Fulltext/Collection.php#L517

Here we send a request to Elasticsearch for getting the appropriate results and then we filter the collection on the returning ids.

If you're having all your products ids in the result, it's likely that another module is manipulating the collection and reseting the filtering.

Regards