Skip to main content

Magento 2.1.6 Product Grid page count and record count issue when programmatically adding filter using different methods

I have found a method that works. It seems that the Category view, regardless of a filter being applied or not, will ALWAYS use the Catalog Search module. Knowing that, we can override the following class to add a custom filter.
Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection
 But it's not that simple, first we have to create a custom filter in an xml document. Here are the steps:

Step 1

Create a search_request.xml file in Company/RegionManager/etc/search_request.xml. For my example I used the following code, you can replace any instance of deal_active with your custom attribute. Including the $deal_active$
<?xml version="1.0"?>
<requests xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:noNamespaceSchemaLocation="urn:magento:framework:Search/etc/search_request.xsd">
 <request query="catalog_view_container" index="catalogsearch_fulltext">
        <queries>
            <query xsi:type="boolQuery" name="catalog_view_container" boost="1">
                <queryReference clause="should" ref="active_query"/>
            </query>
            <query name="active_query" xsi:type="filteredQuery">
                <filterReference clause="must" ref="active_query_filter"/>
            </query>
        </queries>
        <filters>
            <filter xsi:type="termFilter" name="active_query_filter" field="deal_active" value="$deal_active$"/>
        </filters>
  <from>0</from>
        <size>10000</size>
    </request>
</requests>

Step 2

Override \Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection in the di.xml in your /etc/
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <preference for="Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection" type="Company\RegionManager\Model\ResourceModel\Fulltext\Collection" />
</config>

Step 3

In the Company\RegionManager\Model\ResourceModel\Fulltext\Collection.php file, replace the code between the two ADDING CUSTOM FILTER comments with your custom attribute.

<?php

namespace Company\RegionManager\Model\ResourceModel\Fulltext;

use Magento\CatalogSearch\Model\Search\RequestGenerator;
use Magento\Framework\DB\Select;
use Magento\Framework\Exception\StateException;
use Magento\Framework\Search\Adapter\Mysql\TemporaryStorage;
use Magento\Framework\Search\Response\QueryResponse;
use Magento\Framework\Search\Request\EmptyRequestDataException;
use Magento\Framework\Search\Request\NonExistingRequestNameException;
use Magento\Framework\Api\Search\SearchResultFactory;
use Magento\Framework\Exception\LocalizedException;
use Magento\Framework\App\ObjectManager;

/**
 * Fulltext Collection
 * @SuppressWarnings(PHPMD.CouplingBetweenObjects)
 */
class Collection extends \Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection
{

    /**
     * @var  QueryResponse
     * @deprecated
     */
    protected $queryResponse;

    /**
     * Catalog search data
     *
     * @var \Magento\Search\Model\QueryFactory
     * @deprecated
     */
    protected $queryFactory = null;

    /**
     * @var \Magento\Framework\Search\Request\Builder
     * @deprecated
     */
    private $requestBuilder;

    /**
     * @var \Magento\Search\Model\SearchEngine
     * @deprecated
     */
    private $searchEngine;

    /**
     * @var string
     */
    private $queryText;

    /**
     * @var string|null
     */
    private $order = null;

    /**
     * @var string
     */
    private $searchRequestName;

    /**
     * @var \Magento\Framework\Search\Adapter\Mysql\TemporaryStorageFactory
     */
    private $temporaryStorageFactory;

    /**
     * @var \Magento\Search\Api\SearchInterface
     */
    private $search;

    /**
     * @var \Magento\Framework\Api\Search\SearchCriteriaBuilder
     */
    private $searchCriteriaBuilder;

    /**
     * @var \Magento\Framework\Api\Search\SearchResultInterface
     */
    private $searchResult;

    /**
     * @var SearchResultFactory
     */
    private $searchResultFactory;

    /**
     * @var \Magento\Framework\Api\FilterBuilder
     */
    private $filterBuilder;

