|
1
|
|
|
<?php |
|
2
|
|
|
/** |
|
3
|
|
|
* @link https://www.yiiframework.com/ |
|
4
|
|
|
* @copyright Copyright (c) 2008 Yii Software LLC |
|
5
|
|
|
* @license https://www.yiiframework.com/license/ |
|
6
|
|
|
*/ |
|
7
|
|
|
|
|
8
|
|
|
namespace yii\data; |
|
9
|
|
|
|
|
10
|
|
|
use yii\base\InvalidConfigException; |
|
11
|
|
|
use yii\caching\ArrayCache; |
|
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|null 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
|
|
|
} |
|
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 |
|
$limit = $pagination->getLimit(); |
|
131
|
9 |
|
$offset = $pagination->getOffset(); |
|
132
|
|
|
} |
|
133
|
|
|
|
|
134
|
9 |
|
$sql = $this->db->getQueryBuilder()->buildOrderByAndLimit($sql, $orders, $limit, $offset); |
|
135
|
|
|
|
|
136
|
9 |
|
return $this->db->createCommand($sql, $this->params)->queryAll(); |
|
137
|
|
|
} |
|
138
|
|
|
|
|
139
|
|
|
/** |
|
140
|
|
|
* {@inheritdoc} |
|
141
|
|
|
*/ |
|
142
|
9 |
|
protected function prepareKeys($models) |
|
143
|
|
|
{ |
|
144
|
9 |
|
$keys = []; |
|
145
|
9 |
|
if ($this->key !== null) { |
|
146
|
|
|
foreach ($models as $model) { |
|
147
|
|
|
if (is_string($this->key)) { |
|
148
|
|
|
$keys[] = $model[$this->key]; |
|
149
|
|
|
} else { |
|
150
|
|
|
$keys[] = call_user_func($this->key, $model); |
|
151
|
|
|
} |
|
152
|
|
|
} |
|
153
|
|
|
return $keys; |
|
154
|
|
|
} |
|
155
|
9 |
|
return array_keys($models); |
|
156
|
|
|
} |
|
157
|
|
|
|
|
158
|
|
|
/** |
|
159
|
|
|
* {@inheritdoc} |
|
160
|
|
|
*/ |
|
161
|
11 |
|
protected function prepareTotalCount() |
|
162
|
|
|
{ |
|
163
|
11 |
|
$query = new Query([ |
|
164
|
11 |
|
'from' => ['sub' => "({$this->sql})"], |
|
165
|
11 |
|
'params' => $this->params, |
|
166
|
11 |
|
]); |
|
167
|
|
|
|
|
168
|
11 |
|
return $this->getQueryCache()->getOrSet((string)$query, function () use ($query) { |
|
169
|
11 |
|
return (int) $query->count('*', $this->db); |
|
170
|
11 |
|
}); |
|
171
|
|
|
} |
|
172
|
|
|
|
|
173
|
|
|
private $_queryCache; |
|
174
|
|
|
|
|
175
|
|
|
/** |
|
176
|
|
|
* @return \yii\caching\CacheInterface |
|
177
|
|
|
*/ |
|
178
|
11 |
|
protected function getQueryCache() |
|
179
|
|
|
{ |
|
180
|
11 |
|
if ($this->_queryCache === null) { |
|
181
|
11 |
|
$this->_queryCache = new ArrayCache(); |
|
182
|
|
|
} |
|
183
|
11 |
|
return $this->_queryCache; |
|
184
|
|
|
} |
|
185
|
|
|
} |
|
186
|
|
|
|
yiisoft /
yii2
This function has been deprecated. The supplier of the function has supplied an explanatory message.
The explanatory message should give you some clue as to whether and when the function will be removed and what other function to use instead.