Add a where condition in ps_facetedsearch search provider query

[vincenzo.c]

I've been struggling for days with a problem in PrestaShop 8.1, please help me.
I need to add a where p.id_product IN ($array) in order to filter the products and show only "allowed" ones, I've gone deep down into the rabbit hole but couldn't manage to solve my problem.

These are my conclusions:

In classes/controller/ProductListingFrontControllerCore -> getProductSearchVariables -> provider = $this->getProductSearchProviderFromModules($query); (line 311), $provider is an object of type PrestaShop\Module\FacetedSearch\Product\SearchProvider.

The query is then executed at lines 356-359: $result = $provider->runQuery( $context, $query );

$result is an object of type PrestaShop\PrestaShop\Core\Product\Search\ProductSearchResult. The runQuery method is inside the interface PrestaShop\PrestaShop\Core\Product\Search\ProductSearchProviderInterface, which is then implemented by PrestaShop\Module\FacetedSearch\Product\SearchProvider along with the interface PrestaShop\PrestaShop\Core\Product\Search\FacetsRendererInterface.

The runQuery method within SearchProvider is at line 153, and products are extracted at lines 186-189: $productsAndCount = $filterProductSearch->getProductByFilters( $query, $facetedSearchFilters );

$filterProductSearch is an object of type PrestaShop\Module\FacetedSearch\Filters\Products and calls the getProductByFilters method (line 64 of Products). The products are then extracted at line 90: $matchingProductList = $this->searchAdapter->execute();

$this->searchAdapter is assigned during object construction: public function __construct(Search $productSearch) { $this->searchAdapter = $productSearch->getSearchAdapter(); }

The PrestaShop\Module\FacetedSearch\Product\Search object in turn calls PrestaShop\Module\FacetedSearch\Adapter\MySQL as an adapter, which extends PrestaShop\Module\FacetedSearch\Adapter\AbstractAdapter, which implements PrestaShop\Module\FacetedSearch\Adapter\InterfaceAdapter, where the execute method resides, not actually "valued" since it's inside an interface and not a class.

Consequently, this->searchAdapter->execute() calls the execute method of the MySQL class, which returns $this->getDatabase()->executeS($this->getQuery()).

At the moment I'm using the actionProductSearchProviderRunQueryAfter hook to filters results, but this one fires too late and doesn't fires at all with pagination, but still the first page has some missing products, showing less then 12 products in its grid.

This is how I filter out the products at the moment

public function hookActionProductSearchProviderRunQueryAfter($params)
    {

        if (!isset($params['query']) || !$params['query'] instanceof \PrestaShop\PrestaShop\Core\Product\Search\ProductSearchQuery) {
            return;
        }

        $groups = $this->context->customer->getGroups();
        $products = $params['result']->getProducts();
        $idCategory = null;
        $searchQuery = $params['query'];
        if ($searchQuery->getIdCategory()) {
            $idCategory = (int)$searchQuery->getIdCategory();
        } elseif (Tools::getValue('id_category')) {
            $idCategory = (int)Tools::getValue('id_category');
        }
    
        $filteredProducts = [];
        $allowedAttributes = $this->getAllowedAttributes($groups, $idCategory);

        if(!empty($allowedAttributes)) {
            $allowedProducts = $this->getAllowedProducts($allowedAttributes, $idCategory);

            foreach ($products as $product) {

                $productId = $product['id_product'];
                
                if(in_array($productId, array_column($allowedProducts, 'id_product'))) {
                    $filteredProducts[] = $product;
                } 
        
            }
        }
        
        $params['result']->setProducts($filteredProducts); 

    } 

I tried using the actionProductSearchProviderRunQueryBefore hook, I tried to create a custom search provider, I tried to override the MySQL class too but nothing worked. All I managed to do was to execute the query "manually" using reflectionclass, but this was useless.

 

public function hookActionProductSearchProviderRunQueryBefore($params)
    {

        $query = $params['query'];
       
        $providers = Hook::exec(
            'productSearchProvider',
            ['query' => $query],
            null,
            true
        );

        if (!is_array($providers)) {
            $providers = [];
        }

        foreach ($providers as $provider) {
            if ($provider instanceof ProductSearchProviderInterface) {
                $searchProvider = $provider;
                break;
            }
        }

        $reflectionProvider = new ReflectionClass($searchProvider);

        $moduleProperty = $reflectionProvider->getProperty('module');
        $moduleProperty->setAccessible(true);
        $module = $moduleProperty->getValue($searchProvider);
        $queryContext = $module->getContext();

        $filtersConverterProperty = $reflectionProvider->getProperty('filtersConverter');
        $filtersConverterProperty->setAccessible(true);
        $filtersConverter = $filtersConverterProperty->getValue($searchProvider);

        $searchFactoryProperty = $reflectionProvider->getProperty('searchFactory');
        $searchFactoryProperty->setAccessible(true);
        $searchFactory = $searchFactoryProperty->getValue($searchProvider);

        $facetedSearchFilters = $filtersConverter->createFacetedSearchFiltersFromQuery($query);
        $facetedSearch = $searchFactory->build($queryContext);
        $facetedSearch->setQuery($query);
        $facetedSearch->initSearch($facetedSearchFilters);

        if (!empty($facetedSearchFilters['id_attribute_group'])) {
            $facetedSearch->getSearchAdapter()->getInitialPopulation()->addSelectField('id_product_attribute');
            $facetedSearch->getSearchAdapter()->addSelectField('id_product_attribute');
        }

        $searchAdapter = $facetedSearch->getSearchAdapter();

        $reflectionAdapter = new ReflectionClass($searchAdapter);

        $addFilterMethod = $reflectionAdapter->getMethod('addFilter');
        $addFilterMethod->setAccessible(true);

        // just for testing purposes, TODO: implement actual logic
        $productIds = [415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523];

        $addFilterMethod->invoke($searchAdapter, 'id_product', $productIds, '=');
        
        $raw_query = $facetedSearch->getSearchAdapter()->getQuery();

        $getDatabaseMethod = $reflectionAdapter->getMethod('getDatabase');
        $getDatabaseMethod->setAccessible(true);
        $database = $getDatabaseMethod->invoke($searchAdapter);

        $executed_query = $database->executeS($raw_query);
    } 

Any help will be much appreciated, thanks in advance!

[Knowband Plugins]

Try to add this. 

public function hookActionProductSearchProviderRunQueryBefore($params)
{
    if (isset($params['query'])) {
        $params['query']->addFilter('id_product', ['operator' => 'IN', 'value' => [1, 2, 3, 4]]);
    }
}

 

Register the hook & Reset the module

[vincenzo.c]

Hi, thanks for the answer, sadly that method does not exists in PrestaShop\PrestaShop\Core\Product\Search\ProductSearchQuery.
Do you have any other advices?