EDIT: I have seen that in magento 2 the ListProduct page is cached, for this reason once loaded it does not enter.

Is there any way to modify the query returned by ListProduct?

I extend the class:


And the method:


I do not apply the changes, when I look at the products that are inside the collection, they all come out to me.

If I get the query and use it in the sql server it shows me the well filtered products.

public function getLoadedProductCollection()

    $idCustomer = $this->getLoggedInCustomerId();

    if ($idCustomer) {
        $productCollection = $this->_getProductCollection();

         * items collection of table representative_brand
        $brandCollection = $this->representativeBrandCollection;
        $brandCollection->addFieldToFilter(self::CUSTOMER_FIELD, $idCustomer); 

        $items = $brandCollection->toArray()['items'];

        $arr_groups = [];
        if (count($items) > 0) {
            foreach ($items as $item) {
                $arr_groups[] =  $item[self::GROUP_FIELD];

        try {
             * attribute: sgi_itemgroup
            $productCollection->addAttributeToFilter(self::GROUP_ATTRIBUTE, ['in' => $arr_groups]);
        } catch (LocalizedException $e) {
            $this->logger->debug('Representative Brand: ' . $e);

        var_dump($productCollection->toArray()); die("stop");

        return $productCollection;

    return null;


Query of $productCollection:

SELECT e.*, cat_index.position AS cat_index_position, stock_status_index.stock_status AS is_salable, 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, at_sgi_itemgroup.value AS sgi_itemgroup 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='3' AND cat_index.is_parent=1 INNER 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 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 = '1' INNER JOIN search_tmp_5bb499868517e4_20951822 AS search_result ON e.entity_id = search_result.entity_id INNER JOIN catalog_product_entity_int AS at_sgi_itemgroup ON (at_sgi_itemgroup.entity_id = e.entity_id) AND (at_sgi_itemgroup.attribute_id = '205') AND (at_sgi_itemgroup.store_id = 0) WHERE (stock_status_index.stock_status = 1) AND (at_sgi_itemgroup.value IN('7', '7', '3')) ORDER BY cat_index.position asc, cat_index.position asc LIMIT 9

  • Have you found any error log? Oct 5, 2018 at 8:05
  • No, no error, only the filter does not apply and it returns all the products of the category.
    – chdzma
    Oct 5, 2018 at 8:10
  • please try to print query try { /** * attribute: sgi_itemgroup */ $productCollection->addAttributeToFilter(self::GROUP_ATTRIBUTE, ['in' => $arr_groups]); print_r($productCollection->getSelect()->__toString());die("here); } Query is right? Oct 5, 2018 at 8:12
  • Yes, I have also done tests attacking the database and it returns the correct results.
    – chdzma
    Oct 5, 2018 at 8:14
  • Have you override this class using plugin? Oct 5, 2018 at 8:17

1 Answer 1


For Magento 2, if you want to give custom conditions for product collection, your custom conditions works fine for your product collection in listing page but when you check for layered navigation in left sidebar, You may have wrong result for product collection.

When you set custom conditions for product collection by before or after plugin for getLoadedProductCollection() or override php file, that print proper query for your result and when you check that query with a database you gave the proper result for a product.

In Magento 2 you need to override or create a plugin for build() function of IndexBuilder.php file from Catalog search module to accomplish your task.

Check blog for details instructions, Set Custom conditions for product collection

You need to create module, to apply custom conditions to product collection and based on above blogs module you got a proper result for collection.

Refer blog and you got more idea for your issue, Set Custom conditions in layered navigation

  • Thanks for your answer, but in this method it is also cacheable, it is necessary that each time the request is made, the query is run as it filters by user id and each user has to see different products.
    – chdzma
    Oct 8, 2018 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.