2

In search bar when i am searching a product using sku (100.40.10),

It displays all the product whose sku is having prefix (100.xx.xx) ,

But i only want it to fetch whose sku is (100.40.10).

4
  • use exact search not %like% Commented Jun 12, 2018 at 9:58
  • Could you please explain this in detail, like which file needs to override in Magento2 Commented Jun 12, 2018 at 9:59
  • I referred this blog : ibnab.com/en/blog/magento-2/… Bu do not get success Commented Jun 12, 2018 at 10:01
  • check the answer Commented Jun 12, 2018 at 10:19

3 Answers 3

1

By default, while searching for a product through catalog search, Magento uses all attributes for which the value of Use in Search is set to Yes.

If your Magento is not able to search a unique product when your search is being done by SKU, it means there is at least one attribute which is being used for search and the value of the search string is same for some products for that particular attribute.

You may check for the attributes for which the value of Use in Search is set to Yes. Set the value of Use in Search to No for all the attributes except SKU.

After that Reindex and check again. Once the search starts working as expected, you can set the value of Use in Search to Yes for the attributes which you want to include in search.

1
  • It doesn't make sense to me. Can you explain how this would work?
    – jrosell
    Commented Aug 19, 2020 at 9:39
0

Magento 1 has feature to set which kind of search you want, but in Magento 2 that feature removed so you need to customize that magento-search module Just create Plugin the customize and change the match strategie Magento\Framework\Search\Adapter\Mysql\Query\Builder\Match and use around Plugin Function technique :

<?php

namespace Test\OneSku\Plugin;

use Magento\Framework\DB\Helper\Mysql\Fulltext;
use Magento\Framework\DB\Select;
use Magento\Framework\Search\Adapter\Mysql\Field\FieldInterface;
use Magento\Framework\Search\Adapter\Mysql\Field\ResolverInterface;
use Magento\Framework\Search\Adapter\Mysql\ScoreBuilder;
use Magento\Framework\Search\Request\Query\BoolExpression;
use Magento\Framework\Search\Request\QueryInterface as RequestQueryInterface;
use Magento\Framework\Search\Adapter\Preprocessor\PreprocessorInterface;
use Magento\CatalogSearch\Model\ResourceModel\Fulltext as FulltextResource;
use Magento\Catalog\Model\ResourceModel\Product\Attribute\Collection as AttributeCollection;
use Magento\Store\Model\StoreManagerInterface;
use Magento\Framework\App\ResourceConnection;
use Test\OneSku\Helper\Data as OneSkuData;

class Match extends \Magento\Framework\Search\Adapter\Mysql\Query\Builder\Match
{
    const SPECIAL_CHARACTERS = '-+~/\\<>\'":*$#@()!,.?`=%&^';
    const MINIMAL_CHARACTER_LENGTH = 3;

    private $replaceSymbols = [];
    private $resolver;
    private $fulltextHelper;
    private $fulltextSearchMode;
    protected $preprocessors;
    private $attributeCollection;
    private $fulltextResource;
    private $storeManager;
    private $_resource;
    protected $dataHelper;

    public function __construct(
        OneSkuData $dataHelper,
        AttributeCollection $attributeCollection,
        FulltextResource $fulltextResource,
        StoreManagerInterface $storeManage,
        ResourceConnection $resource,
        ResolverInterface $resolver,
        Fulltext $fulltextHelper,
        $fulltextSearchMode = Fulltext::FULLTEXT_MODE_BOOLEAN,
        array $preprocessors = []
    ) {
        $this->resolver = $resolver;
        $this->replaceSymbols = str_split(self::SPECIAL_CHARACTERS, 1);
        $this->fulltextHelper = $fulltextHelper;
        $this->fulltextSearchMode = $fulltextSearchMode;
        $this->preprocessors = $preprocessors;
        $this->attributeCollection = $attributeCollection;
        $this->fulltextResource = $fulltextResource;
        $this->storeManager = $storeManage;
        $this->_resource = $resource;
        $this->dataHelper = $dataHelper;
        parent::__construct($resolver,$fulltextHelper, $fulltextSearchMode, $preprocessors);
    }

    public function aroundBuild($matcher, $build, $scoreBuilder, $select, $query, $conditionType)
    {
        $resultSku = 0;
        $queryValue = $this->prepareQuery($query->getValue(), $conditionType);
        $fieldList = [];
        foreach ($query->getMatches() as $match) {
            $fieldList[] = $match['field'];
        }
        $resolvedFieldList = $this->resolver->resolve($fieldList);
        $fieldIds = [];
        $columns = [];
        foreach ($resolvedFieldList as $field) {
            if ($field->getType() === FieldInterface::TYPE_FULLTEXT && $field->getAttributeId()) {
                $fieldIds[] = $field->getAttributeId();
            }
            $column = $field->getColumn();
            $columns[$column] = $column;
        }
        if ($this->dataHelper->allowExtension()) {
            $attribute = $this->attributeCollection->getItemByColumnValue('attribute_code', 'sku');
            $attributeId = $attribute ? $attribute->getId() : 0;
            if ($attributeId != 0) {
                $connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);
                $mainFullTextScope = $this->fulltextResource->getMainTable() . "_scope" . $this->storeManager->getStore()->getId();
                $tblFullTextScope = $connection->getTableName($mainFullTextScope);
                $resultSku = $connection->fetchOne('SELECT count(entity_id) FROM `' . $tblFullTextScope . '` WHERE attribute_id=' . $attributeId . ' And data_index=\'' . $query->getValue() . '\'');
            }
        }
        if ($resultSku == 1) {
            $matchQuery = 'data_index=\'' . $query->getValue() . '\'';
        } else {
            $matchQuery = $this->fulltextHelper->getMatchQuery(
                $columns, $queryValue, $this->fulltextSearchMode
            );
        }
        $matchQuery = $this->fulltextHelper->getMatchQuery(
                $columns, $queryValue, $this->fulltextSearchMode
        );
        $scoreBuilder->addCondition($matchQuery, true);
        if ($fieldIds) {
            $matchQuery = sprintf('(%s AND search_index.attribute_id IN (%s))', $matchQuery, implode(',', $fieldIds));
        }
        $select->where($matchQuery);
        return $select;
    }
}

In this class we have added some line to test of SKU with some reference is exist by example in table catalogsearch_fulltext_scope1 (related to store id 1)

$attribute = $this->attributeCollection->getItemByColumnValue('attribute_code', 'sku');
$attributeId = $attribute ? $attribute->getId() : 0;
if ($attributeId != 0) {
    $connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);
    $mainFullTextScope = $this->fulltextResource->getMainTable() . "_scope" . $this->storeManager->getStore()->getId();
    $tblFullTextScope = $connection->getTableName($mainFullTextScope);
    $resultSku = $connection->fetchOne('SELECT count(entity_id) FROM `' . $tblFullTextScope . '` WHERE attribute_id=' . $attributeId . ' And data_index=\'' . $query->getValue() . '\'');
}

If yes we change the clause were to be strict equal without the Boolean match or like.

if ($resultSku == 1) {
    $matchQuery = 'data_index=\'' . $query->getValue() . '\'';
} else {
    $matchQuery = $this->fulltextHelper->getMatchQuery(
        $columns, $queryValue, $this->fulltextSearchMode
    );
}
9
0

For the latest Magento Version (2.4.x) this can be fixed in file -

Magento\Elasticsearch\SearchAdapter\Query\Builder\MatchQuery.php

Line no 88 add below code-

if ($matchKey != 'match_phrase_prefix') {
    $matchQuery['operator'] = "AND";
}

enter image description here

Note: Try to overwrite this file in your module.

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.