An ActiveQuery can be a normal query or be used in a relational context.
ActiveQuery instances are usually created by [[ActiveRecord::find()]] and [[ActiveRecord::findBySql()]].
Relational queries are created by [[ActiveRecord::hasOne()]] and [[ActiveRecord::hasMany()]].
Normal Query
------------
ActiveQuery mainly provides the following methods to retrieve the query results:
- ActiveQuery::one: returns a single record populated with the first row of data.
- ActiveQuery::all: returns all records based on the query results.
- [[count()]]: returns the number of records.
- [[sum()]]: returns the sum over the specified column.
- [[average()]]: returns the average over the specified column.
- [[min()]]: returns the min over the specified column.
- [[max()]]: returns the max over the specified column.
- [[scalar()]]: returns the value of the first column in the first row of the query result.
- [[column()]]: returns the value of the first column in the query result.
- [[exists()]]: returns a value indicating whether the query result has data or not.
Because ActiveQuery extends from Query, one can use query methods, such as [[where()]],
[[orderBy()]] to customize the query options.
ActiveQuery also provides the following additional query options:
- [[with()]]: list of relations that this query should be performed with.
- ActiveQuery::joinWith: reuse a relation query definition to add a join to a query.
- [[indexBy()]]: the name of the column by which the query result should be indexed.
- [[asArray()]]: whether to return each record as an array.
These options can be configured using methods of the same name. For example:
php
$customers = Customer::find()->with('orders')->asArray()->all();
Relational query
----------------
In relational context ActiveQuery represents a relation between two Active Record classes.
Relational ActiveQuery instances are usually created by calling [[ActiveRecord::hasOne()]] and
[[ActiveRecord::hasMany()]]. An Active Record class declares a relation by defining
a getter method which calls one of the above methods and returns the created ActiveQuery object.
A relation is specified by [[link]] which represents the association between columns
of different tables; and the multiplicity of the relation is indicated by [[multiple]].
If a relation involves a junction table, it may be specified by [[via()]] or ActiveQuery::viaTable method.
These methods may only be called in a relational context. Same is true for [[inverseOf()]], which
marks a relation as inverse of another relation and ActiveQuery::onCondition which adds a condition that
is to be added to relational query join condition.
/** * @param ActiveQuery $query * @param string $attribute * @param bool $partialMath */ private function addCondition($query, $attribute, $partialMath = false) { if (isset($this->relationAttributes[$attribute])) { $attributeName = $this->relationAttributes[$attribute]; } else { $attributeName = call_user_func([$this->modelClassName, 'tableName']) . '.' . $attribute; } $value = $this->{$attribute}; if ($value === '') { return; } if ($partialMath) { $query->andWhere(['like', $attributeName, $value]); } else { $query->andWhere([$attributeName => $value]); } }
/** * @inheritdoc */ public function run() { $countQuery = clone $this->query; $pagination = new \yii\data\Pagination(['totalCount' => $countQuery->count(), 'pageSize' => $this->pageSize]); $this->query->offset($pagination->offset)->limit($pagination->limit); return $this->render("userListBox", ['title' => $this->title, 'users' => $this->query->all(), 'pagination' => $pagination]); }
/** * Use a distinct compare value for each column. Primary and foreign keys support multiple values. * @param \yii\db\ActiveQuery $query * @return \yii\db\ActiveQuery */ protected function addAttributesSearchConditions(\yii\db\ActiveQuery $query) { $tablePrefix = $this->getDb()->getSchema()->quoteSimpleTableName('t'); $conditions = ['and']; $formats = $this->attributeFormats(); $attributes = $this->attributes(); $relations = $this->relations(); $validAttributes = array_diff($attributes, array_keys($this->getErrors())); $attributeValues = $this->getAttributes($validAttributes); $formatter = Yii::$app->formatter; /** @var EnumCollection $enums */ $enums = $formatter instanceof Formatter ? $formatter->getEnums() : null; foreach ($validAttributes as $attribute) { $value = $attributeValues[$attribute]; if ($value === null || !isset($formats[$attribute]) || $enums !== null && !is_array($formats[$attribute]) && $enums->has($formats[$attribute])) { continue; } if (in_array($attribute, $relations)) { // only hasMany relations should be ever marked as valid attributes $conditions[] = $this->getRelationCondition($this->getRelation($attribute), $value); } else { $conditions[] = $this->getAttributeCondition($attribute, $value, $formats, $tablePrefix, $this->getDb()); } } // don't clear attributes to allow rendering filled search form //$this->setAttributes(array_fill_keys($attributes, null)); if ($conditions !== ['and']) { $query->andWhere($conditions); } return $query; }
protected function _run() { $key = $this->getCacheKey() . 'run'; $dependency = new TagDependency(['tags' => [$this->className() . (string) $this->namespace, (new CmsSite())->getTableCacheTag()]]); $result = \Yii::$app->cache->get($key); if ($result === false || $this->enabledRunCache == Cms::BOOL_N) { $this->activeQuery = CmsSite::find(); if ($this->active == Cms::BOOL_Y) { $this->activeQuery->active(); } else { if ($this->active == Cms::BOOL_N) { $this->activeQuery->active(false); } } if ($this->limit) { $this->activeQuery->limit($limit); } if ($this->orderBy) { $this->activeQuery->orderBy([$this->orderBy => (int) $this->order]); } $result = parent::_run(); \Yii::$app->cache->set($key, $result, (int) $this->runCacheDuration, $dependency); } return $result; }
/** * Configures the query as such, that you can filter by a model, its id or an array of both. It is also * possible to invert the query. This means all but the one(s) provided. * * @param \yii\db\ActiveQuery $query the query to modify * @param integer|integer[]|\yii\db\ActiveRecord|\yii\db\ActiveRecord[] $param the id(s) or the model(s). If * an array is provided, it can be a mix of both * @param string $attribute the attribute name to compare (defaults to `id`) * @param bool $invert if true t, the query will be inverted (NOT LIKE, NOT, ...) */ public function oneOrManyModelComparison(&$query, $param, $attribute = 'id', $invert = false) { //get data from array if (is_array($param)) { $data = []; foreach ($param as $p) { if ($p instanceof \yii\db\ActiveRecord) { $data[] = $p->{$attribute}; } else { $data[] = $p; } } $param = $data; } else { if ($param instanceof \yii\db\ActiveRecord) { $param = $param->{$attribute}; } } //modify query if (!$invert) { $query->andWhere([$attribute => $param]); } else { $query->andWhere(['not', [$attribute => $param]]); } }
public static function getActiveToken($token) { $activeQuery = new ActiveQuery(self::className()); $activeQuery->where('access_token = :token', [':token' => $token]); $activeQuery->andWhere('expires > now()'); $token = $activeQuery->one(); return $token; }
/** * @return Pagination */ public function getPaginator() { $paginator = new Pagination(['totalCount' => $this->countQuery->count()]); $paginator->pageParam = 'page'; $paginator->pageSizeParam = false; $paginator->setPageSize($this->limit); return $paginator; }
public function filterQuery(ActiveQuery $query) { if ($this->type) { $query->innerJoinWith('type'); $query->andWhere(['{{death_reason_type}}.[[key]]' => $this->type]); } return $query; }
/** * @param int $sort * @return $this */ public function sort($sort = SORT_DESC) { /** @var ISortableActiveRecord $model */ $model = new $this->owner->modelClass(); $behavior = $model->getSortBehavior(); $this->owner->orderBy([$behavior->attributeName => $sort]); return $this; }
public function getDataprovider() { $query = new ActiveQuery($this::className()); if ($this->airport_id) { $query->andWhere(['airport_id' => $this->airport_id]); } $query->andWhere(['isarrival' => $this->isarrival]); $query->orderBy($this->isarrival == 1 ? "timeto" : "timefrom"); return new ActiveDataProvider(['query' => $query]); }
/** * Creates a Sort object configuration using query default order. * @param \yii\db\ActiveQuery $query * @param array $attributes * @return array */ public function getSortConfig(\yii\db\ActiveQuery $query, array $attributes) { $defaults = $query instanceof ActiveQuery ? $query->getDefaultOrderColumns() : []; $sort = ['enableMultiSort' => true, 'attributes' => [], 'defaultOrder' => $defaults]; /** @var TableSchema $tableSchema */ $tableSchema = $this->getTableSchema(); foreach ($attributes as $attribute) { if ($tableSchema->getColumn($attribute) === null) { continue; } $sort['attributes'][$attribute] = ['asc' => array_merge([$attribute => SORT_ASC], $defaults), 'desc' => array_merge([$attribute => SORT_DESC], $defaults)]; } return $sort; }
/** * @inheritdoc */ public function run() { $result = []; $class = $this->relation->modelClass; $form = $this->form; $template = '<div class="template">' . $this->render($this->viewName, ['model' => new $class(), 'form' => $this->form, 'index' => 'myindex']) . '</div>'; $items = $this->items ?: $this->relation->all(); foreach ($items as $index => $model) { $result[] = $this->render($this->viewName, compact('index', 'model', 'form')); } $result[] = Html::a(\Yii::t('app', ' Add'), '#', ['class' => 'btn btn-success glyphicon glyphicon-plus', 'template' => $template, 'onclick' => ' $(this).before($(this).attr("template").replace(/myindex/g, "new-"+Date.now())); return false;']); return implode('', $result); }
public function filter(ActiveQuery $query, &$cacheKeyAppend) { $get = Yii::$app->request->post(); if (isset($get['changeValue']) && is_array($get['changeValue'])) { foreach ($get['changeValue'] as $propertyId => $isActive) { if ($isActive && isset($get[$this->minValueAttribute][$propertyId]) && isset($get[$this->maxValueAttribute][$propertyId]) && is_numeric($get[$this->minValueAttribute][$propertyId]) && is_numeric($get[$this->maxValueAttribute][$propertyId])) { $query->innerJoin('object_static_values as osvf' . $propertyId, 'product.id=osvf' . $propertyId . '.object_model_id'); $query->innerJoin('property_static_values as psvf' . $propertyId, 'psvf' . $propertyId . '.id=osvf' . $propertyId . '.property_static_value_id'); $query->andWhere('psvf' . $propertyId . '.value >= :minData ')->andWhere('psvf' . $propertyId . '.value <= :maxData ')->andWhere(['psvf' . $propertyId . '.property_id' => $propertyId])->addParams([':minData' => (int) $get[$this->minValueAttribute][$propertyId], ':maxData' => (int) $get[$this->maxValueAttribute][$propertyId]]); $cacheKeyAppend .= 'FilterRangeProperty[min:' . (int) $get[$this->minValueAttribute][$propertyId] . ':max' . (int) $get[$this->maxValueAttribute][$propertyId] . ']'; } } } return $query; }
/** * @inheritdoc */ public function populate($rows) { $event = new PopulateEvent($this, $rows); $this->trigger(static::EVENT_BEFORE_POPULATE, $event); $rows = $event->rows; return parent::populate($rows); }
/** * Конфигурирование объекта запроса поиска по элементам. * * @param \yii\db\ActiveQuery $activeQuery * @param null $modelClassName * @return $this */ public function buildElementsQuery(\yii\db\ActiveQuery $activeQuery) { $where = []; //Нужно учитывать связанные дополнительные данные if ($this->enabledElementProperties == Cms::BOOL_Y) { $activeQuery->joinWith('cmsContentElementProperties'); //Нужно учитывать настройки связанные дополнительные данных if ($this->enabledElementPropertiesSearchable == Cms::BOOL_Y) { $activeQuery->joinWith('cmsContentElementProperties.property'); $where[] = ['and', ['like', CmsContentElementProperty::tableName() . ".value", '%' . $this->searchQuery . '%', false], [CmsContentProperty::tableName() . ".searchable" => Cms::BOOL_Y]]; } else { $where[] = ['like', CmsContentElementProperty::tableName() . ".value", '%' . $this->searchQuery . '%', false]; } } //Поиск по основному набору полей if ($this->searchElementFields) { foreach ($this->searchElementFields as $fieldName) { $where[] = ['like', CmsContentElement::tableName() . "." . $fieldName, '%' . $this->searchQuery . '%', false]; } } if ($where) { $where = array_merge(['or'], $where); $activeQuery->andWhere($where); } //Отфильтровать только конкретный тип if ($this->searchElementContentIds) { $activeQuery->andWhere([CmsContentElement::tableName() . ".content_id" => (array) $this->searchElementContentIds]); } return $this; }
/** * @param $table * @return object * @throws InvalidConfigException */ public static function findx($table) { if (self::$table != $table) { self::$table = $table; } return Yii::createObject(ActiveQuery::className(), [get_called_class(), ['from' => [static::tableName()]]]); }
/** * Фильтрация по атрибутам */ protected function filterAttributes() { if ($this->_mainQuery) { if ($this->hasAttribute('id')) { $this->_mainQuery->andFilterWhere(['id' => $this->id]); } if ($this->hasAttribute('user_id')) { $this->_mainQuery->andFilterWhere(['user_id' => $this->user_id]); } if ($this->hasAttribute('type_key')) { $this->_mainQuery->andFilterWhere(['type_key' => $this->type_key]); } if ($this->hasAttribute('app_id')) { $this->_mainQuery->andFilterWhere(['app_id' => $this->app_id]); } if ($this->hasAttribute('model_name')) { $this->_mainQuery->andFilterWhere(['like', 'model_name', $this->model_name]); } if ($this->hasAttribute('model_name')) { $this->_mainQuery->andFilterWhere(['like', 'table_name', $this->table_name]); } if ($this->hasAttribute('created_at')) { $this->_mainQuery->andFilterWhere(['like', 'created_at', $this->created_at]); } } }
public function allOfProduct($productID, $db = null) { $this->where(['product_id' => $productID]); $this->indexBy('ebay_account_id'); $this->asArray(); return parent::all($db); }
public function prepare($builder) { if ($this->type !== null) { $this->andWhere(['type' => $this->type]); } return parent::prepare($builder); }
/** * @inheritdoc */ public function alias($alias) { $this->alias = null; $result = parent::alias($alias); $this->trigger(static::EVENT_ALIAS); return $result; }
/** * Adds a condition to search in relations using subquery. * @todo this should be called for each token, to group their conditions with OR and group token groups with AND * * @param \yii\db\ActiveQuery $query * @param array $tokens all search tokens extracted from term * @param array $relationAttributes array of string(relation name) => array( * 'model' => netis\crud\db\ActiveRecord, * 'searchModel' => netis\crud\db\ActiveSearchTrait, * 'attributes' => array * ) * @return array conditions to add to $query */ protected function processSearchRelated(\yii\db\ActiveQuery $query, array $tokens, array $relationAttributes) { $allConditions = ['or']; foreach ($relationAttributes as $relationName => $relation) { /** * @todo optimize this (check first, don't want to loose another battle with PostgreSQL query planner): * - for BELONGS_TO check fk against subquery * - for HAS_MANY and HAS_ONE check pk against subquery * - for MANY_MANY join only to pivot table and check its fk agains subquery */ $query->joinWith([$relationName => function ($query) use($relationName) { /** @var \yii\db\ActiveQuery $query */ /** @var \yii\db\ActiveRecord $class */ $class = $query->modelClass; return $query->select(false)->from([$relationName => $class::tableName()]); }]); $conditions = ['and']; /** @var ActiveSearchInterface $searchModel */ $searchModel = $relation['searchModel']; if (!$searchModel instanceof ActiveSearchInterface) { continue; } foreach ($tokens as $token) { $condition = $searchModel->processSearchToken($token, $relation['attributes'], $relationName); if ($condition !== null) { $conditions[] = $condition; } } if ($conditions !== ['and']) { $allConditions[] = $conditions; } } return $allConditions !== ['or'] ? $allConditions : null; }
/** * Apply possible answers order to query * @param ActiveQuery $query * @param $order * @return string */ public static function applyOrder(ActiveQuery $query, $order) { switch ($order) { case 'oldest': $query->orderBy('created_at DESC'); break; case 'active': $query->orderBy('created_at ASC'); break; case 'votes': default: $query->orderBy('votes DESC'); break; } return $order; }
/** * 拿到属于用户的所有,返回数组 * @param [type] $userID [description] * @param [type] $db [description] * @return [type] [description] */ public function allOfUser($userID, $db = null) { $this->where(['user_id' => $userID]); $this->orderBy('price ASC'); $this->asArray(); return parent::all($db); }
protected function fillModels() { $models = $this->dataProvider ? $this->dataProvider->getModels() : $this->query->all(); foreach ($models as $model) { $this->_models[] = new Model(['instance' => $model, 'standardModel' => $this->_standardModels[0]]); } }
/** * @inheritdoc */ public function rules() { return array_merge(parent::rules(), [[['db', 'ns', 'tableName', 'modelClass', 'baseClass', 'queryNs', 'queryClass', 'queryBaseClass'], 'filter', 'filter' => 'trim'], [['ns', 'queryNs'], 'filter', 'filter' => function ($value) { return trim($value, '\\'); }], [['db', 'ns', 'tableName', 'baseClass', 'queryNs', 'queryBaseClass'], 'required'], [['db', 'modelClass', 'queryClass'], 'match', 'pattern' => '/^\\w+$/', 'message' => 'Only word characters are allowed.'], [['ns', 'baseClass', 'queryNs', 'queryBaseClass'], 'match', 'pattern' => '/^[\\w\\\\]+$/', 'message' => 'Only word characters and backslashes are allowed.'], [['tableName'], 'match', 'pattern' => '/^(\\w+\\.)?([\\w\\*]+)$/', 'message' => 'Only word characters, and optionally an asterisk and/or a dot are allowed.'], [['db'], 'validateDb'], [['ns', 'queryNs'], 'validateNamespace'], [['tableName'], 'validateTableName'], [['modelClass'], 'validateModelClass', 'skipOnEmpty' => false], [['baseClass'], 'validateClass', 'params' => ['extends' => ActiveRecord::className()]], [['queryBaseClass'], 'validateClass', 'params' => ['extends' => ActiveQuery::className()]], [['generateRelations', 'generateLabelsFromComments', 'useTablePrefix', 'useSchemaName', 'generateQuery'], 'boolean'], [['enableI18N'], 'boolean'], [['messageCategory'], 'validateMessageCategory', 'skipOnEmpty' => false], [['imagesDomain', 'imagesPath'], 'filter', 'filter' => 'trim'], [['imagesPath'], 'filter', 'filter' => function ($value) { return trim($value, '/'); }], [['imagesDomain', 'imagesPath'], 'required'], [['addingI18NStrings'], 'boolean'], [['imagesDomain'], 'match', 'pattern' => '/^(?:[\\w](?:[\\w-]+[\\w])?\\.(?:{\\$domain})|(?:[\\w](?:[0-9\\w\\-\\.]+)?[\\w]\\.[\\w]+))|(?:@[\\w_-]+)$/', 'message' => 'No valid images domain.'], [['messagesPaths'], 'validateMessagesPaths']]); }
public function init() { parent::init(); /* @var $modelClass ActiveRecord */ $modelClass = $this->modelClass; $tableName = $modelClass::tableName(); $this->select(['m.id', 'm.text', 'm.created_at', 'm.is_new', 'm.sender_id'])->from(['m' => $tableName])->where(['or', ['sender_id' => $this->contactId, 'receiver_id' => $this->userId, 'is_deleted_by_receiver' => 0], ['sender_id' => $this->userId, 'receiver_id' => $this->contactId, 'is_deleted_by_sender' => 0]])->orderBy(['m.id' => SORT_DESC])->asArray(); }
public function prepare($builder) { if ($this->type !== null) { $this->andWhere(['competition_type' => $this->type]); Yii::trace($this->type, 'CompetitionQuery::prepare'); } return parent::prepare($builder); }
public function populate($rows) { $models = parent::populate($rows); if (!empty($this->link) && !empty($this->via) && $this->via instanceof self) { $this->populateJunctionAttributes($models); } return $models; }
/** * @inheritdoc * @return Parametro|array|null */ public function activeByNameDecoded($name, $db = null) { $ret = parent::where(['nombre' => $name])->andWhere('fecha_fin is null')->one(); if (!$ret) { return null; } return Json::decode($ret->valor); }
/** * @param ActiveQuery $query * @param $attribute * @param bool|false $partialMatch */ protected function addCondition(ActiveQuery $query, $attribute, $partialMatch = false) { if (($pos = strrpos($attribute, '.')) !== false) { $modelAttribute = substr($attribute, $pos + 1); } else { $modelAttribute = $attribute; } $value = $this->{$modelAttribute}; if (trim($value) === '') { return; } $attribute = "books.{$attribute}"; if ($partialMatch) { $query->andWhere(['like', $attribute, $value]); } else { $query->andWhere([$attribute => $value]); } }