    /**
     * @param \Magento\Framework\Data\Collection\EntityFactory $entityFactory
     * @param \Psr\Log\LoggerInterface $logger
     * @param \Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy
     * @param \Magento\Framework\Event\ManagerInterface $eventManager
     * @param \Magento\Eav\Model\Config $eavConfig
     * @param \Magento\Framework\App\ResourceConnection $resource
     * @param \Magento\Eav\Model\EntityFactory $eavEntityFactory
     * @param \Magento\Catalog\Model\ResourceModel\Helper $resourceHelper
     * @param \Magento\Framework\Validator\UniversalFactory $universalFactory
     * @param \Magento\Store\Model\StoreManagerInterface $storeManager
     * @param \Magento\Framework\Module\Manager $moduleManager
     * @param \Magento\Catalog\Model\Indexer\Product\Flat\State $catalogProductFlatState
     * @param \Magento\Framework\App\Config\ScopeConfigInterface $scopeConfig
     * @param \Magento\Catalog\Model\Product\OptionFactory $productOptionFactory
     * @param \Magento\Catalog\Model\ResourceModel\Url $catalogUrl
     * @param \Magento\Framework\Stdlib\DateTime\TimezoneInterface $localeDate
     * @param \Magento\Customer\Model\Session $customerSession
     * @param \Magento\Framework\Stdlib\DateTime $dateTime
     * @param \Magento\Customer\Api\GroupManagementInterface $groupManagement
     * @param \Magento\Search\Model\QueryFactory $catalogSearchData
     * @param \Magento\Framework\Search\Request\Builder $requestBuilder
     * @param \Magento\Search\Model\SearchEngine $searchEngine
     * @param \Magento\Framework\Search\Adapter\Mysql\TemporaryStorageFactory $temporaryStorageFactory
     * @param \Magento\Framework\DB\Adapter\AdapterInterface $connection
     * @param string $searchRequestName
     * @param SearchResultFactory $searchResultFactory
     * @SuppressWarnings(PHPMD.ExcessiveParameterList)
     */
    public function __construct(
        \Magento\Framework\Data\Collection\EntityFactory $entityFactory,
        \Psr\Log\LoggerInterface $logger,
        \Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy,
        \Magento\Framework\Event\ManagerInterface $eventManager,
        \Magento\Eav\Model\Config $eavConfig,
        \Magento\Framework\App\ResourceConnection $resource,
        \Magento\Eav\Model\EntityFactory $eavEntityFactory,
        \Magento\Catalog\Model\ResourceModel\Helper $resourceHelper,
        \Magento\Framework\Validator\UniversalFactory $universalFactory,
        \Magento\Store\Model\StoreManagerInterface $storeManager,
        \Magento\Framework\Module\Manager $moduleManager,
        \Magento\Catalog\Model\Indexer\Product\Flat\State $catalogProductFlatState,
        \Magento\Framework\App\Config\ScopeConfigInterface $scopeConfig,
        \Magento\Catalog\Model\Product\OptionFactory $productOptionFactory,
        \Magento\Catalog\Model\ResourceModel\Url $catalogUrl,
        \Magento\Framework\Stdlib\DateTime\TimezoneInterface $localeDate,
        \Magento\Customer\Model\Session $customerSession,
        \Magento\Framework\Stdlib\DateTime $dateTime,
        \Magento\Customer\Api\GroupManagementInterface $groupManagement,
        \Magento\Search\Model\QueryFactory $catalogSearchData,
        \Magento\Framework\Search\Request\Builder $requestBuilder,
        \Magento\Search\Model\SearchEngine $searchEngine,
        \Magento\Framework\Search\Adapter\Mysql\TemporaryStorageFactory $temporaryStorageFactory,
        \Magento\Framework\DB\Adapter\AdapterInterface $connection = null,
        $searchRequestName = 'catalog_view_container',
        SearchResultFactory $searchResultFactory = null
    ) {
        $this->queryFactory = $catalogSearchData;
        if ($searchResultFactory === null) {
            $this->searchResultFactory = \Magento\Framework\App\ObjectManager::getInstance()
                ->get('Magento\Framework\Api\Search\SearchResultFactory');
        }
        parent::__construct(
            $entityFactory,
            $logger,
            $fetchStrategy,
            $eventManager,
            $eavConfig,
            $resource,
            $eavEntityFactory,
            $resourceHelper,
            $universalFactory,
            $storeManager,
            $moduleManager,
            $catalogProductFlatState,
            $scopeConfig,
            $productOptionFactory,
            $catalogUrl,
            $localeDate,
            $customerSession,
            $dateTime,
            $groupManagement,
            $catalogSearchData,
            $requestBuilder,
            $searchEngine,
            $temporaryStorageFactory,
            $connection,
            $searchRequestName,
            $searchResultFactory
        );
        $this->requestBuilder = $requestBuilder;
        $this->searchEngine = $searchEngine;
        $this->temporaryStorageFactory = $temporaryStorageFactory;
        $this->searchRequestName = $searchRequestName;
    }

