ActiveQuery.php 13.4 KB
Newer Older
Qiang Xue committed
1 2 3 4
<?php
/**
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @link http://www.yiiframework.com/
Qiang Xue committed
5
 * @copyright Copyright (c) 2008 Yii Software LLC
Qiang Xue committed
6 7 8
 * @license http://www.yiiframework.com/license/
 */

Qiang Xue committed
9
namespace yii\db;
Qiang Xue committed
10

Qiang Xue committed
11 12 13
/**
 * ActiveQuery represents a DB query associated with an Active Record class.
 *
14
 * ActiveQuery instances are usually created by [[ActiveRecord::find()]] and [[ActiveRecord::findBySql()]].
Qiang Xue committed
15 16 17 18 19
 *
 * ActiveQuery mainly provides the following methods to retrieve the query results:
 *
 * - [[one()]]: returns a single record populated with the first row of data.
 * - [[all()]]: returns all records based on the query results.
20 21 22 23 24 25
 * - [[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.
26
 * - [[column()]]: returns the value of the first column in the query result.
Qiang Xue committed
27 28 29 30 31 32 33
 * - [[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:
 *
Qiang Xue committed
34 35 36
 * - [[with()]]: list of relations that this query should be performed with.
 * - [[indexBy()]]: the name of the column by which the query result should be indexed.
 * - [[asArray()]]: whether to return each record as an array.
Qiang Xue committed
37 38 39 40 41 42 43 44
 *
 * These options can be configured using methods of the same name. For example:
 *
 * ~~~
 * $customers = Customer::find()->with('orders')->asArray()->all();
 * ~~~
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
45
 * @author Carsten Brandt <mail@cebe.cc>
Qiang Xue committed
46 47
 * @since 2.0
 */
