SqlDataProvider.php 4.37 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace yii\data;

use Yii;
use yii\base\InvalidConfigException;
use yii\base\Model;
use yii\db\Connection;

/**
 * SqlDataProvider implements a data provider based on a plain SQL statement.
 *
 * SqlDataProvider provides data in terms of arrays, each representing a row of query result.
 *
 * Like other data providers, SqlDataProvider also supports sorting and pagination.
 * It does so by modifying the given [[sql]] statement with "ORDER BY" and "LIMIT"
 * clauses. You may configure the [[sort]] and [[pagination]] properties to
 * customize sorting and pagination behaviors.
 *
 * SqlDataProvider may be used in the following way:
 *
 * ~~~
Qiang Xue committed
28 29 30 31
 * $count = Yii::$app->db->createCommand('
 *     SELECT COUNT(*) FROM tbl_user WHERE status=:status
 * ', [':status' => 1])->queryScalar();
 *
32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
 * $dataProvider = new SqlDataProvider([
 *     'sql' => 'SELECT * FROM tbl_user WHERE status=:status',
 *     'params' => [':status' => 1],
 *     'totalCount' => $count,
 *     'sort' => [
 *         'attributes' => [
 *             'age',
 *             'name' => [
 *                 'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC],
 *                 'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC],
 *                 'default' => SORT_DESC,
 *                 'label' => 'Name',
 *             ],
 *         ],
 *     ],
 *     'pagination' => [
 *         'pageSize' => 20,
 *     ],
 * ]);
Qiang Xue committed
51
 *
52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
 * // get the user records in the current page
 * $models = $dataProvider->getModels();
 * ~~~
 *
 * Note: if you want to use the pagination feature, you must configure the [[totalCount]] property
 * to be the total number of rows (without pagination). And if you want to use the sorting feature,
 * you must configure the [[sort]] property so that the provider knows which columns can be sorted.
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
 */
class SqlDataProvider extends BaseDataProvider
{
	/**
	 * @var Connection|string the DB connection object or the application component ID of the DB connection.
	 */
68
	public $db = 'db';
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 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 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159
	/**
	 * @var string the SQL statement to be used for fetching data rows.
	 */
	public $sql;
	/**
	 * @var array parameters (name=>value) to be bound to the SQL statement.
	 */
	public $params = [];
	/**
	 * @var string|callable the column that is used as the key of the data models.
	 * This can be either a column name, or a callable that returns the key value of a given data model.
	 *
	 * If this is not set, the keys of the [[models]] array will be used.
	 */
	public $key;


	/**
	 * Initializes the DB connection component.
	 * This method will initialize the [[db]] property to make sure it refers to a valid DB connection.
	 * @throws InvalidConfigException if [[db]] is invalid.
	 */
	public function init()
	{
		parent::init();
		if (is_string($this->db)) {
			$this->db = Yii::$app->getComponent($this->db);
		}
		if (!$this->db instanceof Connection) {
			throw new InvalidConfigException('The "db" property must be a valid DB Connection application component.');
		}
		if ($this->sql === null) {
			throw new InvalidConfigException('The "sql" property must be set.');
		}
	}

	/**
	 * @inheritdoc
	 */
	protected function prepareModels()
	{
		$sql = $this->sql;
		$qb = $this->db->getQueryBuilder();
		if (($sort = $this->getSort()) !== false) {
			$orderBy = $qb->buildOrderBy($sort->getOrders());
			if (!empty($orderBy)) {
				$orderBy = substr($orderBy, 9);
				if (preg_match('/\s+order\s+by\s+[\w\s,\.]+$/i', $sql)) {
					$sql .= ', ' . $orderBy;
				} else {
					$sql .= ' ORDER BY ' . $orderBy;
				}
			}
		}

		if (($pagination = $this->getPagination()) !== false) {
			$pagination->totalCount = $this->getTotalCount();
			$sql .= ' ' . $qb->buildLimit($pagination->getLimit(), $pagination->getOffset());
		}

		return $this->db->createCommand($sql, $this->params)->queryAll();
	}

	/**
	 * @inheritdoc
	 */
	protected function prepareKeys($models)
	{
		$keys = [];
		if ($this->key !== null) {
			foreach ($models as $model) {
				if (is_string($this->key)) {
					$keys[] = $model[$this->key];
				} else {
					$keys[] = call_user_func($this->key, $model);
				}
			}
			return $keys;
		} else {
			return array_keys($models);
		}
	}

	/**
	 * @inheritdoc
	 */
	protected function prepareTotalCount()
	{
		return 0;
	}
}