    /**
     * @inheritdoc
     */
    protected function _renderFiltersBefore()
    {
        $this->getSearchCriteriaBuilder();
        $this->getFilterBuilder();
        $this->getSearch();
        if ($this->queryText) {
            $this->filterBuilder->setField('search_term');
            $this->filterBuilder->setValue($this->queryText);
            $this->searchCriteriaBuilder->addFilter($this->filterBuilder->create());
        }
        $priceRangeCalculation = $this->_scopeConfig->getValue(
            \Magento\Catalog\Model\Layer\Filter\Dynamic\AlgorithmFactory::XML_PATH_RANGE_CALCULATION,
            \Magento\Store\Model\ScopeInterface::SCOPE_STORE
        );
        if ($priceRangeCalculation) {
            $this->filterBuilder->setField('price_dynamic_algorithm');
            $this->filterBuilder->setValue($priceRangeCalculation);
            $this->searchCriteriaBuilder->addFilter($this->filterBuilder->create());
        }
        // ADDING CUSTOM FILTER START
        $this->filterBuilder->setField('deal_active');
        $this->filterBuilder->setValue('yes');
        $this->filterBuilder->setConditionType('eq');
        $this->searchCriteriaBuilder->addFilter($this->filterBuilder->create());
        // ADDING CUSTOM FILTER END

        $searchCriteria = $this->searchCriteriaBuilder->create();
        $searchCriteria->setRequestName($this->searchRequestName);
        try {
            $this->searchResult = $this->getSearch()->search($searchCriteria);
        } catch (EmptyRequestDataException $e) {
            /** @var \Magento\Framework\Api\Search\SearchResultInterface $searchResult */
            $this->searchResult = $this->searchResultFactory->create()->setItems([]);
        } catch (NonExistingRequestNameException $e) {
            $this->_logger->error($e->getMessage());
            throw new LocalizedException(__('Sorry, something went wrong. You can find out more in the error log.'));
        }
        $temporaryStorage = $this->temporaryStorageFactory->create();
        $table = $temporaryStorage->storeApiDocuments($this->searchResult->getItems());
        $this->getSelect()->joinInner(
            [
                'search_result' => $table->getName(),
            ],
            'e.entity_id = search_result.' . TemporaryStorage::FIELD_ENTITY_ID,
            []
        );

        $this->_totalRecords = $this->searchResult->getTotalCount();

        if ($this->order && 'relevance' === $this->order['field']) {
            $this->getSelect()->order('search_result.'. TemporaryStorage::FIELD_SCORE . ' ' . $this->order['dir']);
        }
        //return parent::_renderFiltersBefore();
        return \Magento\Catalog\Model\ResourceModel\Product\Collection::_renderFiltersBefore();
    }
    /**
     * @deprecated
     * @return \Magento\Framework\Api\Search\SearchCriteriaBuilder
     */
    private function getSearchCriteriaBuilder()
    {
        if ($this->searchCriteriaBuilder === null) {
            $this->searchCriteriaBuilder = ObjectManager::getInstance()
                ->get('\Magento\Framework\Api\Search\SearchCriteriaBuilder');
        }
        return $this->searchCriteriaBuilder;
    }

    /**
     * @deprecated
     * @return \Magento\Framework\Api\FilterBuilder
     */
    private function getFilterBuilder()
    {
        if ($this->filterBuilder === null) {
            $this->filterBuilder = ObjectManager::getInstance()->get('\Magento\Framework\Api\FilterBuilder');
        }
        return $this->filterBuilder;
    }

    /**
     * @deprecated
     * @return \Magento\Search\Api\SearchInterface
     */
    private function getSearch()
    {
        if ($this->search === null) {
            $this->search = ObjectManager::getInstance()->get('\Magento\Search\Api\SearchInterface');
        }
        return $this->search;
    }

