/**
* Add attribute to filter
*
* @param int $storeId
* @param string $attributeCode
* @param mixed $value
* @param string $type
* @return \Magento\Framework\DB\Select|bool
*/
protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
{
if (!$this->_select instanceof \Magento\Framework\DB\Select) {
return false;
}
if (!isset($this->_attributesCache[$attributeCode])) {
$attribute = $this->_categoryResource->getAttribute($attributeCode);
$this->_attributesCache[$attributeCode] = ['entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal(), 'backend_type' => $attribute->getBackendType()];
}
$attribute = $this->_attributesCache[$attributeCode];
switch ($type) {
case '=':
$conditionRule = '=?';
break;
case 'in':
$conditionRule = ' IN(?)';
break;
default:
return false;
break;
}
if ($attribute['backend_type'] == 'static') {
$this->_select->where('e.' . $attributeCode . $conditionRule, $value);
} else {
$this->_select->join(['t1_' . $attributeCode => $attribute['table']], 'e.entity_id = t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id = 0', [])->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
if ($attribute['is_global']) {
$this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
} else {
$ifCase = $this->getConnection()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
$this->_select->joinLeft(['t2_' . $attributeCode => $attribute['table']], $this->getConnection()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), [])->where('(' . $ifCase . ')' . $conditionRule, $value);
}
}
return $this->_select;
}
/**
* {@inheritdoc}
*/
public function build(ScoreBuilder $scoreBuilder, Select $select, RequestQueryInterface $query, $conditionType)
{
/** @var $query \Magento\Framework\Search\Request\Query\Match */
$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;
}
$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;
}
/**
* Add attribute to filter
*
* @param int $storeId
* @param string $attributeCode
* @param mixed $value
* @param string $type
* @return \Magento\Framework\DB\Select|bool
*/
protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
{
if (!$this->_select instanceof \Magento\Framework\DB\Select) {
return false;
}
switch ($type) {
case '=':
$conditionRule = '=?';
break;
case 'in':
$conditionRule = ' IN(?)';
break;
default:
return false;
break;
}
$attribute = $this->_getAttribute($attributeCode);
if ($attribute['backend_type'] == 'static') {
$this->_select->where('e.' . $attributeCode . $conditionRule, $value);
} else {
$this->_joinAttribute($storeId, $attributeCode);
if ($attribute['is_global']) {
$this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
} else {
$ifCase = $this->getConnection()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
$this->_select->where('(' . $ifCase . ')' . $conditionRule, $value);
}
}
return $this->_select;
}
/**
* Method for FULLTEXT search in Mysql, will added generated
* MATCH ($columns) AGAINST ('$expression' $mode) to where clause
*
* @param \Magento\Framework\DB\Select $select
* @param string|string[] $columns Columns which add to MATCH ()
* @param string $expression Expression which add to AGAINST ()
* @param bool $isCondition true=AND, false=OR
* @param string $mode
* @return \Magento\Framework\DB\Select
*/
public function match($select, $columns, $expression, $isCondition = true, $mode = self::FULLTEXT_MODE_NATURAL)
{
$fullCondition = $this->getMatchQuery($columns, $expression, $mode);
if ($isCondition) {
$select->where($fullCondition);
} else {
$select->orWhere($fullCondition);
}
return $select;
}
/**
* @inheritdoc
*/
public function addFieldToFilter($field, $condition = null)
{
if (is_array($field)) {
$conditions = [];
foreach ($field as $key => $value) {
$conditions[] = $this->translateCondition($value, isset($condition[$key]) ? $condition[$key] : null);
}
$resultCondition = '(' . implode(') ' . \Zend_Db_Select::SQL_OR . ' (', $conditions) . ')';
} else {
$resultCondition = $this->translateCondition($field, $condition);
}
$this->select->where($resultCondition, null, Select::TYPE_CONDITION);
}
/**
* Process WHERE clause
*
* @param Select $select
* @return void
*/
protected function processWhereCondition(Select $select)
{
foreach ($this->referenceColumns as $column => $referenceColumn) {
$identifier = '';
if (isset($referenceColumn['tableAlias'])) {
$identifier = $referenceColumn['tableAlias'] . '.';
}
$columnName = $column;
if (isset($referenceColumn['columnName'])) {
$columnName = $referenceColumn['columnName'];
}
$select->where(sprintf('%s = %s', $this->adapter->quoteIdentifier('lookup.' . $column), $this->adapter->quoteIdentifier($identifier . $columnName)));
}
}
/**
* @param \Magento\Framework\DB\Select $select
* @return \Magento\Framework\DB\Select
*/
public function prepareSelect(\Magento\Framework\DB\Select $select)
{
$select->where('website_id = :website_id')->order(['dest_country_id DESC', 'dest_region_id DESC', 'dest_zip DESC'])->limit(1);
// Render destination condition
$orWhere = '(' . implode(') OR (', ["dest_country_id = :country_id AND dest_region_id = :region_id AND dest_zip = :postcode", "dest_country_id = :country_id AND dest_region_id = :region_id AND dest_zip = ''", "dest_country_id = :country_id AND dest_region_id = :region_id AND dest_zip = '*'", "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = '*'", "dest_country_id = '0' AND dest_region_id = :region_id AND dest_zip = '*'", "dest_country_id = '0' AND dest_region_id = 0 AND dest_zip = '*'", "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = ''", "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = :postcode", "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = '*'"]) . ')';
$select->where($orWhere);
// Render condition by condition name
if (is_array($this->request->getConditionName())) {
$orWhere = [];
foreach (range(0, count($this->request->getConditionName())) as $conditionNumber) {
$bindNameKey = sprintf(':condition_name_%d', $conditionNumber);
$bindValueKey = sprintf(':condition_value_%d', $conditionNumber);
$orWhere[] = "(condition_name = {$bindNameKey} AND condition_value <= {$bindValueKey})";
}
if ($orWhere) {
$select->where(implode(' OR ', $orWhere));
}
} else {
$select->where('condition_name = :condition_name');
$select->where('condition_value <= :condition_value');
}
return $select;
}
/**
* Add attribute join condition to select and return \Zend_Db_Expr
* attribute value definition
* If $condition is not empty apply limitation for select
*
* @param \Magento\Framework\DB\Select $select
* @param string $attrCode the attribute code
* @param string|\Zend_Db_Expr $entity the entity field or expression for condition
* @param string|\Zend_Db_Expr $store the store field or expression for condition
* @param \Zend_Db_Expr $condition the limitation condition
* @param bool $required if required or has condition used INNER join, else - LEFT
* @return \Zend_Db_Expr the attribute value expression
*/
protected function _addAttributeToSelect($select, $attrCode, $entity, $store, $condition = null, $required = false)
{
$attribute = $this->_getAttribute($attrCode);
$attributeId = $attribute->getAttributeId();
$attributeTable = $attribute->getBackend()->getTable();
$connection = $this->getConnection();
$joinType = $condition !== null || $required ? 'join' : 'joinLeft';
if ($attribute->isScopeGlobal()) {
$alias = 'ta_' . $attrCode;
$select->{$joinType}([$alias => $attributeTable], "{$alias}.entity_id = {$entity} AND {$alias}.attribute_id = {$attributeId}" . " AND {$alias}.store_id = 0", []);
$expression = new \Zend_Db_Expr("{$alias}.value");
} else {
$dAlias = 'tad_' . $attrCode;
$sAlias = 'tas_' . $attrCode;
$select->{$joinType}([$dAlias => $attributeTable], "{$dAlias}.entity_id = {$entity} AND {$dAlias}.attribute_id = {$attributeId}" . " AND {$dAlias}.store_id = 0", []);
$select->joinLeft([$sAlias => $attributeTable], "{$sAlias}.entity_id = {$entity} AND {$sAlias}.attribute_id = {$attributeId}" . " AND {$sAlias}.store_id = {$store}", []);
$expression = $connection->getCheckSql($connection->getIfNullSql("{$sAlias}.value_id", -1) . ' > 0', "{$sAlias}.value", "{$dAlias}.value");
}
if ($condition !== null) {
$select->where("{$expression}{$condition}");
}
return $expression;
}
/**
* Add filtering by dimensions
*
* @param RequestInterface $request
* @param Select $select
* @return \Magento\Framework\DB\Select
*/
private function processDimensions(RequestInterface $request, Select $select)
{
$dimensions = $this->prepareDimensions($request->getDimensions());
$query = $this->conditionManager->combineQueries($dimensions, Select::SQL_OR);
if (!empty($query)) {
$select->where($this->conditionManager->wrapBrackets($query));
}
return $select;
}
protected function applyCondition(Select $select)
{
$select->where('attribute.entity_id IN(?)', $this->entityIds);
}
/**
* Apply stores filter to select object
*
* @param \Magento\Framework\DB\Select $select
* @return $this
*/
protected function _applyStoresFilterToSelect(\Magento\Framework\DB\Select $select)
{
$nullCheck = false;
$storeIds = $this->_storesIds;
if (!is_array($storeIds)) {
$storeIds = [$storeIds];
}
$storeIds = array_unique($storeIds);
if ($index = array_search(null, $storeIds)) {
unset($storeIds[$index]);
$nullCheck = true;
}
if ($nullCheck) {
$select->where('store_id IN(?) OR store_id IS NULL', $storeIds);
} else {
$select->where('store_id IN(?)', $storeIds);
}
return $this;
}
/**
* Add filtering by dimensions
*
* @param RequestInterface $request
* @param Select $select
* @return \Magento\Framework\DB\Select
*/
private function processDimensions(RequestInterface $request, Select $select)
{
$dimensions = [];
foreach ($request->getDimensions() as $dimension) {
$dimensions[] = $this->dimensionsBuilder->build($dimension);
}
$query = $this->conditionManager->combineQueries($dimensions, Select::SQL_OR);
if (!empty($query)) {
$select->where($this->conditionManager->wrapBrackets($query));
}
return $select;
}
/**
* @param \Magento\Framework\DB\Select $select
* @param int|string $now
* @return void
*/
public function applyDate($select, $now)
{
$select->where('from_date is null or from_date <= ?', $now)->where('to_date is null or to_date >= ?', $now);
}
/**
* {@inheritdoc}
*/
public function build(ScoreBuilder $scoreBuilder, Select $select, RequestQueryInterface $query, $conditionType)
{
/** @var $query \Magento\Framework\Search\Request\Query\Match */
$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;
}
$exactMatchQuery = $this->getExactMatchQuery($columns, $query->getValue());
$scoreQuery = $this->getScoreQuery($columns, $query->getValue());
$scoreBuilder->addCondition(new \Zend_Db_Expr($scoreQuery), true);
if ($query->getValue() != $this->queryHelper->singularize($query->getValue())) {
$scoreQuery = $this->getScoreQuery($columns, $this->queryHelper->singularize($query->getValue()));
$scoreBuilder->addCondition(new \Zend_Db_Expr($scoreQuery), true);
}
if ($fieldIds) {
$select->where(sprintf('search_index.attribute_id IN (%s)', implode(',', $fieldIds)));
}
$select->having(new \Zend_Db_Expr($exactMatchQuery))->group('entity_id');
return $select;
}
public function limitFlatActive(Select $select, $alias = 'main')
{
$select->join(['flat' => $this->getTable('entity_flat')], $alias . '.entity_id = flat.entity_id', []);
$select->where('flat.scope_id = ?', $this->scope->getId($this->scopeCode));
$select->where('flat.is_active = ?', 1);
return $this;
}
/**
* @param Select $select
* @return Select
*/
private function setCustomerGroupId($select)
{
return $select->where('customer_group_id = ?', $this->customerSession->getCustomerGroupId());
}
/**
* Add stock status limitation to catalog product price index select object
*
* @param \Magento\Framework\DB\Select $select
* @param string|\Zend_Db_Expr $entityField
* @param string|\Zend_Db_Expr $websiteField
* @return $this
*/
public function prepareCatalogProductIndexSelect(\Magento\Framework\DB\Select $select, $entityField, $websiteField)
{
$select->join(array('ciss' => $this->getMainTable()), "ciss.product_id = {$entityField} AND ciss.website_id = {$websiteField}", array());
$select->where('ciss.stock_status = ?', Stock\Status::STATUS_IN_STOCK);
return $this;
}
protected function applyCondition(Select $select)
{
$select->where($this->entityCondition);
}
/**
* Add filtering by dimensions
*
* @param RequestInterface $request
* @param Select $select
* @return \Magento\Framework\DB\Select
*/
private function processDimensions(RequestInterface $request, Select $select)
{
$dimensions = [];
foreach ($request->getDimensions() as $dimension) {
$dimensions[] = $this->dimensionsBuilder->build($dimension);
}
if (!empty($dimensions)) {
$query = sprintf('(%s)', implode(' ' . Select::SQL_OR . ' ', $dimensions));
$select->where($query);
}
return $select;
}
