CSqlDataProvider.php 4.18 KB
Newer Older
JULIO JARAMILLO's avatar
JULIO JARAMILLO committed
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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 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
<?php
/**
 * CSqlDataProvider implements a data provider based on a plain SQL statement.
 *
 * CSqlDataProvider provides data in terms of arrays, each representing a row of query result.
 *
 * Like other data providers, CSqlDataProvider also supports sorting and pagination.
 * It does so by modifying the given {@link sql} statement with "ORDER BY" and "LIMIT"
 * clauses. You may configure the {@link sort} and {@link pagination} properties to
 * customize sorting and pagination behaviors.
 *
 * CSqlDataProvider may be used in the following way:
 * <pre>
 * $count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM tbl_user')->queryScalar();
 * $sql='SELECT * FROM tbl_user';
 * $dataProvider=new CSqlDataProvider($sql, array(
 *     'totalItemCount'=>$count,
 *     'sort'=>array(
 *         'attributes'=>array(
 *              'id', 'username', 'email',
 *         ),
 *     ),
 *     'pagination'=>array(
 *         'pageSize'=>10,
 *     ),
 * ));
 * // $dataProvider->getData() will return a list of arrays.
 * </pre>
 *
 * Note: if you want to use the pagination feature, you must configure the {@link totalItemCount} property
 * to be the total number of rows (without pagination). And if you want to use the sorting feature,
 * you must configure {@link sort} property so that the provider knows which columns can be sorted.
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @package system.web
 * @since 1.1.4
 */
class CSqlDataProvider extends CDataProvider
{
	/**
	 * @var CDbConnection the database connection to be used in the queries.
	 * Defaults to null, meaning using Yii::app()->db.
	 */
	public $db;
	/**
	 * @var string|CDbCommand the SQL statement to be used for fetching data rows.
	 * Since version 1.1.13 this can also be an instance of {@link CDbCommand}.
	 */
	public $sql;
	/**
	 * @var array parameters (name=>value) to be bound to the SQL statement.
	 */
	public $params=array();
	/**
	 * @var string the name of key field. Defaults to 'id'.
	 */
	public $keyField='id';

	/**
	 * Constructor.
	 * @param string|CDbCommand $sql the SQL statement to be used for fetching data rows. Since version 1.1.13 this can also be an instance of {@link CDbCommand}.
	 * @param array $config configuration (name=>value) to be applied as the initial property values of this class.
	 */
	public function __construct($sql,$config=array())
	{
		$this->sql=$sql;
		foreach($config as $key=>$value)
			$this->$key=$value;
	}

	/**
	 * Fetches the data from the persistent data storage.
	 * @return array list of data items
	 */
	protected function fetchData()
	{
		if(!($this->sql instanceof CDbCommand))
		{
			$db=$this->db===null ? Yii::app()->db : $this->db;
			$command=$db->createCommand($this->sql);
		}
		else
			$command=clone $this->sql;

		if(($sort=$this->getSort())!==false)
		{
			$order=$sort->getOrderBy();
			if(!empty($order))
			{
				if(preg_match('/\s+order\s+by\s+[\w\s,\.]+$/i',$command->text))
					$command->text.=', '.$order;
				else
					$command->text.=' ORDER BY '.$order;
			}
		}

		if(($pagination=$this->getPagination())!==false)
		{
			$pagination->setItemCount($this->getTotalItemCount());
			$limit=$pagination->getLimit();
			$offset=$pagination->getOffset();
			$command->text=$command->getConnection()->getCommandBuilder()->applyLimit($command->text,$limit,$offset);
		}

		foreach($this->params as $name=>$value)
			$command->bindValue($name,$value);

		return $command->queryAll();
	}

	/**
	 * Fetches the data item keys from the persistent data storage.
	 * @return array list of data item keys.
	 */
	protected function fetchKeys()
	{
		$keys=array();
		if($data=$this->getData())
		{
			if(is_object(reset($data)))
				foreach($data as $i=>$item)
					$keys[$i]=$item->{$this->keyField};
			else
				foreach($data as $i=>$item)
					$keys[$i]=$item[$this->keyField];
		}
		return $keys;
	}

	/**
	 * Calculates the total number of data items.
	 * This method is invoked when {@link getTotalItemCount()} is invoked
	 * and {@link totalItemCount} is not set previously.
	 * The default implementation simply returns 0.
	 * You may override this method to return accurate total number of data items.
	 * @return integer the total number of data items.
	 */
	protected function calculateTotalItemCount()
	{
		return 0;
	}
}