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

[romainruaud]

I'm moving this to Discussions because this is not an issue

[OvalMedia]

$this->getSelect()->reset(\Magento\Framework\DB\Select::WHERE);

This is the problem. This will erase any existing where clause. How is this not an issue?

$docIds in my case is a list of ALL product ids in the store.

For clarification, this is the query that arrives at _renderFiltersBefore():

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')) LIMIT 12

Then your code kills the WHERE part and instead ads a where clause including all entity_ids, not just the five in the original query.

[rbayet]

Hello @OvalMedia,

This is not an issue because this is by design.
In order for filtering, pagination and ordering of products to be consistent, there cannot be a filter that is applied only on the DB side.

That's why availability, visibility and stock status filters are "hardcoded" through XML requests definitions (see vendor/smile/elasticsuite/src/module-elasticsuite-catalog/etc/elasticsuite_search_request.xml)

    <request name="quick_search_container" label="Catalog Product Search" index="catalog_product" track_total_hits="true" fulltext="true">
        <filters>
            <filter name="stockFilter">Smile\ElasticsuiteCatalog\Model\Product\Search\Request\Container\Filter\Stock</filter>
            <filter name="visibleInSearch">Smile\ElasticsuiteCatalog\Model\Product\Search\Request\Container\Filter\VisibleInSearch</filter>

Otherwise, you would have the Elasticsuite request returning 20 products out of 1245 as per requested by the pagination, but "ooopsie" the DB filter limits that to 11 products and your first page of result/of the category contains fewer products than expected.

What's why the Elasticsuite product collection implements its own "addFieldToFilter" to intercept any proper attribute filtering to render it as an Elasticsearch request query filter.
It also allows to apply a pre-built query filter with "addQueryFilter".

My guess is that your "where" condition you're losing is not added to the collection thought the first method, but by doing a side effect on "collection->getCatalogPreparedSelect()".
That's not/no longer the way to do it if you use Elasticsuite (and probably if you're using the native Elasticsearch7 or Elasticsearch8 connectors either).

The correct approach is to find a way to replicate your "where" condition at the Elasticsearch request level.
It could be a simple as

• making sure the data on which you will be filtering is indexed into the product Elasticsuite index
• implementing and declaring a request filter like "stockFilter" and "visibleInSearch / visibleInCatalog" for the "quick_search_container", "catalog_product_autocomplete" and "catalog_view_container" requests.

I invite you to browse or wiki to see how to add the extra data you need through datasources and then how to implement the filtering.

Regards,

[rbayet]

Addendum for @OvalMedia, I scrolled up and saw indeed that the third party module is adding a SQL condition through addAttributeToFilter which the Elasticsuite product collection does not override.

It if where me, I would simply use the product collection to do a search on the sku, but if you want to stick to the approach of that module, you could simply replace

$result->distinct(true)->addAttributeToFilter('entity_id', ['in' => $this->helperData->getIdsBySku($sku)]);

by

 $result->addFieldToFilter('entity_id', ['in' => $this->helperData->getIdsBySku($sku)]);

Regards,

[OvalMedia]

Thx, just seconds before I came to the same conclusion. And, in fact, that seems to be the solution.