Recommendations.php

Go to the documentation of this file.

<?php
namespace Magento\AdvancedSearch\Model\ResourceModel;

class Recommendations extends \Magento\Framework\Model\ResourceModel\Db\AbstractDb
{

    protected $_searchQueryModel;

    public function __construct(
        \Magento\Framework\Model\ResourceModel\Db\Context $context,
        \Magento\Search\Model\QueryFactory $queryFactory,
        $connectionName = null
    ) {
        parent::__construct($context, $connectionName);
        $this->_searchQueryModel = $queryFactory->create();
    }

    protected function _construct()
    {
        $this->_init('catalogsearch_recommendations', 'id');
    }

    public function saveRelatedQueries($queryId, $relatedQueries = [])
    {
        $connection = $this->getConnection();
        $whereOr = [];
        if (count($relatedQueries) > 0) {
            $whereOr[] = implode(
                ' AND ',
                [
                    $connection->quoteInto('query_id=?', $queryId),
                    $connection->quoteInto('relation_id NOT IN(?)', $relatedQueries)
                ]
            );
            $whereOr[] = implode(
                ' AND ',
                [
                    $connection->quoteInto('relation_id = ?', $queryId),
                    $connection->quoteInto('query_id NOT IN(?)', $relatedQueries)
                ]
            );
        } else {
            $whereOr[] = $connection->quoteInto('query_id = ?', $queryId);
            $whereOr[] = $connection->quoteInto('relation_id = ?', $queryId);
        }
        $whereCond = '(' . implode(') OR (', $whereOr) . ')';
        $connection->delete($this->getMainTable(), $whereCond);

        $existsRelatedQueries = $this->getRelatedQueries($queryId);
        $neededRelatedQueries = array_diff($relatedQueries, $existsRelatedQueries);
        foreach ($neededRelatedQueries as $relationId) {
            $connection->insert($this->getMainTable(), ["query_id" => $queryId, "relation_id" => $relationId]);
        }
        return $this;
    }

    public function getRelatedQueries($queryId, $limit = false, $order = false)
    {
        $collection = $this->_searchQueryModel->getResourceCollection();
        $connection = $this->getConnection();

        $queryIdCond = $connection->quoteInto('main_table.query_id IN (?)', $queryId);

        $collection->getSelect()->join(
            ['sr' => $collection->getTable('catalogsearch_recommendations')],
            '(sr.query_id=main_table.query_id OR sr.relation_id=main_table.query_id) AND ' . $queryIdCond
        )->reset(
            \Magento\Framework\DB\Select::COLUMNS
        )->columns(
            [
                'rel_id' => $connection->getCheckSql(
                    'main_table.query_id=sr.query_id',
                    'sr.relation_id',
                    'sr.query_id'
                ),
            ]
        );
        if (!empty($limit)) {
            $collection->getSelect()->limit($limit);
        }
        if (!empty($order)) {
            $collection->getSelect()->order($order);
        }

        $queryIds = $connection->fetchCol($collection->getSelect());
        return $queryIds;
    }

    public function getRecommendationsByQuery($query, $params, $searchRecommendationsCount)
    {
        $this->_searchQueryModel->loadByQueryText($query);

        if (isset($params['store_id'])) {
            $this->_searchQueryModel->setStoreId($params['store_id']);
        }
        $relatedQueriesIds = $this->loadByQuery($query, $searchRecommendationsCount);
        $relatedQueries = [];
        if (count($relatedQueriesIds)) {
            $connection = $this->getConnection();
            $mainTable = $this->_searchQueryModel->getResourceCollection()->getMainTable();
            $select = $connection->select()->from(
                ['main_table' => $mainTable],
                ['query_text', 'num_results']
            )->where(
                'query_id IN(?)',
                $relatedQueriesIds
            )->where(
                'num_results > 0'
            );
            $relatedQueries = $connection->fetchAll($select);
        }

        return $relatedQueries;
    }

    protected function loadByQuery($query, $searchRecommendationsCount)
    {
        $connection = $this->getConnection();
        $queryId = $this->_searchQueryModel->getId();
        $relatedQueries = $this->getRelatedQueries($queryId, $searchRecommendationsCount, 'num_results DESC');
        if ($searchRecommendationsCount - count($relatedQueries) < 1) {
            return $relatedQueries;
        }

        $queryWords = [$query];
        if (strpos($query, ' ') !== false) {
            $queryWords = array_unique(array_merge($queryWords, explode(' ', $query)));
            foreach ($queryWords as $key => $word) {
                $queryWords[$key] = trim($word);
                if (strlen($word) < 3) {
                    unset($queryWords[$key]);
                }
            }
        }

        $likeCondition = [];
        foreach ($queryWords as $word) {
            $likeCondition[] = $connection->quoteInto('query_text LIKE ?', $word . '%');
        }
        $likeCondition = implode(' OR ', $likeCondition);

        $select = $connection->select()->from(
            $this->_searchQueryModel->getResource()->getMainTable(),
            ['query_id']
        )->where(
            new \Zend_Db_Expr($likeCondition)
        )->where(
            'store_id=?',
            $this->_searchQueryModel->getStoreId()
        )->order(
            'num_results DESC'
        )->limit(
            $searchRecommendationsCount + 1
        );
        $ids = $connection->fetchCol($select);

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

        $key = array_search($queryId, $ids);
        if ($key !== false) {
            unset($ids[$key]);
        }
        $ids = array_unique(array_merge($relatedQueries, $ids));
        $ids = array_slice($ids, 0, $searchRecommendationsCount);
        return $ids;
    }
}