/   /   /  Magento 2 Get One Search Result by Sku (Free Extension with explanation of code)

Note:

For more extensions and themes visit our store

Magento 2 Get One Search Result by Sku (Free Extension with explanation of code)

Author:  Ibnab

Download Fee Magento 2 Get One Search Result By Sku Extension


By default when you enter sku reference in quick bar search of Magento 2 , It displays all the product whose sku is having prefix of entered sku , By example if you enter 'BHSHIRT-F' for searching specific proudtc which have this reference as value , you will get  more than one for example ' BHSHIRT-FX1 and  BHSHIRT-FX2' .
Why ?
Now in Magento 2 we have in database catalogsearch_fulltext_scope1 and catalogsearch_fulltext_scope2 , is related to how many scope you have , let's imagine you have 2 store one for English and other for Arabic ,  the system create 2 scope . And stock the map of search inside this tables , but how ? Yeah let's go and see the structure of table ,this table have 3 columns entity_id and atrribute_id and data_index :
entity_id : id of entity or product that mapped with this infos
atrribute_id : id of attribute related to attribute set of product by default title and short description and description attribute used in map
 data_index: the work used for map taking from attribute like title or description
When you create an attribute (for Example Sku) with option of search-able option (use in search) , the system add automatically this attribute to catalogsearch_fulltext_scope for indexation
 
When you search from the small field text in the top with reference of sku the type of query is 'quick_search_container'  , the system take the query like 'BHSHIRT-F' nad create sql query based on it , but the fulltext query have custom strategie to create this query sql , te field data_index is used in matching proccess but Match responsible will add the filter based on data_index filed and text and add to query sql :
  1.  
  2. public function build(
  3.        ScoreBuilder $scoreBuilder,
  4.         Select $select,
  5.         RequestQueryInterface $query,        
  6.         $conditionType
  7.     ) {
  8.         /** @var $query \Magento\Framework\Search\Request\Query\Match */
  9.         $queryValue = $this->prepareQuery($query->getValue(), $conditionType);
  10.         $fieldList = [];
  11.         foreach ($query->getMatches() as $match) {
  12.             $fieldList[] = $match['field'];
  13.         }
  14.         $resolvedFieldList = $this->resolver->resolve($fieldList);
  15.         $fieldIds = [];
  16.         $columns = [];
  17.         foreach ($resolvedFieldList as $field) {
  18.             
  19.             if ($field->getType() === FieldInterface::TYPE_FULLTEXT && $field->getAttributeId()) {
  20.                 $fieldIds[] = $field->getAttributeId();
  21.             }
  22.             $column = $field->getColumn();
  23.             $columns[$column] = $column;
  24.         }
  25.         $matchQuery = $this->fulltextHelper->getMatchQuery(
  26.             $columns,
  27.             $queryValue,
  28.             $this->fulltextSearchMode
  29.         );
  30.         $scoreBuilder->addCondition($matchQuery, true);
  31.         if ($fieldIds) {
  32.             $matchQuery = sprintf('(%s AND search_index.attribute_id IN (%s))', $matchQuery, implode(',', $fieldIds));
  33.         }
  34.        $select->where($matchQuery);
  35.        return $select;
  36.    }
  37.  

 
The important line here for us is  $this->prepareQuery($query->getValue(), $conditionType); 
  1.  
  2.     /**
  3.      * @param string $queryValue
  4.      * @param string $conditionType
  5.      * @return string
  6.      */
  7.     protected function prepareQuery($queryValue, $conditionType)
  8.     {
  9.         $queryValue = str_replace($this->replaceSymbols, ' ', $queryValue);
  10.         
  11.         foreach ($this->preprocessors as $preprocessor) {
  12.             $queryValue = $preprocessor->process($queryValue);
  13.         }
  14.         $stringPrefix = '';
  15.         if ($conditionType === BoolExpression::QUERY_CONDITION_MUST) {
  16.             $stringPrefix = '';
  17.         } elseif ($conditionType === BoolExpression::QUERY_CONDITION_NOT) {
  18.             $stringPrefix = '-';
  19.         }
  20.         $queryValues = explode(' ', $queryValue);
  21.         foreach ($queryValues as $queryKey => $queryValue) {
  22.             if (empty($queryValue)) {
  23.                 unset($queryValues[$queryKey]);
  24.             } else {
  25.                 $stringSuffix = self::MINIMAL_CHARACTER_LENGTH > strlen($queryValue) ? '' : '*';
  26.                 $queryValues[$queryKey] = $stringPrefix . $queryValue . $stringSuffix;
  27.             }
  28.         }
  29.         $queryValue = implode(' ', $queryValues);      
  30.         return $queryValue;
  31.     } 
  32.  
