Magento 2.2.6: Product collection does not apply the filter

Question

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:

\Magento\Catalog\Block\Product\ListProduct

And the method:

addAttributeToFilter

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

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