Using a CDbDataReader (sql query result) in a zii widget (such as CGridView or CListView)

If in your application you have to proceed to a complex SQL query that is not associated to a model, you will use CDbDataReader like it is described in the Yii documentation. An example could be

$oDbConnection = Yii::app()->db; // Getting database connection (config/main.php has to set up database
// Here you will use your complex sql query using a string or other yii ways to create your query
$oCommand = $oDbConnection->createCommand('SELECT * FROM my_table WHERE myAttr = :myValue');
// Bind the parameter
$oCommand->bindParam(':myValue', $myValueThatCameFromPostOrAnywereElse, PDO::PARAM_STR);

$oCDbDataReader = $oCommand->queryAll(); // Run query and get all results in a CDbDataReader

Then if you use $oCDbDataReader in your view as a dataprovider, it won't work (because this object is not a dataprovider) :

// Will not work because $oCDbDataReader is not a valid data provider
$this->widget('zii.widgets.CListView', array(
  'dataProvider'=>$oCDbDataReader,
  'itemView'=>'_view',
));

To use zii widgets, you will have to convert your CDbDataReader into a valid data provider. You could do so using a CArrayDataProvider :

$dataProvider=new CArrayDataProvider($oSearchResultsArray, array(
  'id'=>'myDataProvider',
  'sort'=>array(
	 'attributes'=>array(
	   'id', 'title' // Attributes has to be row name of my sql query result
	    ),
  ),
  'pagination'=>array(
         'pageSize'=>50,
          ),
));

Finally in your view you will only have to use $dataprovider instead of $oCDbDataReader.

Last but not least, in your item view, CArrayDataProvider doesn't work like yii model data provider. You won't be able to use

$data->myAttribute.

You will have to use

$data['myAttribute']

I hope it could help.

8 1
8 followers
Viewed: 29 199 times
Version: 1.1
Category: How-tos
Written by: Revelis Luc Bonnin
Last updated by: Revelis Luc Bonnin
Created on: Aug 18, 2011
Last updated: 6 years ago
Update Article

Revisions

View all history

Related Articles