48
class ActiveQuery extends Query implements ActiveQueryInterface
Qiang Xue committed
49
{
50
	use ActiveQueryTrait;
51

Qiang Xue committed
52 53 54 55 56 57 58 59 60
	/**
	 * @var string the SQL statement to be executed for retrieving AR records.
	 * This is set by [[ActiveRecord::findBySql()]].
	 */
	public $sql;


	/**
	 * Executes query and returns all results as an array.
61 62
	 * @param Connection $db the DB connection used to create the DB command.
	 * If null, the DB connection returned by [[modelClass]] will be used.
63
	 * @return array|ActiveRecord[] the query results. If the query results in nothing, an empty array will be returned.
Qiang Xue committed
64
	 */
65
	public function all($db = null)
Qiang Xue committed
66
	{
67
		$command = $this->createCommand($db);
Qiang Xue committed
68
		$rows = $command->queryAll();
69
		if (!empty($rows)) {
Carsten Brandt committed
70
			$models = $this->createModels($rows);
71 72 73
			if (!empty($this->join) && $this->indexBy === null) {
				$models = $this->removeDuplicatedModels($models);
			}
74
			if (!empty($this->with)) {
75
				$this->findWith($this->with, $models);
76
			}
77 78 79 80 81
			if (!$this->asArray) {
				foreach($models as $model) {
					$model->afterFind();
				}
			}
82 83
			return $models;
		} else {
Alexander Makarov committed
84
			return [];
Qiang Xue committed
85
		}
Qiang Xue committed
86 87
	}

88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
	/**
	 * Removes duplicated models by checking their primary key values.
	 * This method is mainly called when a join query is performed, which may cause duplicated rows being returned.
	 * @param array $models the models to be checked
	 * @return array the distinctive models
	 */
	private function removeDuplicatedModels($models)
	{
		$hash = [];
		/** @var ActiveRecord $class */
		$class = $this->modelClass;
		$pks = $class::primaryKey();

		if (count($pks) > 1) {
			foreach ($models as $i => $model) {
				$key = [];
				foreach ($pks as $pk) {
					$key[] = $model[$pk];
				}
				$key = serialize($key);
				if (isset($hash[$key])) {
					unset($models[$i]);
				} else {
					$hash[$key] = true;
				}
			}
		} else {
			$pk = reset($pks);
			foreach ($models as $i => $model) {
				$key = $model[$pk];
				if (isset($hash[$key])) {
					unset($models[$i]);
				} else {
					$hash[$key] = true;
				}
			}
		}

		return array_values($models);
	}

Qiang Xue committed
129 130
	/**
	 * Executes query and returns a single row of result.
131 132
	 * @param Connection $db the DB connection used to create the DB command.
	 * If null, the DB connection returned by [[modelClass]] will be used.
Qiang Xue committed
133 134
	 * @return ActiveRecord|array|null a single row of query result. Depending on the setting of [[asArray]],
	 * the query result may be either an array or an ActiveRecord object. Null will be returned
Qiang Xue committed
135 136
	 * if the query results in nothing.
	 */
137
	public function one($db = null)
Qiang Xue committed
138
	{
139
		$command = $this->createCommand($db);
140
		$row = $command->queryOne();
141 142 143 144
		if ($row !== false) {
			if ($this->asArray) {
				$model = $row;
			} else {
slavcodev committed
145
				/** @var ActiveRecord $class */
146
				$class = $this->modelClass;
Carsten Brandt committed
147
				$model = $class::create($row);
148
			}
Qiang Xue committed
149
			if (!empty($this->with)) {
Alexander Makarov committed
150
				$models = [$model];
151
				$this->findWith($this->with, $models);
Qiang Xue committed
152
				$model = $models[0];
Qiang Xue committed
153
			}
154 155 156
			if (!$this->asArray) {
				$model->afterFind();
			}
Qiang Xue committed
157
			return $model;
158
		} else {
159
			return null;
Qiang Xue committed
160
		}
Qiang Xue committed
161 162 163
	}

	/**
Qiang Xue committed
164
	 * Creates a DB command that can be used to execute this query.
Qiang Xue committed
165 166
	 * @param Connection $db the DB connection used to create the DB command.
	 * If null, the DB connection returned by [[modelClass]] will be used.
Qiang Xue committed
167
	 * @return Command the created DB command instance.
Qiang Xue committed
168
	 */
Qiang Xue committed
169
	public function createCommand($db = null)
Qiang Xue committed
170
	{
slavcodev committed
171
		/** @var ActiveRecord $modelClass */
Qiang Xue committed
172
		$modelClass = $this->modelClass;
Qiang Xue committed
173
		if ($db === null) {
Qiang Xue committed
174
			$db = $modelClass::getDb();
Qiang Xue committed
175
		}
176

Qiang Xue committed
177
		if ($this->sql === null) {
Carsten Brandt committed
178 179 180
			$select = $this->select;
			$from = $this->from;

Qiang Xue committed
181 182
			if ($this->from === null) {
				$tableName = $modelClass::tableName();
Qiang Xue committed
183
				if ($this->select === null && !empty($this->join)) {
Alexander Makarov committed
184
					$this->select = ["$tableName.*"];
Qiang Xue committed
185
				}
Alexander Makarov committed
186
				$this->from = [$tableName];
Qiang Xue committed
187
			}
Carsten Brandt committed
188 189 190 191 192 193 194
			list ($sql, $params) = $db->getQueryBuilder()->build($this);

			$this->select = $select;
			$this->from = $from;
		} else {
			$sql = $this->sql;
			$params = $this->params;
Qiang Xue committed
195
		}
Carsten Brandt committed
196
		return $db->createCommand($sql, $params);
Qiang Xue committed
197
	}
198

199 200 201 202 203 204 205 206 207 208 209 210
	/**
	 * Joins with the specified relations.
	 *
	 * This method allows you to reuse existing relation definitions to perform JOIN queries.
	 * Based on the definition of the specified relation(s), the method will append one or multiple
	 * JOIN statements to the current query.
	 *
	 * If the `$eagerLoading` parameter is true, the method will also eager loading the specified relations,
	 * which is equivalent to calling [[with()]] using the specified relations.
	 *
	 * Note that because a JOIN query will be performed, you are responsible to disambiguate column names.
	 *
211 212
	 * This method differs from [[with()]] in that it will build up and execute a JOIN SQL statement
	 * for the primary table. And when `$eagerLoading` is true, it will call [[with()]] in addition with the specified relations.
213 214 215 216 217 218 219 220 221
	 *
	 * @param array $with the relations to be joined. Each array element represents a single relation.
	 * The array keys are relation names, and the array values are the corresponding anonymous functions that
	 * can be used to modify the relation queries on-the-fly. If a relation query does not need modification,
	 * you may use the relation name as the array value. Sub-relations can also be specified (see [[with()]]).
	 * For example,
	 *
	 * ```php
	 * // find all orders that contain books, and eager loading "books"
222 223
	 * Order::find()->joinWith('books', true, 'INNER JOIN')->all();
	 * // find all orders, eager loading "books", and sort the orders and books by the book names.
224 225 226 227 228 229 230
	 * Order::find()->joinWith([
	 *     'books' => function ($query) {
	 *         $query->orderBy('tbl_item.name');
	 *     }
	 * ])->all();
	 * ```
	 *
Qiang Xue committed
231 232 233 234 235 236 237
	 * @param boolean|array $eagerLoading whether to eager load the relations specified in `$with`.
	 * When this is a boolean, it applies to all relations specified in `$with`. Use an array
	 * to explicitly list which relations in `$with` need to be eagerly loaded.
	 * @param string|array $joinType the join type of the relations specified in `$with`.
	 * When this is a string, it applies to all relations specified in `$with`. Use an array
	 * in the format of `relationName => joinType` to specify different join types for different relations.
	 * @return static the query object itself
238
	 */
239
	public function joinWith($with, $eagerLoading = true, $joinType = 'LEFT JOIN')
240 241 242 243 244 245 246 247 248 249 250 251 252 253
	{
		$with = (array)$with;
		$this->joinWithRelations(new $this->modelClass, $with, $joinType);

		if (is_array($eagerLoading)) {
			foreach ($with as $name => $callback) {
				if (is_integer($name)) {
					if (!in_array($callback, $eagerLoading, true)) {
						unset($with[$name]);
					}
				} elseif (!in_array($name, $eagerLoading, true)) {
					unset($with[$name]);
				}
			}
Qiang Xue committed
254 255
		} elseif (!$eagerLoading) {
			$with = [];
256
		}
Qiang Xue committed
257

Qiang Xue committed
258
		return $this->with($with);
259 260
	}

261 262 263 264 265 266 267 268 269 270 271 272 273 274
	/**
	 * Inner joins with the specified relations.
	 * This is a shortcut method to [[joinWith()]] with the join type set as "INNER JOIN".
	 * Please refer to [[joinWith()]] for detailed usage of this method.
	 * @param array $with the relations to be joined with
	 * @param boolean|array $eagerLoading whether to eager loading the relations
	 * @return static the query object itself
	 * @see joinWith()
	 */
	public function innerJoinWith($with, $eagerLoading = true)
	{
		return $this->joinWith($with, $eagerLoading, 'INNER JOIN');
	}

275
	/**
276 277 278 279
	 * Modifies the current query by adding join fragments based on the given relations.
	 * @param ActiveRecord $model the primary model
	 * @param array $with the relations to be joined
	 * @param string|array $joinType the join type
280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320
	 */
	private function joinWithRelations($model, $with, $joinType)
	{
		$relations = [];

		foreach ($with as $name => $callback) {
			if (is_integer($name)) {
				$name = $callback;
				$callback = null;
			}

			$primaryModel = $model;
			$parent = $this;
			$prefix = '';
			while (($pos = strpos($name, '.')) !== false) {
				$childName = substr($name, $pos + 1);
				$name = substr($name, 0, $pos);
				$fullName = $prefix === '' ? $name : "$prefix.$name";
				if (!isset($relations[$fullName])) {
					$relations[$fullName] = $relation = $primaryModel->getRelation($name);
					$this->joinWithRelation($parent, $relation, $this->getJoinType($joinType, $fullName));
				} else {
					$relation = $relations[$fullName];
				}
				$primaryModel = new $relation->modelClass;
				$parent = $relation;
				$prefix = $fullName;
				$name = $childName;
			}

			$fullName = $prefix === '' ? $name : "$prefix.$name";
			if (!isset($relations[$fullName])) {
				$relations[$fullName] = $relation = $primaryModel->getRelation($name);
				if ($callback !== null) {
					call_user_func($callback, $relation);
				}
				$this->joinWithRelation($parent, $relation, $this->getJoinType($joinType, $fullName));
			}
		}
	}

321 322 323 324 325 326
	/**
	 * Returns the join type based on the given join type parameter and the relation name.
	 * @param string|array $joinType the given join type(s)
	 * @param string $name relation name
	 * @return string the real join type
	 */
327 328 329 330 331 332 333 334 335 336
	private function getJoinType($joinType, $name)
	{
		if (is_array($joinType) && isset($joinType[$name])) {
			return $joinType[$name];
		} else {
			return is_string($joinType) ? $joinType : 'INNER JOIN';
		}
	}

	/**
337
	 * Returns the table name and the table alias for [[modelClass]].
338
	 * @param ActiveQuery $query
339
	 * @return array the table name and the table alias.
340
	 */
341
	private function getQueryTableName($query)
342 343 344 345
	{
		if (empty($query->from)) {
			/** @var ActiveRecord $modelClass */
			$modelClass = $query->modelClass;
346
			$tableName = $modelClass::tableName();
347
		} else {
348
			$tableName = reset($query->from);
349
		}
350 351

		if (preg_match('/^(.*?)\s+({{\w+}}|\w+)$/', $tableName, $matches)) {
352 353 354
			$alias = $matches[2];
		} else {
			$alias = $tableName;
355
		}
356 357

		return [$tableName, $alias];
358 359 360
	}

	/**
361 362
	 * Joins a parent query with a child query.
	 * The current query object will be modified accordingly.
363 364 365 366 367 368
	 * @param ActiveQuery $parent
	 * @param ActiveRelation $child
	 * @param string $joinType
	 */
	private function joinWithRelation($parent, $child, $joinType)
	{
369 370 371 372 373 374 375 376 377 378 379 380 381 382
		$via = $child->via;
		$child->via = null;
		if ($via instanceof ActiveRelation) {
			// via table
			$this->joinWithRelation($parent, $via, $joinType);
			$this->joinWithRelation($via, $child, $joinType);
			return;
		} elseif (is_array($via)) {
			// via relation
			$this->joinWithRelation($parent, $via[1], $joinType);
			$this->joinWithRelation($via[1], $child, $joinType);
			return;
		}

383 384
		list ($parentTable, $parentAlias) = $this->getQueryTableName($parent);
		list ($childTable, $childAlias) = $this->getQueryTableName($child);
385

386
		if (!empty($child->link)) {
387 388 389 390 391 392 393 394

			if (strpos($parentAlias, '{{') === false) {
				$parentAlias = '{{' . $parentAlias . '}}';
			}
			if (strpos($childAlias, '{{') === false) {
				$childAlias = '{{' . $childAlias . '}}';
			}

395 396
			$on = [];
			foreach ($child->link as $childColumn => $parentColumn) {
397
				$on[] = "$parentAlias.[[$parentColumn]] = $childAlias.[[$childColumn]]";
398 399
			}
			$on = implode(' AND ', $on);
400 401 402
			if (!empty($child->on)) {
				$on = ['and', $on, $child->on];
			}
403
		} else {
404
			$on = $child->on;
405 406
		}
		$this->join($joinType, $childTable, $on);
407 408


409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434
		if (!empty($child->where)) {
			$this->andWhere($child->where);
		}
		if (!empty($child->having)) {
			$this->andHaving($child->having);
		}
		if (!empty($child->orderBy)) {
			$this->addOrderBy($child->orderBy);
		}
		if (!empty($child->groupBy)) {
			$this->addGroupBy($child->groupBy);
		}
		if (!empty($child->params)) {
			$this->addParams($child->params);
		}
		if (!empty($child->join)) {
			foreach ($child->join as $join) {
				$this->join[] = $join;
			}
		}
		if (!empty($child->union)) {
			foreach ($child->union as $union) {
				$this->union[] = $union;
			}
		}
	}
Qiang Xue committed
435
}