/**
* @param FilterInterface $filter
* @param bool $isNegation
* @param string $query
* @return string
*/
private function processQueryWithField(FilterInterface $filter, $isNegation, $query)
{
/** @var Attribute $attribute */
$attribute = $this->config->getAttribute(Product::ENTITY, $filter->getField());
if ($filter->getField() === 'price') {
$resultQuery = str_replace($this->connection->quoteIdentifier('price'), $this->connection->quoteIdentifier('price_index.min_price'), $query);
} elseif ($filter->getField() === 'category_ids') {
return 'category_ids_index.category_id = ' . $filter->getValue();
} elseif ($attribute->isStatic()) {
$alias = $this->tableMapper->getMappingAlias($filter);
$resultQuery = str_replace($this->connection->quoteIdentifier($attribute->getAttributeCode()), $this->connection->quoteIdentifier($alias . '.' . $attribute->getAttributeCode()), $query);
} elseif ($filter->getType() === FilterInterface::TYPE_TERM && in_array($attribute->getFrontendInput(), ['select', 'multiselect'], true)) {
$resultQuery = $this->processTermSelect($filter, $isNegation);
} elseif ($filter->getType() === FilterInterface::TYPE_RANGE && in_array($attribute->getBackendType(), ['decimal', 'int'], true)) {
$resultQuery = $this->processRangeNumeric($filter, $query, $attribute);
} else {
$table = $attribute->getBackendTable();
$select = $this->connection->select();
$ifNullCondition = $this->connection->getIfNullSql('current_store.value', 'main_table.value');
$currentStoreId = $this->scopeResolver->getScope()->getId();
$select->from(['main_table' => $table], 'entity_id')->joinLeft(['current_store' => $table], 'current_store.attribute_id = main_table.attribute_id AND current_store.store_id = ' . $currentStoreId, null)->columns([$filter->getField() => $ifNullCondition])->where('main_table.attribute_id = ?', $attribute->getAttributeId())->where('main_table.store_id = ?', Store::DEFAULT_STORE_ID)->having($query);
$resultQuery = 'search_index.entity_id IN (
select entity_id from ' . $this->conditionManager->wrapBrackets($select) . ' as filter
)';
}
return $resultQuery;
}
/**
* {@inheritdoc}
*/
public function getAggregation(BucketInterface $bucket, array $dimensions, $range, \Magento\Framework\Search\Dynamic\EntityStorage $entityStorage)
{
$select = $this->dataProvider->getDataSet($bucket, $dimensions, $entityStorage->getSource());
$column = $select->getPart(Select::COLUMNS)[0];
$select->reset(Select::COLUMNS);
$rangeExpr = new \Zend_Db_Expr($this->connection->getIfNullSql($this->connection->quoteInto('FLOOR(' . $column[1] . ' / ? ) + 1', $range), 1));
$select->columns(['range' => $rangeExpr])->columns(['metrix' => 'COUNT(*)'])->group('range')->order('range');
$result = $this->connection->fetchPairs($select);
return $result;
}
/**
* Get select for all products
*
* @param \Magento\Store\Model\Store $store
* @return \Magento\Framework\DB\Select
*/
protected function getAllProducts(\Magento\Store\Model\Store $store)
{
if (!isset($this->productsSelects[$store->getId()])) {
$statusAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'status')->getId();
$visibilityAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'visibility')->getId();
$select = $this->connection->select()->from(['cp' => $this->getTable('catalog_product_entity')], [])->joinInner(['cpw' => $this->getTable('catalog_product_website')], 'cpw.product_id = cp.entity_id', [])->joinInner(['cpsd' => $this->getTable('catalog_product_entity_int')], 'cpsd.entity_id = cp.entity_id AND cpsd.store_id = 0' . ' AND cpsd.attribute_id = ' . $statusAttributeId, [])->joinLeft(['cpss' => $this->getTable('catalog_product_entity_int')], 'cpss.entity_id = cp.entity_id AND cpss.attribute_id = cpsd.attribute_id' . ' AND cpss.store_id = ' . $store->getId(), [])->joinInner(['cpvd' => $this->getTable('catalog_product_entity_int')], 'cpvd.entity_id = cp.entity_id AND cpvd.store_id = 0' . ' AND cpvd.attribute_id = ' . $visibilityAttributeId, [])->joinLeft(['cpvs' => $this->getTable('catalog_product_entity_int')], 'cpvs.entity_id = cp.entity_id AND cpvs.attribute_id = cpvd.attribute_id ' . ' AND cpvs.store_id = ' . $store->getId(), [])->joinLeft(['ccp' => $this->getTable('catalog_category_product')], 'ccp.product_id = cp.entity_id', [])->where('cpw.website_id = ?', $store->getWebsiteId())->where($this->connection->getIfNullSql('cpss.value', 'cpsd.value') . ' = ?', \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED)->where($this->connection->getIfNullSql('cpvs.value', 'cpvd.value') . ' IN (?)', [\Magento\Catalog\Model\Product\Visibility::VISIBILITY_IN_CATALOG, \Magento\Catalog\Model\Product\Visibility::VISIBILITY_IN_SEARCH, \Magento\Catalog\Model\Product\Visibility::VISIBILITY_BOTH])->group('cp.entity_id')->columns(['category_id' => new \Zend_Db_Expr($store->getRootCategoryId()), 'product_id' => 'cp.entity_id', 'position' => new \Zend_Db_Expr($this->connection->getCheckSql('ccp.product_id IS NOT NULL', 'ccp.position', '0')), 'is_parent' => new \Zend_Db_Expr($this->connection->getCheckSql('ccp.product_id IS NOT NULL', '1', '0')), 'store_id' => new \Zend_Db_Expr($store->getId()), 'visibility' => new \Zend_Db_Expr($this->connection->getIfNullSql('cpvs.value', 'cpvd.value'))]);
$this->productsSelects[$store->getId()] = $select;
}
return $this->productsSelects[$store->getId()];
}
/**
* @param AbstractCondition $condition
* @param string $value
* @return string
* @throws \Magento\Framework\Exception
*/
protected function _getMappedSqlCondition(AbstractCondition $condition, $value = '')
{
$argument = $condition->getMappedSqlField();
if ($argument) {
$conditionOperator = $condition->getOperatorForValidate();
if (!isset($this->_conditionOperatorMap[$conditionOperator])) {
throw new \Magento\Framework\Exception('Unknown condition operator');
}
$sql = str_replace(':field', $this->_connection->getIfNullSql($this->_connection->quoteIdentifier($argument), 0), $this->_conditionOperatorMap[$conditionOperator]);
return $this->_expressionFactory->create(['expression' => $value . $this->_connection->quoteInto($sql, $condition->getBindArgumentValue())]);
}
return '';
}
/**
* Fill temporary flat table by data from temporary flat table parts
*
* @param array $tables
* @param int|string $storeId
* @param string $valueFieldSuffix
* @return void
*/
protected function _fillTemporaryFlatTable(array $tables, $storeId, $valueFieldSuffix)
{
$linkField = $this->metadataPool->getMetadata(ProductInterface::class)->getLinkField();
$select = $this->_connection->select();
$temporaryFlatTableName = $this->_getTemporaryTableName($this->_productIndexerHelper->getFlatTableName($storeId));
$flatColumns = $this->_productIndexerHelper->getFlatColumns();
$entityTableName = $this->_productIndexerHelper->getTable('catalog_product_entity');
$entityTemporaryTableName = $this->_getTemporaryTableName($entityTableName);
$columnsList = array_keys($tables[$entityTableName]);
$websiteId = (int) $this->_storeManager->getStore($storeId)->getWebsiteId();
unset($tables[$entityTableName]);
$allColumns = array_merge(['entity_id', 'type_id', 'attribute_set_id'], $columnsList);
/* @var $status \Magento\Eav\Model\Entity\Attribute */
$status = $this->_productIndexerHelper->getAttribute('status');
$statusTable = $this->_getTemporaryTableName($status->getBackendTable());
$statusConditions = [sprintf('e.%s = dstatus.%s', $linkField, $linkField), 'dstatus.store_id = ' . (int) $storeId, 'dstatus.attribute_id = ' . (int) $status->getId()];
$statusExpression = $this->_connection->getIfNullSql('dstatus.value', $this->_connection->quoteIdentifier("{$statusTable}.status"));
$select->from(['e' => $entityTemporaryTableName], $allColumns)->joinInner(['wp' => $this->_productIndexerHelper->getTable('catalog_product_website')], 'wp.product_id = e.entity_id AND wp.website_id = ' . $websiteId, [])->joinLeft(['dstatus' => $status->getBackend()->getTable()], implode(' AND ', $statusConditions), [])->where($statusExpression . ' = ' . \Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED);
foreach ($tables as $tableName => $columns) {
$columnValueNames = [];
$temporaryTableName = $this->_getTemporaryTableName($tableName);
$temporaryValueTableName = $temporaryTableName . $valueFieldSuffix;
$columnsNames = array_keys($columns);
$select->joinLeft($temporaryTableName, "e.{$linkField} = " . $temporaryTableName . ".{$linkField}", $columnsNames);
$allColumns = array_merge($allColumns, $columnsNames);
foreach ($columnsNames as $name) {
$columnValueName = $name . $valueFieldSuffix;
if (isset($flatColumns[$columnValueName])) {
$columnValueNames[] = $columnValueName;
}
}
if (!empty($columnValueNames)) {
$select->joinLeft($temporaryValueTableName, "e.{$linkField} = " . $temporaryValueTableName . ".{$linkField}", $columnValueNames);
$allColumns = array_merge($allColumns, $columnValueNames);
}
}
$sql = $select->insertFromSelect($temporaryFlatTableName, $allColumns, false);
$this->_connection->query($sql);
}
/**
* @param int $websiteId
* @param Product|null $product
* @return \Zend_Db_Statement_Interface
* @throws \Magento\Framework\Exception\LocalizedException
*/
protected function getRuleProductsStmt($websiteId, Product $product = null)
{
/**
* Sort order is important
* It used for check stop price rule condition.
* website_id customer_group_id product_id sort_order
* 1 1 1 0
* 1 1 1 1
* 1 1 1 2
* if row with sort order 1 will have stop flag we should exclude
* all next rows for same product id from price calculation
*/
$select = $this->connection->select()->from(['rp' => $this->getTable('catalogrule_product')])->order(['rp.website_id', 'rp.customer_group_id', 'rp.product_id', 'rp.sort_order', 'rp.rule_id']);
if ($product && $product->getEntityId()) {
$select->where('rp.product_id=?', $product->getEntityId());
}
/**
* Join default price and websites prices to result
*/
$priceAttr = $this->eavConfig->getAttribute(Product::ENTITY, 'price');
$priceTable = $priceAttr->getBackend()->getTable();
$attributeId = $priceAttr->getId();
$linkField = $this->metadataPool->getMetadata(ProductInterface::class)->getLinkField();
$select->join(['e' => $this->getTable('catalog_product_entity')], sprintf('e.entity_id = rp.product_id'), []);
$joinCondition = '%1$s.' . $linkField . '=e.' . $linkField . ' AND (%1$s.attribute_id=' . $attributeId . ') and %1$s.store_id=%2$s';
$select->join(['pp_default' => $priceTable], sprintf($joinCondition, 'pp_default', \Magento\Store\Model\Store::DEFAULT_STORE_ID), []);
$website = $this->storeManager->getWebsite($websiteId);
$defaultGroup = $website->getDefaultGroup();
if ($defaultGroup instanceof \Magento\Store\Model\Group) {
$storeId = $defaultGroup->getDefaultStoreId();
} else {
$storeId = \Magento\Store\Model\Store::DEFAULT_STORE_ID;
}
$select->joinInner(['product_website' => $this->getTable('catalog_product_website')], 'product_website.product_id=rp.product_id ' . 'AND product_website.website_id = rp.website_id ' . 'AND product_website.website_id=' . $websiteId, []);
$tableAlias = 'pp' . $websiteId;
$select->joinLeft([$tableAlias => $priceTable], sprintf($joinCondition, $tableAlias, $storeId), []);
$select->columns(['default_price' => $this->connection->getIfNullSql($tableAlias . '.value', 'pp_default.value')]);
return $this->connection->query($select);
}
/**
* Write single product into flat product table
*
* @param int $storeId
* @param int $productId
* @param string $valueFieldSuffix
* @return \Magento\Catalog\Model\Indexer\Product\Flat
*/
public function write($storeId, $productId, $valueFieldSuffix = '')
{
$flatTable = $this->_productIndexerHelper->getFlatTableName($storeId);
$attributes = $this->_productIndexerHelper->getAttributes();
$eavAttributes = $this->_productIndexerHelper->getTablesStructure($attributes);
$updateData = array();
$describe = $this->_connection->describeTable($flatTable);
foreach ($eavAttributes as $tableName => $tableColumns) {
$columnsChunks = array_chunk($tableColumns, self::ATTRIBUTES_CHUNK_SIZE, true);
foreach ($columnsChunks as $columns) {
$select = $this->_connection->select();
$selectValue = $this->_connection->select();
$keyColumns = array('entity_id' => 'e.entity_id', 'attribute_id' => 't.attribute_id', 'value' => $this->_connection->getIfNullSql('`t2`.`value`', '`t`.`value`'));
if ($tableName != $this->_productIndexerHelper->getTable('catalog_product_entity')) {
$valueColumns = array();
$ids = array();
$select->from(array('e' => $this->_productIndexerHelper->getTable('catalog_product_entity')), $keyColumns);
$selectValue->from(array('e' => $this->_productIndexerHelper->getTable('catalog_product_entity')), $keyColumns);
/** @var $attribute \Magento\Catalog\Model\Resource\Eav\Attribute */
foreach ($columns as $columnName => $attribute) {
if (isset($describe[$columnName])) {
$ids[$attribute->getId()] = $columnName;
}
}
$select->joinLeft(array('t' => $tableName), 'e.entity_id = t.entity_id ' . $this->_connection->quoteInto(' AND t.attribute_id IN (?)', array_keys($ids)) . ' AND t.store_id = 0', array())->joinLeft(array('t2' => $tableName), 't.entity_id = t2.entity_id ' . ' AND t.attribute_id = t2.attribute_id ' . $this->_connection->quoteInto(' AND t2.store_id = ?', $storeId), array())->where('e.entity_id = ' . $productId)->where('t.attribute_id IS NOT NULL');
$cursor = $this->_connection->query($select);
while ($row = $cursor->fetch(\Zend_Db::FETCH_ASSOC)) {
$updateData[$ids[$row['attribute_id']]] = $row['value'];
$valueColumnName = $ids[$row['attribute_id']] . $valueFieldSuffix;
if (isset($describe[$valueColumnName])) {
$valueColumns[$row['value']] = $valueColumnName;
}
}
//Update not simple attributes (eg. dropdown)
if (!empty($valueColumns)) {
$valueIds = array_keys($valueColumns);
$select = $this->_connection->select()->from(array('t' => $this->_productIndexerHelper->getTable('eav_attribute_option_value')), array('t.option_id', 't.value'))->where($this->_connection->quoteInto('t.option_id IN (?)', $valueIds));
$cursor = $this->_connection->query($select);
while ($row = $cursor->fetch(\Zend_Db::FETCH_ASSOC)) {
$valueColumnName = $valueColumns[$row['option_id']];
if (isset($describe[$valueColumnName])) {
$updateData[$valueColumnName] = $row['value'];
}
}
}
} else {
$columnNames = array_keys($columns);
$columnNames[] = 'attribute_set_id';
$columnNames[] = 'type_id';
$select->from(array('e' => $this->_productIndexerHelper->getTable('catalog_product_entity')), $columnNames)->where('e.entity_id = ' . $productId);
$cursor = $this->_connection->query($select);
$row = $cursor->fetch(\Zend_Db::FETCH_ASSOC);
if (!empty($row)) {
foreach ($row as $columnName => $value) {
$updateData[$columnName] = $value;
}
}
}
}
}
if (!empty($updateData)) {
$updateData += array('entity_id' => $productId);
$updateFields = array();
foreach ($updateData as $key => $value) {
$updateFields[$key] = $key;
}
$this->_connection->insertOnDuplicate($flatTable, $updateData, $updateFields);
}
return $this;
}
