yii2

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;
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 <qiang.xue@gmail.com>
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
    public function init()
97
    {
98
        parent::init();
99
        $this->db = Instance::ensure($this->db, Connection::className());
100
        if ($this->sql === null) {
101
            throw new InvalidConfigException('The "sql" property must be set.');
102
        }
103
    }
104

105
    /**
106
     * {@inheritdoc}
107
     */
108
    protected function prepareModels()
109
    {
110
        $sort = $this->getSort();
111
        $pagination = $this->getPagination();
112
        if ($pagination === false && $sort === false) {
113
            return $this->db->createCommand($this->sql, $this->params)->queryAll();
114
        }
115

116
        $sql = $this->sql;
117
        $orders = [];
118
        $limit = $offset = null;
119

120
        if ($sort !== false) {
121
            $orders = $sort->getOrders();
122
            $pattern = '/\s+order\s+by\s+([\w\s,\."`\[\]]+)$/i';
123
            if (preg_match($pattern, $sql, $matches)) {
124
                array_unshift($orders, new Expression($matches[1]));
125
                $sql = preg_replace($pattern, '', $sql);
126
            }
127
        }
128

129
        if ($pagination !== false) {
130
            $pagination->totalCount = $this->getTotalCount();
131
            $limit = $pagination->getLimit();
132
            $offset = $pagination->getOffset();
133
        }
134

135
        $sql = $this->db->getQueryBuilder()->buildOrderByAndLimit($sql, $orders, $limit, $offset);
136

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

140
    /**
141
     * {@inheritdoc}
142
     */
143
    protected function prepareKeys($models)
144
    {
145
        $keys = [];
146
        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
        return array_keys($models);
159
    }
160

161
    /**
162
     * {@inheritdoc}
163
     */
164
    protected function prepareTotalCount()
165
    {
166
        return (new Query([
167
            'from' => ['sub' => "({$this->sql})"],
168
            'params' => $this->params,
169
        ]))->count('*', $this->db);
170
    }
171
}
172