But how you can only fetch whose sku is 'BHSHIRT-F' ?
Just create Plugin the customize and change the match strategie Magento\Framework\Search\Adapter\Mysql\Query\Builder\Match
and use around Plugin Function technique :
  1.  
  2. <p><?php
  3. namespace Ibnab\OneSku\Plugin;
  4. use Magento\Framework\DB\Helper\Mysql\Fulltext;
  5. use Magento\Framework\DB\Select;
  6. use Magento\Framework\Search\Adapter\Mysql\Field\FieldInterface;
  7. use Magento\Framework\Search\Adapter\Mysql\Field\ResolverInterface;
  8. use Magento\Framework\Search\Adapter\Mysql\ScoreBuilder;
  9. use Magento\Framework\Search\Request\Query\BoolExpression;
  10. use Magento\Framework\Search\Request\QueryInterface as RequestQueryInterface;
  11. use Magento\Framework\Search\Adapter\Preprocessor\PreprocessorInterface;
  12. use Magento\CatalogSearch\Model\ResourceModel\Fulltext as FulltextResource;
  13. use Magento\Catalog\Model\ResourceModel\Product\Attribute\Collection as AttributeCollection;
  14. use Magento\Store\Model\StoreManagerInterface;
  15. use Magento\Framework\App\ResourceConnection;
  16. use Ibnab\OneSku\Helper\Data as OneSkuData;
  17. class Match extends \Magento\Framework\Search\Adapter\Mysql\Query\Builder\Match {
  18.     const SPECIAL_CHARACTERS = '-+~/\\<>\'":*$#@()!,.?`=%&^';
  19.     const MINIMAL_CHARACTER_LENGTH = 3;
  20.     /**
  21.      * @var string[]
  22.      */
  23.     private $replaceSymbols = [];
  24.     /**
  25.      * @var ResolverInterface
  26.      */
  27.     private $resolver;
  28.     /**
  29.      * @var Fulltext
  30.      */
  31.     private $fulltextHelper;
  32.     /**
  33.      * @var string
  34.      */
  35.     private $fulltextSearchMode;
  36.     /**
  37.      * @var PreprocessorInterface[]
  38.      * @since 100.1.0
  39.      */
  40.     protected $preprocessors;
  41.     /**
  42.      * @var AttributeCollection
  43.      */
  44.     private $attributeCollection;
  45.     /**
  46.      * @var FulltextResource
  47.      */
  48.     private $fulltextResource;
  49.     private $storeManager;
  50.     private $_resource;</p>
  51.  
  52. <p>    /**
  53.      * @var \Ibnab\PaymentFee\Helper\Data
  54.      */
  55.     protected $dataHelper;    
  56.     /**
  57.      * @param ResolverInterface $resolver
  58.      * @param Fulltext $fulltextHelper
  59.      * @param string $fulltextSearchMode
  60.      * @param PreprocessorInterface[] $preprocessors
  61.      */
  62.     public function __construct(
  63.     OneSkuData $dataHelper,AttributeCollection $attributeCollection, FulltextResource $fulltextResource, StoreManagerInterface $storeManage, ResourceConnection $resource, ResolverInterface $resolver, Fulltext $fulltextHelper, $fulltextSearchMode = Fulltext::FULLTEXT_MODE_BOOLEAN, array $preprocessors = []
  64.     ) {        
  65.         $this->resolver = $resolver;
  66.         $this->replaceSymbols = str_split(self::SPECIAL_CHARACTERS, 1);
  67.         $this->fulltextHelper = $fulltextHelper;
  68.         $this->fulltextSearchMode = $fulltextSearchMode;
  69.         $this->preprocessors = $preprocessors;
  70.         $this->attributeCollection = $attributeCollection;
  71.         $this->fulltextResource = $fulltextResource;
  72.         $this->storeManager = $storeManage;
  73.         $this->_resource = $resource;
  74.         $this->dataHelper = $dataHelper;
  75.         parent::__construct($resolver,$fulltextHelper, $fulltextSearchMode, $preprocessors);
  76.     }
  77.     public function aroundBuild($matcher, $build, $scoreBuilder, $select, $query, $conditionType
  78.     ) {
  79.         /** @var $query \Magento\Framework\Search\Request\Query\Match */
  80.         $resultSku = 0;
  81.         $queryValue = $this->prepareQuery($query->getValue(), $conditionType);
  82.         $fieldList = [];
  83.         foreach ($query->getMatches() as $match) {
  84.             $fieldList[] = $match['field'];
  85.         }
  86.         $resolvedFieldList = $this->resolver->resolve($fieldList);
  87.         $fieldIds = [];
  88.         $columns = [];
  89.         foreach ($resolvedFieldList as $field) {
  90.             if ($field->getType() === FieldInterface::TYPE_FULLTEXT && $field->getAttributeId()) {
  91.                 $fieldIds[] = $field->getAttributeId();
  92.             }
  93.             $column = $field->getColumn();
  94.             $columns[$column] = $column;
  95.         }
  96.         if ($this->dataHelper->allowExtension()) {
  97.         $attribute = $this->attributeCollection->getItemByColumnValue('attribute_code', 'sku');
  98.         $attributeId = $attribute ? $attribute->getId() : 0;
  99.         if ($attributeId != 0) {
  100.             $connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);
  101.             $mainFullTextScope = $this->fulltextResource->getMainTable() . "_scope" . $this->storeManager->getStore()->getId();
  102.             $tblFullTextScope = $connection->getTableName($mainFullTextScope);
  103.             $resultSku = $connection->fetchOne('SELECT count(entity_id) FROM `' . $tblFullTextScope . '` WHERE attribute_id=' . $attributeId . ' And data_index=\'' . $query->getValue() . '\'');
  104.         }
  105.         }
  106.         if ($resultSku == 1) {
  107.             $matchQuery = 'data_index=\'' . $query->getValue() . '\'';
  108.         }else{
  109.             $matchQuery = $this->fulltextHelper->getMatchQuery(
  110.                     $columns, $queryValue, $this->fulltextSearchMode
  111.             );     
  112.         }
  113.             $matchQuery = $this->fulltextHelper->getMatchQuery(
  114.                     $columns, $queryValue, $this->fulltextSearchMode
  115.             ); 
  116.         $scoreBuilder->addCondition($matchQuery, true);
  117.         if ($fieldIds) {
  118.             $matchQuery = sprintf('(%s AND search_index.attribute_id IN (%s))', $matchQuery, implode(',', $fieldIds));
  119.         }
  120.         $select->where($matchQuery);
  121.         return $select;
  122.     }
  123. }
  124.  

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) 
  1.  
  2.         $attribute = $this->attributeCollection->getItemByColumnValue('attribute_code', 'sku');
  3.         $attributeId = $attribute ? $attribute->getId() : 0;
  4.         if ($attributeId != 0) {
  5.             $connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);
  6.             $mainFullTextScope = $this->fulltextResource->getMainTable() . "_scope" . $this->storeManager->getStore()->getId();
  7.             $tblFullTextScope = $connection->getTableName($mainFullTextScope);
  8.             $resultSku = $connection->fetchOne('SELECT count(entity_id) FROM `' . $tblFullTextScope . '` WHERE attribute_id=' . $attributeId . ' And data_index=\'' . $query->getValue() . '\'');
  9.         }
  10.  
if yes we change the clause where to be strict equal without Boolean match or like .
  1.  
  2.         if ($resultSku == 1) {
  3.             $matchQuery = 'data_index=\'' . $query->getValue() . '\'';
  4.         }else{
  5.             $matchQuery = $this->fulltextHelper->getMatchQuery(
  6.                     $columns, $queryValue, $this->fulltextSearchMode
  7.             );     
  8.         }
  9.  
Is done you can download the free ibnab extension get One result Search by Sku
Download Fee Magento 2 Get One Search Result By Sku Extension

Comments

Related Posts

Follow us

Categories

IBNAB is a company made of a group of professionals whose work is providing secure open source solutions. Our company strives for reaching magnificent results with each experience and provides professional open source solutions that cover every part of the business process.

Company

Quick links

Follow us