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]);
}
}
