SqlDataProvider.php 4.88 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12
<?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\db\Connection;
13
use yii\db\Expression;
14
use yii\di\Instance;
15 16 17 18 19 20 21 22 23 24 25 26 27 28

/**
 * 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
29
 * $count = Yii::$app->db->createCommand('
30
 *     SELECT COUNT(*) FROM user WHERE status=:status
Qiang Xue committed
31 32
 * ', [':status' => 1])->queryScalar();
 *
33
 * $dataProvider = new SqlDataProvider([
34
 *     'sql' => 'SELECT * FROM user WHERE status=:status',
35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
 *     '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
52
 *
53 54 55 56 57 58 59 60 61 62 63 64 65
 * // 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
{
66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
    /**
     * @var Connection|string the DB connection object or the application component ID of the DB connection.
     */
    public $db = 'db';
    /**
     * @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;
85

86

87 88 89 90 91 92 93 94
    /**
     * 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();
95
        $this->db = Instance::ensure($this->db, Connection::className());
96 97 98 99
        if ($this->sql === null) {
            throw new InvalidConfigException('The "sql" property must be set.');
        }
    }
100

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

112
        $sql = $this->sql;
113 114 115 116 117 118 119 120 121
        $orders = [];
        $limit = $offset = null;

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

125
        if ($pagination !== false) {
126
            $pagination->totalCount = $this->getTotalCount();
127 128
            $limit = $pagination->getLimit();
            $offset = $pagination->getOffset();
129
        }
130

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

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

136 137 138 139 140 141 142 143 144 145 146 147 148 149
    /**
     * @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);
                }
            }
150

151 152 153 154 155
            return $keys;
        } else {
            return array_keys($models);
        }
    }
156

157 158 159 160 161 162 163
    /**
     * @inheritdoc
     */
    protected function prepareTotalCount()
    {
        return 0;
    }
164
}