/**
* Prepare select statement for specific filter
*
* @param array $data
* @return \Magento\Framework\DB\Select
*/
protected function prepareSelect($data)
{
$select = $this->connection->select();
$select->from($this->resource->getTableName(self::TABLE_NAME));
foreach ($data as $column => $value) {
$select->where($this->connection->quoteIdentifier($column) . ' IN (?)', $value);
}
return $select;
}
/**
* Prepare select statement for specific filter
*
* @param Filter $filter
* @return \Magento\Framework\DB\Select
*/
protected function prepareSelect($filter)
{
$select = $this->connection->select();
$select->from(self::TABLE_NAME);
foreach ($filter->getFilter() as $column => $value) {
$select->where($this->connection->quoteIdentifier($column) . ' IN (?)', $value);
}
return $select;
}
/**
* Returns SQL expression
* TRIM(CONCAT_WS(separator, IF(str1 <> '', str1, NULL), IF(str2 <> '', str2, NULL) ...))
*
* @return string
*/
public function __toString()
{
$columns = [];
foreach ($this->columns as $key => $part) {
if (isset($part['columnName']) && $part['columnName'] instanceof \Zend_Db_Expr) {
$column = $part['columnName'];
} else {
$column = $this->adapter->quoteIdentifier((isset($part['tableAlias']) ? $part['tableAlias'] . '.' : '') . (isset($part['columnName']) ? $part['columnName'] : $key));
}
$columns[] = $this->adapter->getCheckSql($column . " <> ''", $column, 'NULL');
}
return sprintf('TRIM(%s)', $this->adapter->getConcatSql($columns, ' '));
}
/**
* Populate the temporary category tree index table
*
* @param string $temporaryName
*/
protected function fillTempCategoryTreeIndex($temporaryName)
{
// This finds all children (cc2) that descend from a parent (cc) by path.
// For example, cc.path may be '1/2', and cc2.path may be '1/2/3/4/5'.
$temporarySelect = $this->connection->select()->from(['cc' => $this->getTable('catalog_category_entity')], ['parent_id' => 'entity_id'])->joinInner(['cc2' => $this->getTable('catalog_category_entity')], 'cc2.path LIKE ' . $this->connection->getConcatSql([$this->connection->quoteIdentifier('cc.path'), $this->connection->quote('/%')]), ['child_id' => 'entity_id']);
$this->connection->query($temporarySelect->insertFromSelect($temporaryName, ['parent_id', 'child_id']));
}
/**
* @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;
}
/**
* Create anchor select
*
* @param \Magento\Store\Model\Store $store
* @return \Magento\Framework\DB\Select
*/
protected function createAnchorSelect(\Magento\Store\Model\Store $store)
{
$isAnchorAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Category::ENTITY, 'is_anchor')->getId();
$statusAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'status')->getId();
$visibilityAttributeId = $this->config->getAttribute(\Magento\Catalog\Model\Product::ENTITY, 'visibility')->getId();
$rootCatIds = explode('/', $this->getPathFromCategoryId($store->getRootCategoryId()));
array_pop($rootCatIds);
return $this->connection->select()->from(['cc' => $this->getTable('catalog_category_entity')], [])->joinInner(['cc2' => $this->getTable('catalog_category_entity')], 'cc2.path LIKE ' . $this->connection->getConcatSql([$this->connection->quoteIdentifier('cc.path'), $this->connection->quote('/%')]) . ' AND cc.entity_id NOT IN (' . implode(',', $rootCatIds) . ')', [])->joinInner(['ccp' => $this->getTable('catalog_category_product')], 'ccp.category_id = cc2.entity_id', [])->joinInner(['cpw' => $this->getTable('catalog_product_website')], 'cpw.product_id = ccp.product_id', [])->joinInner(['cpsd' => $this->getTable('catalog_product_entity_int')], 'cpsd.entity_id = ccp.product_id AND cpsd.store_id = 0' . ' AND cpsd.attribute_id = ' . $statusAttributeId, [])->joinLeft(['cpss' => $this->getTable('catalog_product_entity_int')], 'cpss.entity_id = ccp.product_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 = ccp.product_id AND cpvd.store_id = 0' . ' AND cpvd.attribute_id = ' . $visibilityAttributeId, [])->joinLeft(['cpvs' => $this->getTable('catalog_product_entity_int')], 'cpvs.entity_id = ccp.product_id AND cpvs.attribute_id = cpvd.attribute_id ' . 'AND cpvs.store_id = ' . $store->getId(), [])->joinInner(['ccad' => $this->getTable('catalog_category_entity_int')], 'ccad.entity_id = cc.entity_id AND ccad.store_id = 0' . ' AND ccad.attribute_id = ' . $isAnchorAttributeId, [])->joinLeft(['ccas' => $this->getTable('catalog_category_entity_int')], 'ccas.entity_id = cc.entity_id AND ccas.attribute_id = ccad.attribute_id' . ' AND ccas.store_id = ' . $store->getId(), [])->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])->where($this->connection->getIfNullSql('ccas.value', 'ccad.value') . ' = ?', 1)->columns(['category_id' => 'cc.entity_id', 'product_id' => 'ccp.product_id', 'position' => new \Zend_Db_Expr('ccp.position + 10000'), 'is_parent' => new \Zend_Db_Expr('0'), 'store_id' => new \Zend_Db_Expr($store->getId()), 'visibility' => new \Zend_Db_Expr($this->connection->getIfNullSql('cpvs.value', 'cpvd.value'))]);
}
/**
* Process ORDER BY clause
*
* @param Select $select
* @return void
*/
protected function processSortOrder(Select $select)
{
foreach ($this->sortOrder as $direction => $column) {
if (!in_array($direction, [Select::SQL_ASC, Select::SQL_DESC])) {
$direction = '';
}
$expr = new \Zend_Db_Expr(sprintf('%s %s', $this->adapter->quoteIdentifier('lookup.' . $column), $direction));
$select->order($expr);
}
}
/**
* @param Dimension $dimension
* @param AdapterInterface $adapter
* @return string
*/
private function generateExpression(Dimension $dimension, AdapterInterface $adapter)
{
$identifier = $dimension->getName();
$value = $dimension->getValue();
if (self::DEFAULT_DIMENSION_NAME === $identifier) {
$identifier = self::STORE_FIELD_NAME;
$value = $this->scopeResolver->getScope($value)->getId();
}
return sprintf('%s = %s', $adapter->quoteIdentifier($identifier), $adapter->quote($value));
}
/**
* Build trigger statement for INSER, UPDATE, DELETE events
*
* @param string $event
* @param \Magento\Framework\Mview\View\ChangelogInterface $changelog
* @return string
*/
protected function buildStatement($event, $changelog)
{
switch ($event) {
case Trigger::EVENT_INSERT:
case Trigger::EVENT_UPDATE:
return sprintf("INSERT IGNORE INTO %s (%s) VALUES (NEW.%s);", $this->connection->quoteIdentifier($this->resource->getTableName($changelog->getName())), $this->connection->quoteIdentifier($changelog->getColumnName()), $this->connection->quoteIdentifier($this->getColumnName()));
case Trigger::EVENT_DELETE:
return sprintf("INSERT IGNORE INTO %s (%s) VALUES (OLD.%s);", $this->connection->quoteIdentifier($this->resource->getTableName($changelog->getName())), $this->connection->quoteIdentifier($changelog->getColumnName()), $this->connection->quoteIdentifier($this->getColumnName()));
default:
return '';
}
}
/**
* Load node
*
* @param int|string $nodeId
* @return Node
*/
public function loadNode($nodeId)
{
$select = clone $this->_select;
if (is_numeric($nodeId)) {
$condField = $this->_conn->quoteIdentifier([$this->_table, $this->_idField]);
} else {
$condField = $this->_conn->quoteIdentifier([$this->_table, $this->_pathField]);
}
$select->where("{$condField} = ?", $nodeId);
$node = new Node($this->_conn->fetchRow($select), $this->_idField, $this);
$this->addNode($node);
return $node;
}
/**
* @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 '';
}
/**
* @param Node $node
* @return $this
* @throws \Exception
*/
public function removeNode($node)
{
// For reorder old node branch
$dataReorderOld = [$this->_orderField => new \Zend_Db_Expr($this->_conn->quoteIdentifier($this->_orderField) . '-1')];
$conditionReorderOld = $this->_conn->quoteIdentifier($this->_parentField) . '=' . $node->getData($this->_parentField) . ' AND ' . $this->_conn->quoteIdentifier($this->_orderField) . '>' . $node->getData($this->_orderField);
$this->_conn->beginTransaction();
try {
$condition = $this->_conn->quoteInto("{$this->_idField}=?", $node->getId());
$this->_conn->delete($this->_table, $condition);
// Update old node branch
$this->_conn->update($this->_table, $dataReorderOld, $conditionReorderOld);
$this->_conn->commit();
} catch (\Exception $e) {
$this->_conn->rollBack();
throw new \Exception('Can\'t remove tree node');
}
parent::removeNode($node);
return $this;
}
/**
* Get all database triggers
*
* @return void
*/
protected function loadTriggers()
{
$schema = $this->getSchemaName();
if ($schema) {
$sqlFilter = $this->resourceAdapter->quoteIdentifier('TRIGGER_SCHEMA') . ' = ' . $this->resourceAdapter->quote($schema);
} else {
$sqlFilter = $this->resourceAdapter->quoteIdentifier('TRIGGER_SCHEMA') . ' != ' . $this->resourceAdapter->quote('INFORMATION_SCHEMA');
}
$select = $this->getSelect()->from(new \Zend_Db_Expr($this->resourceAdapter->quoteIdentifier(['INFORMATION_SCHEMA', 'TRIGGERS'])))->where($sqlFilter);
$results = $this->resourceAdapter->query($select);
$data = [];
foreach ($results as $row) {
$row = array_change_key_case($row, CASE_LOWER);
$row['action_statement'] = $this->convertStatement($row['action_statement']);
$key = $row['event_object_table'] . $row['event_manipulation'] . $row['action_timing'];
$data[$key] = $row;
}
$this->triggers = $data;
}
/**
* Update suffix for url rewrites
*
* @return $this
*/
protected function updateSuffixForUrlRewrites()
{
$map = [ProductUrlPathGenerator::XML_PATH_PRODUCT_URL_SUFFIX => ProductUrlRewriteGenerator::ENTITY_TYPE, CategoryUrlPathGenerator::XML_PATH_CATEGORY_URL_SUFFIX => CategoryUrlRewriteGenerator::ENTITY_TYPE];
if (!isset($map[$this->getPath()])) {
return $this;
}
$dataFilter = [UrlRewrite::ENTITY_TYPE => $map[$this->getPath()]];
$storesIds = $this->getStoreIds();
if ($storesIds) {
$dataFilter[UrlRewrite::STORE_ID] = $storesIds;
}
$entities = $this->urlFinder->findAllByData($dataFilter);
$oldSuffixPattern = '~' . preg_quote($this->getOldValue()) . '$~';
$suffix = $this->getValue();
foreach ($entities as $urlRewrite) {
$bind = $urlRewrite->getIsAutogenerated() ? [UrlRewrite::REQUEST_PATH => preg_replace($oldSuffixPattern, $suffix, $urlRewrite->getRequestPath())] : [UrlRewrite::TARGET_PATH => preg_replace($oldSuffixPattern, $suffix, $urlRewrite->getTargetPath())];
$this->connection->update(DbStorage::TABLE_NAME, $bind, $this->connection->quoteIdentifier(UrlRewrite::URL_REWRITE_ID) . ' = ' . $urlRewrite->getUrlRewriteId());
}
return $this;
}
/**
* 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);
}
/**
* Array of SKU to array of super attribute values for all products.
*
* @param array $bunch
* @return $this
*/
protected function _loadSkuSuperDataForBunch(array $bunch)
{
$newSku = $this->_entityModel->getNewSku();
$oldSku = $this->_entityModel->getOldSku();
$productIds = [];
foreach ($bunch as $rowData) {
$sku = $rowData[ImportProduct::COL_SKU];
$productData = isset($newSku[$sku]) ? $newSku[$sku] : $oldSku[$sku];
$productIds[] = $productData['entity_id'];
}
$this->_productSuperAttrs = [];
$this->_skuSuperData = [];
if (!empty($productIds)) {
$mainTable = $this->_resource->getTableName('catalog_product_super_attribute');
$optionTable = $this->_resource->getTableName('eav_attribute_option');
$select = $this->_connection->select()->from(['m' => $mainTable], ['product_id', 'attribute_id', 'product_super_attribute_id'])->joinLeft(['o' => $optionTable], $this->_connection->quoteIdentifier('o.attribute_id') . ' = ' . $this->_connection->quoteIdentifier('o.attribute_id'), ['option_id'])->where('product_id IN ( ? )', $productIds);
foreach ($this->_connection->fetchAll($select) as $row) {
$attrId = $row['attribute_id'];
$productId = $row['product_id'];
if ($row['option_id']) {
$this->_skuSuperData[$productId][$attrId][$row['option_id']] = true;
}
$this->_productSuperAttrs["{$productId}_{$attrId}"] = $row['product_super_attribute_id'];
}
}
return $this;
}
/**
* @param string $field
* @param string $operator
* @param mixed $value
* @return string
*/
public function generateCondition($field, $operator, $value)
{
return sprintf(is_array($value) ? self::CONDITION_PATTERN_ARRAY : self::CONDITION_PATTERN_SIMPLE, $this->adapter->quoteIdentifier($field), $operator, $this->adapter->quote($value));
}
/**
* Return attribute values for given entities and store of specific attribute type
*
* @param string $type
* @param array $entityIds
* @param integer $storeId
* @return array
*/
protected function getAttributeTypeValues($type, $entityIds, $storeId)
{
$linkField = $this->getCategoryMetadata()->getLinkField();
$select = $this->connection->select()->from(['def' => $this->connection->getTableName($this->getTableName('catalog_category_entity_' . $type))], [$linkField, 'attribute_id'])->joinLeft(['e' => $this->connection->getTableName($this->getTableName('catalog_category_entity'))], "def.{$linkField} = e.{$linkField}")->joinLeft(['store' => $this->connection->getTableName($this->getTableName('catalog_category_entity_' . $type))], "store.{$linkField} = def.{$linkField} AND store.attribute_id = def.attribute_id " . 'AND store.store_id = ' . $storeId, ['value' => $this->connection->getCheckSql('store.value_id > 0', $this->connection->quoteIdentifier('store.value'), $this->connection->quoteIdentifier('def.value'))])->where("e.entity_id IN (?)", $entityIds)->where('def.store_id IN (?)', [\Magento\Store\Model\Store::DEFAULT_STORE_ID, $storeId]);
return $this->connection->fetchAll($select);
}
/**
* Retrieve table header comment
*
* @param string $tableName
* @return string
*/
public function getTableHeader($tableName)
{
$quotedTableName = $this->connection->quoteIdentifier($tableName);
return "\n--\n" . "-- Table structure for table {$quotedTableName}\n" . "--\n\n";
}
