/** * 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, ' ')); }
/** * 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()]; }
/** * Prepare tier price index table * * @param int|array $entityIds the entity ids limitation * @return \Magento\Catalog\Model\Indexer\Product\Price\AbstractAction */ protected function _prepareTierPriceIndex($entityIds = null) { $table = $this->_defaultIndexerResource->getTable('catalog_product_index_tier_price'); $this->_emptyTable($table); $websiteExpression = $this->_connection->getCheckSql('tp.website_id = 0', 'ROUND(tp.value * cwd.rate, 4)', 'tp.value'); $select = $this->_connection->select()->from(['tp' => $this->_defaultIndexerResource->getTable(['catalog_product_entity', 'tier_price'])], ['entity_id'])->join(['cg' => $this->_defaultIndexerResource->getTable('customer_group')], 'tp.all_groups = 1 OR (tp.all_groups = 0 AND tp.customer_group_id = cg.customer_group_id)', ['customer_group_id'])->join(['cw' => $this->_defaultIndexerResource->getTable('store_website')], 'tp.website_id = 0 OR tp.website_id = cw.website_id', ['website_id'])->join(['cwd' => $this->_defaultIndexerResource->getTable('catalog_product_index_website')], 'cw.website_id = cwd.website_id', [])->where('cw.website_id != 0')->columns(new \Zend_Db_Expr("MIN({$websiteExpression})"))->group(['tp.entity_id', 'cg.customer_group_id', 'cw.website_id']); if (!empty($entityIds)) { $select->where('tp.entity_id IN(?)', $entityIds); } $query = $select->insertFromSelect($table); $this->_connection->query($query); return $this; }
/** * Load product(s) attributes * * @param int $storeId * @param array $productIds * @param array $attributeTypes * @return array */ public function getProductAttributes($storeId, array $productIds, array $attributeTypes) { $result = []; $selects = []; $ifStoreValue = $this->connection->getCheckSql('t_store.value_id > 0', 't_store.value', 't_default.value'); foreach ($attributeTypes as $backendType => $attributeIds) { if ($attributeIds) { $tableName = $this->getTable('catalog_product_entity_' . $backendType); $selects[] = $this->connection->select()->from(['t_default' => $tableName], ['entity_id', 'attribute_id'])->joinLeft(['t_store' => $tableName], $this->connection->quoteInto('t_default.entity_id=t_store.entity_id' . ' AND t_default.attribute_id=t_store.attribute_id' . ' AND t_store.store_id = ?', $storeId), ['value' => $this->unifyField($ifStoreValue, $backendType)])->where('t_default.store_id = ?', 0)->where('t_default.attribute_id IN (?)', $attributeIds)->where('t_default.entity_id IN (?)', $productIds); } } if ($selects) { $select = $this->connection->select()->union($selects, \Magento\Framework\DB\Select::SQL_UNION_ALL); $query = $this->connection->query($select); while ($row = $query->fetch()) { $result[$row['entity_id']][$row['attribute_id']] = $row['value']; } } return $result; }
/** * 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); }
/** * @param AdapterInterface $connection * @param bool $isAggregate * @return mixed */ protected function getStatusExpression(AdapterInterface $connection, $isAggregate = false) { $isInStockExpression = $isAggregate ? 'MAX(cisi.is_in_stock)' : 'cisi.is_in_stock'; if ($this->_isManageStock()) { $statusExpr = $connection->getCheckSql('cisi.use_config_manage_stock = 0 AND cisi.manage_stock = 0', 1, $isInStockExpression); } else { $statusExpr = $connection->getCheckSql('cisi.use_config_manage_stock = 0 AND cisi.manage_stock = 1', $isInStockExpression, 1); } return $statusExpr; }