    /**
     * Return field faceted data from faceted search result
     *
     * @param string $field
     * @return array
     * @throws StateException
     */
    public function getFacetedData($field)
    {
        $this->_renderFilters();
        $result = [];
        $aggregations = $this->searchResult->getAggregations();
        // This behavior is for case with empty object when we got EmptyRequestDataException
        if (null !== $aggregations) {
            $bucket = $aggregations->getBucket($field . RequestGenerator::BUCKET_SUFFIX);
            if ($bucket) {
                foreach ($bucket->getValues() as $value) {
                    $metrics = $value->getMetrics();
                    $result[$metrics['value']] = $metrics;
                }
            } else {
                throw new StateException(__('Bucket does not exist'));
            }
        }
        return $result;
    }
}

Comments

  1. Unknown25 May 2018 at 03:30


    Magento is one of the most popular eCommerce platform, customizable shopping cart platforms available. If you have chosen Magento as your eCommerce platform (WebSite), Acelerar can offer you end-to-end support with our efficient and streamlined Magento Bulk Product Upload Services at the most economical rates and 24x7 Support. Our Experts have a wealth of experience working on Magento Product Upload Services on eCommerce platform.

    ReplyDelete
  2. Bhavesh Kavad12 July 2018 at 21:40

    Thank you for sharing excellent information. Your website is very cool. Fully useful your blog post... Online Shopping Websites In India

    ReplyDelete
  3. SHARESOFT19 July 2019 at 00:40

    Magento is highly trustable e-commerce platform by worlds leading brands and also it is used by most of the small businesses and startup level companies. After few years of development Magento 2 were released for their customers. We upgrade your existing magento 1.x shop to magento 2 or built a new Magento 2 website from scratch with enhanced Scalability, Performance and security.

    Magento Migration Link: https://www.sharesoft.in/do-you-need-magento-1-to-magento-2-data-migration-without-any-problem/

    ReplyDelete

Post a Comment

Popular posts from this blog

Magento 2 product collection Filtering multi-select attribute values

  If you have multi-select attribute of product like below If you want filter value for this option Use below syntax to get product data: ->addAttributeToFilter('store_model', array('finset' => $params['store_model'])) finset key is used for multiselect attribute filter. $objectManager = \Magento\Framework\App\ObjectManager::getInstance(); $products = $objectManager->get('Magento\Catalog\Model\Product')         ->getCollection()         ->addAttributeToSelect('*')         ->addAttributeToSelect('store_brand')         ->addAttributeToSelect('store_model')         ->addAttributeToSelect('store_year')         ->addAttributeToFilter('store_brand', array('finset' => $params['store_brand']))         ->addAttributeToFilter('store_model', array('finset' => $params['store_model']))         ->ad...
6 comments
Read more

magento 2 best seller product display based on current category and subcategories

Magento 2 getting best seller based on category wise displaying i have done below for that to implement this functionality it is working fine for me. step 1 create block file in our module folder <?php namespace Pawan\Bestseller\Block; use Magento\Catalog\Api\CategoryRepositoryInterface; class Bestsellercategory extends \Magento\Catalog\Block\Product\ListProduct {     /**      * Product collection model      *      * @var Magento\Catalog\Model\Resource\Product\Collection      */     protected $_collection;     /**      * Product collection model      *      * @var Magento\Catalog\Model\Resource\Product\Collection      */     protected $_productCollection;     /**      * Image helper      *      * @var Magento\Catalog\Helper\Image     ...
6 comments
Read more

Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous

When trying to filter sales order grid with From and To dates it was redirecting to dashboard.after that again i tried to open sales order grind it is generating reports in reports file it showing. "Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous" means it is finding a another created_at field. because when we adding or joining the other table then it has also a field named as created_at. So below is the  solution for this error. magento that created_at is of the main_table not of my custom table. Find the below code in the sales order grid.php file. $this->addColumn('created_at', array(            'header' => Mage::helper('sales')->__('Purchased On'),             'index' => 'created_at',             'type' => 'datetime',             'width' => '100px',         )); ...
Post a Comment
Read more