PHP AdapterInterface::getCheckSql方法代码示例

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