/** * 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"; }