PHP Select::where方法代码示例

 /**
  * Add attribute to filter
  *
  * @param int $storeId
  * @param string $attributeCode
  * @param mixed $value
  * @param string $type
  * @return \Magento\Framework\DB\Select|bool
  */
 protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
 {
     if (!$this->_select instanceof \Magento\Framework\DB\Select) {
         return false;
     }
     if (!isset($this->_attributesCache[$attributeCode])) {
         $attribute = $this->_categoryResource->getAttribute($attributeCode);
         $this->_attributesCache[$attributeCode] = ['entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal(), 'backend_type' => $attribute->getBackendType()];
     }
     $attribute = $this->_attributesCache[$attributeCode];
     switch ($type) {
         case '=':
             $conditionRule = '=?';
             break;
         case 'in':
             $conditionRule = ' IN(?)';
             break;
         default:
             return false;
             break;
     }
     if ($attribute['backend_type'] == 'static') {
         $this->_select->where('e.' . $attributeCode . $conditionRule, $value);
     } else {
         $this->_select->join(['t1_' . $attributeCode => $attribute['table']], 'e.entity_id = t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id = 0', [])->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
         if ($attribute['is_global']) {
             $this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
         } else {
             $ifCase = $this->getConnection()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
             $this->_select->joinLeft(['t2_' . $attributeCode => $attribute['table']], $this->getConnection()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), [])->where('(' . $ifCase . ')' . $conditionRule, $value);
         }
     }
     return $this->_select;
 }

 /**
  * {@inheritdoc}
  */
 public function build(ScoreBuilder $scoreBuilder, Select $select, RequestQueryInterface $query, $conditionType)
 {
     /** @var $query \Magento\Framework\Search\Request\Query\Match */
     $queryValue = $this->prepareQuery($query->getValue(), $conditionType);
     $fieldList = [];
     foreach ($query->getMatches() as $match) {
         $fieldList[] = $match['field'];
     }
     $resolvedFieldList = $this->resolver->resolve($fieldList);
     $fieldIds = [];
     $columns = [];
     foreach ($resolvedFieldList as $field) {
         if ($field->getType() === FieldInterface::TYPE_FULLTEXT && $field->getAttributeId()) {
             $fieldIds[] = $field->getAttributeId();
         }
         $column = $field->getColumn();
         $columns[$column] = $column;
     }
     $matchQuery = $this->fulltextHelper->getMatchQuery($columns, $queryValue, $this->fulltextSearchMode);
     $scoreBuilder->addCondition($matchQuery, true);
     if ($fieldIds) {
         $matchQuery = sprintf('(%s AND search_index.attribute_id IN (%s))', $matchQuery, implode(',', $fieldIds));
     }
     $select->where($matchQuery);
     return $select;
 }

 /**
  * Add attribute to filter
  *
  * @param int $storeId
  * @param string $attributeCode
  * @param mixed $value
  * @param string $type
  * @return \Magento\Framework\DB\Select|bool
  */
 protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
 {
     if (!$this->_select instanceof \Magento\Framework\DB\Select) {
         return false;
     }
     switch ($type) {
         case '=':
             $conditionRule = '=?';
             break;
         case 'in':
             $conditionRule = ' IN(?)';
             break;
         default:
             return false;
             break;
     }
     $attribute = $this->_getAttribute($attributeCode);
     if ($attribute['backend_type'] == 'static') {
         $this->_select->where('e.' . $attributeCode . $conditionRule, $value);
     } else {
         $this->_joinAttribute($storeId, $attributeCode);
         if ($attribute['is_global']) {
             $this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
         } else {
             $ifCase = $this->getConnection()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
             $this->_select->where('(' . $ifCase . ')' . $conditionRule, $value);
         }
     }
     return $this->_select;
 }

 /**
  * Method for FULLTEXT search in Mysql, will added generated
  * MATCH ($columns) AGAINST ('$expression' $mode) to where clause
  *
  * @param \Magento\Framework\DB\Select $select
  * @param string|string[] $columns Columns which add to MATCH ()
  * @param string $expression Expression which add to AGAINST ()
  * @param bool $isCondition true=AND, false=OR
  * @param string $mode
  * @return \Magento\Framework\DB\Select
  */
 public function match($select, $columns, $expression, $isCondition = true, $mode = self::FULLTEXT_MODE_NATURAL)
 {
     $fullCondition = $this->getMatchQuery($columns, $expression, $mode);
     if ($isCondition) {
         $select->where($fullCondition);
     } else {
         $select->orWhere($fullCondition);
     }
     return $select;
 }

 /**
  * @inheritdoc
  */
 public function addFieldToFilter($field, $condition = null)
 {
     if (is_array($field)) {
         $conditions = [];
         foreach ($field as $key => $value) {
             $conditions[] = $this->translateCondition($value, isset($condition[$key]) ? $condition[$key] : null);
         }
         $resultCondition = '(' . implode(') ' . \Zend_Db_Select::SQL_OR . ' (', $conditions) . ')';
     } else {
         $resultCondition = $this->translateCondition($field, $condition);
     }
     $this->select->where($resultCondition, null, Select::TYPE_CONDITION);
 }

 /**
  * Process WHERE clause
  *
  * @param Select $select
  * @return void
  */
 protected function processWhereCondition(Select $select)
 {
     foreach ($this->referenceColumns as $column => $referenceColumn) {
         $identifier = '';
         if (isset($referenceColumn['tableAlias'])) {
             $identifier = $referenceColumn['tableAlias'] . '.';
         }
         $columnName = $column;
         if (isset($referenceColumn['columnName'])) {
             $columnName = $referenceColumn['columnName'];
         }
         $select->where(sprintf('%s = %s', $this->adapter->quoteIdentifier('lookup.' . $column), $this->adapter->quoteIdentifier($identifier . $columnName)));
     }
 }

 /**
  * @param \Magento\Framework\DB\Select $select
  * @return \Magento\Framework\DB\Select
  */
 public function prepareSelect(\Magento\Framework\DB\Select $select)
 {
     $select->where('website_id = :website_id')->order(['dest_country_id DESC', 'dest_region_id DESC', 'dest_zip DESC'])->limit(1);
     // Render destination condition
     $orWhere = '(' . implode(') OR (', ["dest_country_id = :country_id AND dest_region_id = :region_id AND dest_zip = :postcode", "dest_country_id = :country_id AND dest_region_id = :region_id AND dest_zip = ''", "dest_country_id = :country_id AND dest_region_id = :region_id AND dest_zip = '*'", "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = '*'", "dest_country_id = '0' AND dest_region_id = :region_id AND dest_zip = '*'", "dest_country_id = '0' AND dest_region_id = 0 AND dest_zip = '*'", "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = ''", "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = :postcode", "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = '*'"]) . ')';
     $select->where($orWhere);
     // Render condition by condition name
     if (is_array($this->request->getConditionName())) {
         $orWhere = [];
         foreach (range(0, count($this->request->getConditionName())) as $conditionNumber) {
             $bindNameKey = sprintf(':condition_name_%d', $conditionNumber);
             $bindValueKey = sprintf(':condition_value_%d', $conditionNumber);
             $orWhere[] = "(condition_name = {$bindNameKey} AND condition_value <= {$bindValueKey})";
         }
         if ($orWhere) {
             $select->where(implode(' OR ', $orWhere));
         }
     } else {
         $select->where('condition_name = :condition_name');
         $select->where('condition_value <= :condition_value');
     }
     return $select;
 }

 /**
  * Add attribute join condition to select and return \Zend_Db_Expr
  * attribute value definition
  * If $condition is not empty apply limitation for select
  *
  * @param \Magento\Framework\DB\Select $select
  * @param string $attrCode              the attribute code
  * @param string|\Zend_Db_Expr $entity   the entity field or expression for condition
  * @param string|\Zend_Db_Expr $store    the store field or expression for condition
  * @param \Zend_Db_Expr $condition       the limitation condition
  * @param bool $required                if required or has condition used INNER join, else - LEFT
  * @return \Zend_Db_Expr                 the attribute value expression
  */
 protected function _addAttributeToSelect($select, $attrCode, $entity, $store, $condition = null, $required = false)
 {
     $attribute = $this->_getAttribute($attrCode);
     $attributeId = $attribute->getAttributeId();
     $attributeTable = $attribute->getBackend()->getTable();
     $connection = $this->getConnection();
     $joinType = $condition !== null || $required ? 'join' : 'joinLeft';
     if ($attribute->isScopeGlobal()) {
         $alias = 'ta_' . $attrCode;
         $select->{$joinType}([$alias => $attributeTable], "{$alias}.entity_id = {$entity} AND {$alias}.attribute_id = {$attributeId}" . " AND {$alias}.store_id = 0", []);
         $expression = new \Zend_Db_Expr("{$alias}.value");
     } else {
         $dAlias = 'tad_' . $attrCode;
         $sAlias = 'tas_' . $attrCode;
         $select->{$joinType}([$dAlias => $attributeTable], "{$dAlias}.entity_id = {$entity} AND {$dAlias}.attribute_id = {$attributeId}" . " AND {$dAlias}.store_id = 0", []);
         $select->joinLeft([$sAlias => $attributeTable], "{$sAlias}.entity_id = {$entity} AND {$sAlias}.attribute_id = {$attributeId}" . " AND {$sAlias}.store_id = {$store}", []);
         $expression = $connection->getCheckSql($connection->getIfNullSql("{$sAlias}.value_id", -1) . ' > 0', "{$sAlias}.value", "{$dAlias}.value");
     }
     if ($condition !== null) {
         $select->where("{$expression}{$condition}");
     }
     return $expression;
 }

 /**
  * Add filtering by dimensions
  *
  * @param RequestInterface $request
  * @param Select $select
  * @return \Magento\Framework\DB\Select
  */
 private function processDimensions(RequestInterface $request, Select $select)
 {
     $dimensions = $this->prepareDimensions($request->getDimensions());
     $query = $this->conditionManager->combineQueries($dimensions, Select::SQL_OR);
     if (!empty($query)) {
         $select->where($this->conditionManager->wrapBrackets($query));
     }
     return $select;
 }

 protected function applyCondition(Select $select)
 {
     $select->where('attribute.entity_id IN(?)', $this->entityIds);
 }

 /**
  * Apply stores filter to select object
  *
  * @param \Magento\Framework\DB\Select $select
  * @return $this
  */
 protected function _applyStoresFilterToSelect(\Magento\Framework\DB\Select $select)
 {
     $nullCheck = false;
     $storeIds = $this->_storesIds;
     if (!is_array($storeIds)) {
         $storeIds = [$storeIds];
     }
     $storeIds = array_unique($storeIds);
     if ($index = array_search(null, $storeIds)) {
         unset($storeIds[$index]);
         $nullCheck = true;
     }
     if ($nullCheck) {
         $select->where('store_id IN(?) OR store_id IS NULL', $storeIds);
     } else {
         $select->where('store_id IN(?)', $storeIds);
     }
     return $this;
 }

    /**
     * Add filtering by dimensions
     *
     * @param RequestInterface $request
     * @param Select $select
     * @return \Magento\Framework\DB\Select
     */
    private function processDimensions(RequestInterface $request, Select $select)
    {
        $dimensions = [];
        foreach ($request->getDimensions() as $dimension) {
            $dimensions[] = $this->dimensionsBuilder->build($dimension);
        }

        $query = $this->conditionManager->combineQueries($dimensions, Select::SQL_OR);
        if (!empty($query)) {
            $select->where($this->conditionManager->wrapBrackets($query));
        }

        return $select;
    }

 /**
  * @param \Magento\Framework\DB\Select $select
  * @param int|string $now
  * @return void
  */
 public function applyDate($select, $now)
 {
     $select->where('from_date is null or from_date <= ?', $now)->where('to_date is null or to_date >= ?', $now);
 }

 /**
  * {@inheritdoc}
  */
 public function build(ScoreBuilder $scoreBuilder, Select $select, RequestQueryInterface $query, $conditionType)
 {
     /** @var $query \Magento\Framework\Search\Request\Query\Match */
     $fieldList = [];
     foreach ($query->getMatches() as $match) {
         $fieldList[] = $match['field'];
     }
     $resolvedFieldList = $this->resolver->resolve($fieldList);
     $fieldIds = [];
     $columns = [];
     foreach ($resolvedFieldList as $field) {
         if ($field->getType() === FieldInterface::TYPE_FULLTEXT && $field->getAttributeId()) {
             $fieldIds[] = $field->getAttributeId();
         }
         $column = $field->getColumn();
         $columns[$column] = $column;
     }
     $exactMatchQuery = $this->getExactMatchQuery($columns, $query->getValue());
     $scoreQuery = $this->getScoreQuery($columns, $query->getValue());
     $scoreBuilder->addCondition(new \Zend_Db_Expr($scoreQuery), true);
     if ($query->getValue() != $this->queryHelper->singularize($query->getValue())) {
         $scoreQuery = $this->getScoreQuery($columns, $this->queryHelper->singularize($query->getValue()));
         $scoreBuilder->addCondition(new \Zend_Db_Expr($scoreQuery), true);
     }
     if ($fieldIds) {
         $select->where(sprintf('search_index.attribute_id IN (%s)', implode(',', $fieldIds)));
     }
     $select->having(new \Zend_Db_Expr($exactMatchQuery))->group('entity_id');
     return $select;
 }

 public function limitFlatActive(Select $select, $alias = 'main')
 {
     $select->join(['flat' => $this->getTable('entity_flat')], $alias . '.entity_id = flat.entity_id', []);
     $select->where('flat.scope_id = ?', $this->scope->getId($this->scopeCode));
     $select->where('flat.is_active = ?', 1);
     return $this;
 }