1 | <?php |
||
2 | /** |
||
3 | * @link http://www.yiiframework.com/ |
||
4 | * @copyright Copyright (c) 2008 Yii Software LLC |
||
5 | * @license http://www.yiiframework.com/license/ |
||
6 | */ |
||
7 | |||
8 | namespace yii\data; |
||
9 | |||
10 | use Yii; |
||
11 | use yii\base\InvalidConfigException; |
||
12 | use yii\db\Connection; |
||
13 | use yii\db\Expression; |
||
14 | use yii\db\Query; |
||
15 | use yii\di\Instance; |
||
16 | |||
17 | /** |
||
18 | * SqlDataProvider implements a data provider based on a plain SQL statement. |
||
19 | * |
||
20 | * SqlDataProvider provides data in terms of arrays, each representing a row of query result. |
||
21 | * |
||
22 | * Like other data providers, SqlDataProvider also supports sorting and pagination. |
||
23 | * It does so by modifying the given [[sql]] statement with "ORDER BY" and "LIMIT" |
||
24 | * clauses. You may configure the [[sort]] and [[pagination]] properties to |
||
25 | * customize sorting and pagination behaviors. |
||
26 | * |
||
27 | * SqlDataProvider may be used in the following way: |
||
28 | * |
||
29 | * ```php |
||
30 | * $count = Yii::$app->db->createCommand(' |
||
31 | * SELECT COUNT(*) FROM user WHERE status=:status |
||
32 | * ', [':status' => 1])->queryScalar(); |
||
33 | * |
||
34 | * $dataProvider = new SqlDataProvider([ |
||
35 | * 'sql' => 'SELECT * FROM user WHERE status=:status', |
||
36 | * 'params' => [':status' => 1], |
||
37 | * 'totalCount' => $count, |
||
38 | * 'sort' => [ |
||
39 | * 'attributes' => [ |
||
40 | * 'age', |
||
41 | * 'name' => [ |
||
42 | * 'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC], |
||
43 | * 'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC], |
||
44 | * 'default' => SORT_DESC, |
||
45 | * 'label' => 'Name', |
||
46 | * ], |
||
47 | * ], |
||
48 | * ], |
||
49 | * 'pagination' => [ |
||
50 | * 'pageSize' => 20, |
||
51 | * ], |
||
52 | * ]); |
||
53 | * |
||
54 | * // get the user records in the current page |
||
55 | * $models = $dataProvider->getModels(); |
||
56 | * ``` |
||
57 | * |
||
58 | * Note: if you want to use the pagination feature, you must configure the [[totalCount]] property |
||
59 | * to be the total number of rows (without pagination). And if you want to use the sorting feature, |
||
60 | * you must configure the [[sort]] property so that the provider knows which columns can be sorted. |
||
61 | * |
||
62 | * For more details and usage information on SqlDataProvider, see the [guide article on data providers](guide:output-data-providers). |
||
63 | * |
||
64 | * @author Qiang Xue <[email protected]> |
||
65 | * @since 2.0 |
||
66 | */ |
||
67 | class SqlDataProvider extends BaseDataProvider |
||
68 | { |
||
69 | /** |
||
70 | * @var Connection|array|string the DB connection object or the application component ID of the DB connection. |
||
71 | * Starting from version 2.0.2, this can also be a configuration array for creating the object. |
||
72 | */ |
||
73 | public $db = 'db'; |
||
74 | /** |
||
75 | * @var string the SQL statement to be used for fetching data rows. |
||
76 | */ |
||
77 | public $sql; |
||
78 | /** |
||
79 | * @var array parameters (name=>value) to be bound to the SQL statement. |
||
80 | */ |
||
81 | public $params = []; |
||
82 | /** |
||
83 | * @var string|callable the column that is used as the key of the data models. |
||
84 | * This can be either a column name, or a callable that returns the key value of a given data model. |
||
85 | * |
||
86 | * If this is not set, the keys of the [[models]] array will be used. |
||
87 | */ |
||
88 | public $key; |
||
89 | |||
90 | |||
91 | /** |
||
92 | * Initializes the DB connection component. |
||
93 | * This method will initialize the [[db]] property to make sure it refers to a valid DB connection. |
||
94 | * @throws InvalidConfigException if [[db]] is invalid. |
||
95 | */ |
||
96 | 11 | public function init() |
|
97 | { |
||
98 | 11 | parent::init(); |
|
99 | 11 | $this->db = Instance::ensure($this->db, Connection::className()); |
|
100 | 11 | if ($this->sql === null) { |
|
101 | throw new InvalidConfigException('The "sql" property must be set.'); |
||
102 | } |
||
103 | 11 | } |
|
104 | |||
105 | /** |
||
106 | * {@inheritdoc} |
||
107 | */ |
||
108 | 9 | protected function prepareModels() |
|
109 | { |
||
110 | 9 | $sort = $this->getSort(); |
|
111 | 9 | $pagination = $this->getPagination(); |
|
112 | 9 | if ($pagination === false && $sort === false) { |
|
113 | return $this->db->createCommand($this->sql, $this->params)->queryAll(); |
||
114 | } |
||
115 | |||
116 | 9 | $sql = $this->sql; |
|
117 | 9 | $orders = []; |
|
118 | 9 | $limit = $offset = null; |
|
119 | |||
120 | 9 | if ($sort !== false) { |
|
121 | 9 | $orders = $sort->getOrders(); |
|
122 | 9 | $pattern = '/\s+order\s+by\s+([\w\s,\."`\[\]]+)$/i'; |
|
123 | 9 | if (preg_match($pattern, $sql, $matches)) { |
|
124 | 8 | array_unshift($orders, new Expression($matches[1])); |
|
125 | 8 | $sql = preg_replace($pattern, '', $sql); |
|
126 | } |
||
127 | } |
||
128 | |||
129 | 9 | if ($pagination !== false) { |
|
130 | 9 | $pagination->totalCount = $this->getTotalCount(); |
|
131 | 9 | $limit = $pagination->getLimit(); |
|
132 | 9 | $offset = $pagination->getOffset(); |
|
133 | } |
||
134 | |||
135 | 9 | $sql = $this->db->getQueryBuilder()->buildOrderByAndLimit($sql, $orders, $limit, $offset); |
|
136 | |||
137 | 9 | return $this->db->createCommand($sql, $this->params)->queryAll(); |
|
138 | } |
||
139 | |||
140 | /** |
||
141 | * {@inheritdoc} |
||
142 | */ |
||
143 | 9 | protected function prepareKeys($models) |
|
144 | { |
||
145 | 9 | $keys = []; |
|
146 | 9 | if ($this->key !== null) { |
|
147 | foreach ($models as $model) { |
||
148 | if (is_string($this->key)) { |
||
149 | $keys[] = $model[$this->key]; |
||
150 | } else { |
||
151 | $keys[] = call_user_func($this->key, $model); |
||
152 | } |
||
153 | } |
||
154 | |||
155 | return $keys; |
||
156 | } |
||
157 | |||
158 | 9 | return array_keys($models); |
|
159 | } |
||
160 | |||
161 | /** |
||
162 | * {@inheritdoc} |
||
163 | */ |
||
164 | 11 | protected function prepareTotalCount() |
|
165 | { |
||
166 | 11 | return (new Query([ |
|
0 ignored issues
–
show
Bug
Best Practice
introduced
by
Loading history...
|
|||
167 | 11 | 'from' => ['sub' => "({$this->sql})"], |
|
168 | 11 | 'params' => $this->params, |
|
169 | 11 | ]))->count('*', $this->db); |
|
170 | } |
||
171 | } |
||
172 |