PHP AdapterInterface::getIfNullSql方法代码示例

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