PHP Select::columns方法代码示例

 /**
  * Get category collection array
  *
  * @param null|string|bool|int|\Magento\Store\Model\Store $storeId
  * @return array|bool
  */
 public function getCollection($storeId)
 {
     $products = [];
     /* @var $store \Magento\Store\Model\Store */
     $store = $this->_storeManager->getStore($storeId);
     if (!$store) {
         return false;
     }
     $connection = $this->getConnection();
     $this->_select = $connection->select()->from(['e' => $this->getMainTable()], [$this->getIdFieldName(), 'updated_at'])->joinInner(['w' => $this->getTable('catalog_product_website')], 'e.entity_id = w.product_id', [])->joinLeft(['url_rewrite' => $this->getTable('url_rewrite')], 'e.entity_id = url_rewrite.entity_id AND url_rewrite.is_autogenerated = 1' . $connection->quoteInto(' AND url_rewrite.store_id = ?', $store->getId()) . $connection->quoteInto(' AND url_rewrite.entity_type = ?', ProductUrlRewriteGenerator::ENTITY_TYPE), ['url' => 'request_path'])->where('w.website_id = ?', $store->getWebsiteId());
     $this->_addFilter($store->getId(), 'visibility', $this->_productVisibility->getVisibleInSiteIds(), 'in');
     $this->_addFilter($store->getId(), 'status', $this->_productStatus->getVisibleStatusIds(), 'in');
     // Join product images required attributes
     $imageIncludePolicy = $this->_sitemapData->getProductImageIncludePolicy($store->getId());
     if (\Magento\Sitemap\Model\Source\Product\Image\IncludeImage::INCLUDE_NONE != $imageIncludePolicy) {
         $this->_joinAttribute($store->getId(), 'name');
         $this->_select->columns(['name' => $this->getConnection()->getIfNullSql('t2_name.value', 't1_name.value')]);
         if (\Magento\Sitemap\Model\Source\Product\Image\IncludeImage::INCLUDE_ALL == $imageIncludePolicy) {
             $this->_joinAttribute($store->getId(), 'thumbnail');
             $this->_select->columns(['thumbnail' => $this->getConnection()->getIfNullSql('t2_thumbnail.value', 't1_thumbnail.value')]);
         } elseif (\Magento\Sitemap\Model\Source\Product\Image\IncludeImage::INCLUDE_BASE == $imageIncludePolicy) {
             $this->_joinAttribute($store->getId(), 'image');
             $this->_select->columns(['image' => $this->getConnection()->getIfNullSql('t2_image.value', 't1_image.value')]);
         }
     }
     $query = $connection->query($this->_select);
     while ($row = $query->fetch()) {
         $product = $this->_prepareProduct($row, $store->getId());
         $products[$product->getId()] = $product;
     }
     return $products;
 }

 /**
  * Apply price rule price to price index table
  *
  * @param \Magento\Framework\DB\Select $select
  * @param array|string $indexTable
  * @param string $entityId
  * @param string $customerGroupId
  * @param string $websiteId
  * @param array $updateFields       the array of fields for compare with rule price and update
  * @param string $websiteDate
  * @return \Magento\CatalogRule\Model\ResourceModel\Rule\Product\Price
  */
 public function applyPriceRuleToIndexTable(\Magento\Framework\DB\Select $select, $indexTable, $entityId, $customerGroupId, $websiteId, $updateFields, $websiteDate)
 {
     if (empty($updateFields)) {
         return $this;
     }
     if (is_array($indexTable)) {
         foreach ($indexTable as $k => $v) {
             if (is_string($k)) {
                 $indexAlias = $k;
             } else {
                 $indexAlias = $v;
             }
             break;
         }
     } else {
         $indexAlias = $indexTable;
     }
     $select->join(['rp' => $this->getMainTable()], "rp.rule_date = {$websiteDate}", [])->where("rp.product_id = {$entityId} AND rp.website_id = {$websiteId} AND rp.customer_group_id = {$customerGroupId}");
     foreach ($updateFields as $priceField) {
         $priceCond = $this->getConnection()->quoteIdentifier([$indexAlias, $priceField]);
         $priceExpr = $this->getConnection()->getCheckSql("rp.rule_price < {$priceCond}", 'rp.rule_price', $priceCond);
         $select->columns([$priceField => $priceExpr]);
     }
     $query = $select->crossUpdateFromSelect($indexTable);
     $this->getConnection()->query($query);
     return $this;
 }

 /**
  * @param Select $select
  * @param AggregationRange[] $ranges
  * @return Select
  */
 private function generateCase(Select $select, array $ranges)
 {
     $casesResults = [];
     $field = RequestBucketInterface::FIELD_VALUE;
     foreach ($ranges as $range) {
         $from = $range->getFrom();
         $to = $range->getTo();
         if ($from && $to) {
             $casesResults = array_merge($casesResults, ["`{$field}` BETWEEN {$from} AND {$to}" => "'{$from}_{$to}'"]);
         } elseif ($from) {
             $casesResults = array_merge($casesResults, ["`{$field}` >= {$from}" => "'{$from}_*'"]);
         } elseif ($to) {
             $casesResults = array_merge($casesResults, ["`{$field}` < {$to}" => "'*_{$to}'"]);
         }
     }
     $cases = $this->connection->getCaseSql('', $casesResults);
     $select->columns([RequestBucketInterface::FIELD_VALUE => $cases]);
     return $select;
 }

 /**
  * @param \Magento\Framework\DB\Select $select
  * @param string $table
  * @param string $type
  * @return \Magento\Framework\DB\Select
  */
 protected function _addLoadAttributesSelectValues($select, $table, $type)
 {
     $storeId = $this->getStoreId();
     if ($storeId) {
         $connection = $this->getConnection();
         $valueExpr = $connection->getCheckSql('t_s.value_id IS NULL', 't_d.value', 't_s.value');
         $select->columns(['default_value' => 't_d.value', 'store_value' => 't_s.value', 'value' => $valueExpr]);
     } else {
         $select = parent::_addLoadAttributesSelectValues($select, $table, $type);
     }
     return $select;
 }

 /**
  * Add select values
  *
  * @param \Magento\Framework\DB\Select $select
  * @param string $table
  * @param string $type
  * @return Select
  * @SuppressWarnings(PHPMD.UnusedFormalParameter)
  */
 protected function _addLoadAttributesSelectValues($select, $table, $type)
 {
     $select->columns(['value' => $table . '.value']);
     return $select;
 }

 /**
  * Adds order by random to select object
  * Possible using integer field for optimization
  *
  * @param Select $select
  * @param string $field
  * @return $this
  */
 public function orderRand(Select $select, $field = null)
 {
     if ($field !== null) {
         $expression = new \Zend_Db_Expr(sprintf('RAND() * %s', $this->quoteIdentifier($field)));
         $select->columns(array('mage_rand' => $expression));
         $spec = new \Zend_Db_Expr('mage_rand');
     } else {
         $spec = new \Zend_Db_Expr('RAND()');
     }
     $select->order($spec);
     return $this;
 }

    /**
     * @param RequestInterface $request
     * @param MatchContainer[] $matchQueries
     * @param ScoreBuilder $scoreBuilder
     * @param Select $select
     * @param IndexBuilderInterface $indexBuilder
     * @return Select
     * @internal param QueryContainer $queryContainer
     */
    private function addMatchQueries(
        RequestInterface $request,
        array $matchQueries,
        ScoreBuilder $scoreBuilder,
        Select $select,
        IndexBuilderInterface $indexBuilder
    ) {
        if (!$matchQueries) {
            $select->columns($scoreBuilder->build());
            $select = $this->createAroundSelect($select, $scoreBuilder);
        } elseif (count($matchQueries) === 1) {
            $matchContainer = reset($matchQueries);
            $this->matchBuilder->build(
                $scoreBuilder,
                $select,
                $matchContainer->getRequest(),
                $matchContainer->getConditionType()
            );
            $select->columns($scoreBuilder->build());
            $select = $this->createAroundSelect($select, $scoreBuilder);
        } elseif (count($matchQueries) > 1) {
            $select->columns($scoreBuilder->build());
            $select = $this->createAroundSelect($select, $scoreBuilder);
            $subSelect = $select;
            $select = $this->resource->getConnection(Resource::DEFAULT_READ_RESOURCE)->select();
            $tables = array_merge(array_keys($matchQueries), ['main_select.relevance']);
            $relevance = implode('.relevance + ', $tables);
            $select
                ->from(
                    ['main_select' => $subSelect],
                    [
                        $this->entityMetadata->getEntityId() => 'entity_id',
                        'relevance' => sprintf('(%s)', $relevance),
                    ]
                );

            foreach ($matchQueries as $matchName => $matchContainer) {
                $matchSelect = $indexBuilder->build($request);
                $matchScoreBuilder = $this->scoreBuilderFactory->create();
                $matchSelect = $this->matchBuilder->build(
                    $matchScoreBuilder,
                    $matchSelect,
                    $matchContainer->getRequest(),
                    $matchContainer->getConditionType()
                );
                $matchSelect->columns($matchScoreBuilder->build());
                $select->join(
                    [$matchName => $this->createAroundSelect($matchSelect, $scoreBuilder)],
                    $matchName . '.entity_id = main_select.entity_id',
                    []
                );
            }
        }
        return $select;
    }

 /**
  * @param Select $query
  * @param Table $previousResultTable
  * @param ScoreBuilder $scoreBuilder
  * @return Select
  * @throws \Zend_Db_Exception
  */
 private function joinPreviousResultToSelect(Select $query, Table $previousResultTable, ScoreBuilder $scoreBuilder)
 {
     $query->joinInner(['previous_results' => $previousResultTable->getName()], 'previous_results.entity_id = search_index.entity_id', []);
     $scoreBuilder->addCondition('previous_results.score', false);
     $query->columns($scoreBuilder->build());
     $query = $this->createAroundSelect($query, $scoreBuilder);
     return $query;
 }

 /**
  * Add prepared column group_concat expression
  *
  * @param \Magento\Framework\DB\Select $select
  * @param string $fieldAlias Field alias which will be added with column group_concat expression
  * @param string $fields
  * @param string $groupConcatDelimiter
  * @param string $fieldsDelimiter
  * @param string $additionalWhere
  * @return \Magento\Framework\DB\Select
  */
 public function addGroupConcatColumn($select, $fieldAlias, $fields, $groupConcatDelimiter = ',', $fieldsDelimiter = '', $additionalWhere = '')
 {
     if (is_array($fields)) {
         $fieldExpr = $this->_getReadAdapter()->getConcatSql($fields, $fieldsDelimiter);
     } else {
         $fieldExpr = $fields;
     }
     if ($additionalWhere) {
         $fieldExpr = $this->_getReadAdapter()->getCheckSql($additionalWhere, $fieldExpr, "''");
     }
     $separator = '';
     if ($groupConcatDelimiter) {
         $separator = sprintf(" SEPARATOR '%s'", $groupConcatDelimiter);
     }
     $select->columns([$fieldAlias => new \Zend_Db_Expr(sprintf('GROUP_CONCAT(%s%s)', $fieldExpr, $separator))]);
     return $select;
 }

 /**
  * Joins attribute value to a select
  *
  * @param Select $select
  * @param object $attribute
  * @param string $tableAlias
  * @return $this
  */
 public function joinAttribute(Select $select, $attribute, $tableAlias = 'main')
 {
     $defaultAlias = sprintf('attribute_%s_default', $attribute->code);
     $scopeAlias = sprintf('attribute_%s_scope', $attribute->code);
     $attributeTable = $this->getTable(['entity', $attribute->type]);
     if (1 - $attribute->required > 0.01) {
         $select->joinLeft([$defaultAlias => $attributeTable], $this->andCondition([sprintf('%s.entity_id = %s.entity_id', $defaultAlias, $tableAlias), sprintf('%s.attribute_id = ?', $defaultAlias) => $attribute->id, sprintf('%s.scope_id = ?', $defaultAlias) => 0]), []);
     } else {
         $select->join([$defaultAlias => $attributeTable], $this->andCondition([sprintf('%s.entity_id = %s.entity_id', $defaultAlias, $tableAlias), sprintf('%s.attribute_id = ?', $defaultAlias) => $attribute->id, sprintf('%s.scope_id = ?', $defaultAlias) => 0]), []);
     }
     if ($attribute->scope && $this->scopeCode) {
         $select->joinLeft([$scopeAlias => $attributeTable], $this->andCondition([sprintf('%s.entity_id = %s.entity_id', $defaultAlias, $scopeAlias), sprintf('%s.attribute_id = %s.attribute_id', $defaultAlias, $scopeAlias), sprintf('%s.scope_id = ?', $scopeAlias) => $this->scope->getId($this->scopeCode)]), []);
         $valueExpression = sprintf('IF(%2$s.value_id IS NOT NULL, %2$s.value, %1$s.value)', $defaultAlias, $scopeAlias);
     } else {
         $valueExpression = sprintf('%s.value', $defaultAlias);
     }
     $select->columns([$attribute->code => $valueExpression]);
     return $valueExpression;
 }

 /**
  * Join information for usin full text search
  *
  * @param string $table
  * @param string $alias
  * @param \Magento\Framework\DB\Select $select
  * @return \Zend_Db_Expr
  */
 public function chooseFulltext($table, $alias, $select)
 {
     $field = new \Zend_Db_Expr('MATCH (' . $alias . '.data_index) AGAINST (:query IN BOOLEAN MODE)');
     $select->columns(array('relevance' => $field));
     return $field;
 }