PHP Query::andWhere方法代码示例

 /**
  * {@inheritdoc}
  */
 public function apply($fromAlias, $fromIdentifier, $resourcePrefix, array $requesterIdentifiers, $mask, array $orX = [])
 {
     $this->query->join(['table' => $this->getAclSchema()->getPermissionsTableName(), 'alias' => 'acl_p', 'type' => 'LEFT', 'conditions' => $this->query->newExpr()->eq('acl_p.resource', $this->query->func()->concat([':acl_resource_prefix' => 'literal', $fromAlias . '.' . $fromIdentifier => 'literal']))]);
     $orX[] = $this->query->newExpr()->and_([$this->query->newExpr()->in('acl_p.requester', $requesterIdentifiers, 'string'), $this->query->newExpr(':acl_mask = (acl_p.mask & :acl_mask)')]);
     $this->query->andWhere($this->query->newExpr()->or_($orX));
     $this->query->bind(':acl_resource_prefix', $resourcePrefix);
     $this->query->bind(':acl_mask', $mask, 'integer');
     return $this->query;
 }

 /**
  * {@inheritDoc}
  */
 public function scope(Query $query, TokenInterface $token)
 {
     $column = $this->config('field');
     $value = $token->value();
     if (!$column || empty($value)) {
         return $query;
     }
     $tableAlias = $this->_table->alias();
     $range = $this->_parseRange($token->value());
     if ($range['lower'] !== $range['upper']) {
         $conjunction = $token->negated() ? 'AND NOT' : 'AND';
         $conditions = ["{$conjunction}" => ["{$tableAlias}.{$column} >=" => $range['lower'], "{$tableAlias}.{$column} <=" => $range['upper']]];
     } else {
         $cmp = $token->negated() ? '<=' : '>=';
         $conditions = ["{$tableAlias}.{$column} {$cmp}" => $range['lower']];
     }
     if ($token->where() === 'or') {
         $query->orWhere($conditions);
     } elseif ($token->where() === 'and') {
         $query->andWhere($conditions);
     } else {
         $query->where($conditions);
     }
     return $query;
 }

 /**
  * {@inheritDoc}
  */
 public function scope(Query $query, TokenInterface $token)
 {
     list($conjunction, $value) = $this->_prepareConjunction($token);
     $subQuery = TableRegistry::get('Clients.ClientsRecords')->find()->contain(['Client'])->select(['ClientsRecords.record_id'])->where(['OR' => ["Client.name {$conjunction}" => $value, "Client.lastname {$conjunction}" => $value]]);
     $conditions = ['SearchDatasets.entity_id IN' => $subQuery];
     if ($token->where() === 'or') {
         $query->orWhere($conditions);
     } elseif ($token->where() === 'and') {
         $query->andWhere($conditions);
     }
     return $query->where($conditions);
 }

 /**
  * Returns the rows that are either unlocker or locked by the provided user
  * This finder requires the `lockingUser` key in options
  *
  * @param Query $quert The Query to modify
  * @param array|ArrayObject $options The options containing the `lockingUser` key
  * @return Query
  */
 public function findUnlocked(Query $query, $options)
 {
     return $query->andWhere(function ($exp) use($options) {
         $timeCol = $this->_config['locked_time'];
         $entityClass = $this->_table->entityClass();
         $nullExp = clone $exp;
         $edge = new DateTimeImmutable('@' . (time() - $entityClass::getLockTimeout()));
         $or = $exp->or_([$nullExp->isNull($timeCol), $exp->lte($timeCol, $edge, 'datetime')]);
         if (!empty($options['lockingUser'])) {
             $or->eq($this->_config['locked_by'], $options['lockingUser']);
         }
         return $or;
     });
 }

 /**
  * Prepare CakePHP search
  *
  * @param Request $request DataTable request
  */
 protected function prepareSearch(Request $request)
 {
     $value = $request->getSearch()->getValue();
     if (!empty($value)) {
         $where = [];
         foreach ($request->getColumns() as $column) {
             if ($column->getSearchable() === true) {
                 $where[$column->getData() . ' LIKE'] = '%' . $value . '%';
             }
         }
         $this->query->andWhere(function (QueryExpression $exp) use($where) {
             return $exp->or_($where);
         });
     }
 }

 /**
  * {@inheritDoc}
  */
 public function scope(Query $query, TokenInterface $token)
 {
     $column = $this->config('field');
     $value = $token->value();
     if (!$column || empty($value)) {
         return $query;
     }
     list($conjunction, $value) = $this->_prepareConjunction($token);
     $tableAlias = $this->_table->alias();
     $conditions = ["{$tableAlias}.{$column} {$conjunction}" => $value];
     if ($token->where() === 'or') {
         $query->orWhere($conditions);
     } elseif ($token->where() === 'and') {
         $query->andWhere($conditions);
     } else {
         $query->where($conditions);
     }
     return $query;
 }

 /**
  * Handles the "term:" search operator. Which filters all entities matching
  * a given collection of terms.
  *
  *     term:cat,dog,bird,...,term-slug
  *
  * You can provide up to 10 terms as maximum.
  *
  * @param \Cake\Event\Event $event The event that was triggered
  * @param \Cake\ORM\Query $query The query being scoped
  * @param \Search\Token $token Operator token
  * @return \Cake\ORM\Query Scoped query
  */
 public function operatorTerm(Event $event, $query, $token)
 {
     $slugs = explode(',', $token->value());
     $slugs = array_slice($slugs, 0, 10);
     if (!empty($slugs)) {
         $IN = $token->negated() ? 'NOT IN' : 'IN';
         $table = $event->subject();
         $pk = $table->primaryKey();
         $tableAlias = $table->alias();
         $termsIds = TableRegistry::get('Taxonomy.Terms')->find()->select(['id'])->where(['Terms.slug IN' => $slugs])->all()->extract('id')->toArray();
         $termsIds = empty($termsIds) ? [0] : $termsIds;
         $subQuery = TableRegistry::get('Taxonomy.EntitiesTerms')->find()->select(['entity_id'])->where(['term_id IN' => $termsIds, 'table_alias' => $tableAlias]);
         if ($token->where() === 'or') {
             $query->orWhere(["{$tableAlias}.{$pk} {$IN}" => $subQuery]);
         } elseif ($token->where() === 'and') {
             $query->andWhere(["{$tableAlias}.{$pk} {$IN}" => $subQuery]);
         } else {
             $query->where(["{$tableAlias}.{$pk} {$IN}" => $subQuery]);
         }
     }
     return $query;
 }

 /**
  * Custom finder to get only the `trashed` rows.
  *
  * @param \Cake\ORM\Query $query Query.
  * @param array $options Options.
  * @return \Cake\ORM\Query
  */
 public function findOnlyTrashed(Query $query, array $options)
 {
     return $query->andWhere($query->newExpr()->isNotNull($this->getTrashField()));
 }

 /**
  * Handles "term" search operator.
  *
  *     term:term1-slug,term2-slug,...
  *
  * @param \Cake\ORM\Query $query The query object
  * @param \Search\Parser\TokenInterface $token Operator token
  * @return \Cake\ORM\Query
  */
 public function operatorterm(Query $query, TokenInterface $token)
 {
     $terms = explode(',', strtolower($token->value()));
     $conjunction = $token->negated() ? 'NOT IN' : 'IN';
     if (empty($terms)) {
         return $query;
     }
     $conditions = ["Contents.id {$conjunction}" => TableRegistry::get('Taxonomy.EntitiesTerms')->find()->select(['EntitiesTerms.entity_id'])->where(['EntitiesTerms.table_alias' => $this->alias()])->matching('Terms', function ($q) use($terms) {
         return $q->where(['Terms.slug IN' => $terms]);
     })];
     if (!empty($conditions)) {
         if ($token->where() === 'or') {
             $query->orWhere($conditions);
         } elseif ($token->where() === 'and') {
             $query->andWhere($conditions);
         } else {
             $query->where($conditions);
         }
     }
     return $query;
 }

 /**
  * Appends any conditions required to load the relevant set of records in the
  * target table query given a filter key and some filtering values.
  *
  * @param \Cake\ORM\Query $query Target table's query
  * @param string $key the fields that should be used for filtering
  * @param mixed $filter the value that should be used to match for $key
  * @return \Cake\ORM\Query
  */
 protected function _addFilteringCondition($query, $key, $filter)
 {
     if (is_array($key)) {
         $types = [];
         $defaults = $query->defaultTypes();
         foreach ($key as $k) {
             if (isset($defaults[$k])) {
                 $types[] = $defaults[$k];
             }
         }
         return $query->andWhere(new TupleComparison($key, $filter, $types, 'IN'));
     }
     return $query->andWhere([$key . ' IN' => $filter]);
 }

 /**
  * Similar to "_scopeWords" but using MySQL's fulltext indexes.
  *
  * @param \Cake\ORM\Query $query The query to scope
  * @param \Search\TokenInterface $token Token describing a words sequence. e.g `this is a phrase`
  * @return \Cake\ORM\Query Scoped query
  */
 protected function _scopeWordsInFulltext(Query $query, TokenInterface $token)
 {
     $value = str_replace(['*', '!'], ['*', '*'], $token->value());
     $value = mb_strpos($value, '+') === 0 ? mb_substr($value, 1) : $value;
     if (empty($value) || in_array($value, $this->_stopWords())) {
         return $query;
     }
     $not = $token->negated() ? 'NOT' : '';
     $value = str_replace("'", '"', $value);
     $conditions = ["{$not} MATCH(SearchDatasets.words) AGAINST('{$value}' IN BOOLEAN MODE) > 0"];
     if ($token->where() === 'or') {
         $query->orWhere($conditions);
     } elseif ($token->where() === 'and') {
         $query->andWhere($conditions);
     } else {
         $query->where($conditions);
     }
     return $query;
 }