Hi. I have a city model which contains two attributes: id & city. I try to use CGridView and CArrayDataProvider instead of CActiveDataProvider. Here is my code for search function on city model:
public function search()
{
$page = 1;
if (isset($_GET[strtolower(__CLASS__) . '_page']))
$page = intval($_GET[strtolower(__CLASS__) . '_page']);
$offset = ($page - 1) * Yii::app()->params['pageSize'];
$rowCount = Yii::app()->params['pageSize'];
$sql = "SELECT t1.id, t1.city
FROM tbl_country_city t1
ORDER BY t1.city LIMIT {$offset}, {$rowCount}";
$sqlCount = "SELECT COUNT(t1.id)
FROM tbl_country_city t1
ORDER BY t1.city";
$countData = Yii::app()->db->createCommand($sqlCount)->queryScalar();
$rawData = Yii::app()->db->createCommand($sql)->queryAll();
$dataProvider = new CArrayDataProvider($rawData, array(
'totalItemCount' => $countData,
));
//var_dump($rawData); <--- $rawData always contain appropriate data whatever the page it is on.
//var_dump($dataProvider->getData()); <--- except on page 1, getData() resulting empty arrays.
//exit();
return $dataProvider;
}
The problem is CGridView always displays no data for page above 1.
CArrayDataProvider is not as convenient and efficient as you might be thinking …
You have to supply ALL THE DATA to the CArrayDataProvider, without OFFSET and LIMIT, when you want the pagination of the CGridView (or CListView) to work properly.
The following will work as expected:
public function search()
{
// $page = 1;
// if (isset($_GET[strtolower(__CLASS__) . '_page']))
// $page = intval($_GET[strtolower(__CLASS__) . '_page']);
// $offset = ($page - 1) * Yii::app()->params['pageSize'];
// $rowCount = Yii::app()->params['pageSize'];
$sql = "SELECT t1.id, t1.city
FROM tbl_country_city t1
// ORDER BY t1.city LIMIT {$offset}, {$rowCount}";
ORDER BY t1.city;
...
If you do want to supply the grid with only the data for the current page, then I think you have to cheat the pager of the grid with some hacky trick.
[s]Or it might be a good idea to prepend a set of empty dummy data to the $rawData array according to the pagination.
[/s] [EDIT] Generally it’s not a good idea …
There’s no reason to choose CArrayDataProvider over CActiveDataProvider, except when you have to deal with a condition too complex to be implemented using SQL.
Hi softark. Thank you for the response. I read from the yii guide. It says:
[b][i]Do not overuse Active Record. Although Active Record is good at modelling data in an
OOP fashion, it actually degrades performance due to the fact that it needs to create one
or several objects to represent each row of query result. For data intensive applications,
using DAO or database APIs at lower level could be a better choice.
Last but not least, use LIMIT in your SELECT queries. This avoids fetching overwhelming
data from database and exhausting the memory allocated to PHP.[/i][/b]
This is the reason why i think i should use CArrayDataProvider over CActiveDataProvider for complex query. But i don’t know how to do it with CArrayDataProvider. Can you show me simple code that show the use of CArrayDataProvider and CGridView?
As I said, CArrayDataProvider is, well, far less efficient and convenient than CActiveDataProvider when you fetch data from database using SQL. You may consider using CSqlDataProvider if you are serious about the performance, but there’s no reason at all to stick to CArrayDataProvider.
The following is a very rough sketch about their pros and cons.
The worst thing about CArrayDataProvider is that you have to supply all the data that matches the given conditions (not only those for the current page). Otherwise the pagination and the sorting won’t work. It’s because CArrayDataProvider needs a raw data array as a whole on which it executes the sorting and the paginating.
On the other hand, CActiveDataProvider and CSqlDataProvider fetch only the data for the current page from database, because they construct OFFSET and LIMIT automatically according to the instruction of the pager. And the sorting is executed in SQL. So the fetched data is already sorted and paginated.
IMO, 80% or 90% of the database related jobs can be handled quite effectively and easily using CActiveRecord or CActiveDataProvider without any performance problem. I usually take this way.
Sometimes I use DAO for performance reason, but not very often.
And it’s true that sometimes you have to use CArrayDataProvider when the data set can’t be acquired solely by SQL. (For example, a scheduler for a group … X axis for dates and Y axis for persons … will be difficult for CActiveDataProvider or CSqlDataProvider)
