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