0

I have a Model in my code that creates a custom product collection based on a user search term. That search term should search products and filter it by sku or name... The issue is that it takes too much time to retrieve the data, maybe from 4 to 9 seconds... my catalog has 8000 skus. I would like to optimize this filter. At the same time I have flat catalog enabled as well as categories. And I'm also have Elasticsearch enabled (My current installation has Magento 2.3.5p1). Here is my code:

public function getSearchResult($queryText)
{
    try {
        /** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $productCollection */
        $productCollection = $this->layerResolver->get()->getProductCollection();

        $queryLike = $this->_getQueryPattern($queryText);

        $productCollection
            ->setVisibility([
                Visibility::VISIBILITY_BOTH,
                Visibility::VISIBILITY_IN_SEARCH,
                Visibility::VISIBILITY_IN_CATALOG
            ])
            ->addFieldToFilter('status', Status::STATUS_ENABLED)
            ->addAttributeToFilter('type_id', ['neq' => ProductType::TYPE_BUNDLE])
            ->addAttributeToFilter(
                [
                    ['attribute' => 'sku', 'like' => $queryLike],
                    ['attribute' => 'name', 'like' => $queryLike]
                ]
            );
        $productCollection->getSelect()->limit($this->searchModel->getMaxResShow());

        $this->stockFilter->addInStockFilterToCollection($productCollection);

        $productCollection = $this->searchModel->getResData($productCollection);

        $query_test = $productCollection->getSelect()->__toString();

        $this->_logger->debug(print_r($query_test,true));

        if (!empty($productCollection)) {
            $data = $productCollection->toArray([
                'name',
                'sku',
                'entity_id',
                'type_id',
                'product_hide_price',
                'product_hide_html',
                'product_thumbnail',
                'product_url',
                'popup',
                'product_price',
                'product_price_amount',
                'product_price_exc_tax_html',
                'product_price_exc_tax',
                'inner',
                'master',
            ]);
            return $data;
        }
        return false;
    } catch (\Magento\Framework\Exception\NoSuchEntityException $exception) {
        return false;
    }
}

/**
 * @param string $queryText
 * @return string
 */
private function _getQueryPattern($queryText)
{
    $queryText = preg_replace('/\s+/', '%', $queryText);
    $queryLike = '%' . $queryText . '%';
    return $queryLike;
}

Now, the raw SQL generated by this filter... on $query_test.. is the following: https://pastebin.pl/view/raw/ae39c71f . Please check!.

My question is... is there a way to optimize this?. Or, is there a way to use Elasticsearch to improve this?. I mean... this is taking too much time to retrieve the data... I mean, it takes from 4 to 9 seconds to retrieve the filter. What I think it could be optimized is the e.entity_id IN part... but based on magento 2 practices that I think I'm following what should I do to improve it?.

Thanks in advance for any kind of help and thanks for reading.

4
  • where are you using this collection?
    – Diana
    Mar 10, 2021 at 7:49
  • Thanks Diana! I'm using it in a controller... and there under execute I have if ($queryText) { $response= $this->search->getSearchResult($queryText); }
    – Ricardo M
    Mar 10, 2021 at 13:26
  • roger that. i was trying to understand the motivation of using the layer resolver instead of loading a clean collection
    – Diana
    Mar 10, 2021 at 13:33
  • also not sure why you need $productCollection = $this->searchModel->getResData($productCollection);
    – Diana
    Mar 10, 2021 at 13:45

1 Answer 1

0

If you don't need the layer resolver for anything special, you could inject the \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory in your constructor and store it in a $productCollection class property, then you can replace the

$productCollection = $this->layerResolver->get()->getProductCollection();

with

$productCollection = $this->productCollection->create()

This will exclude all those IDs you carry with you because of the layer resolver.

Your Answer

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

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