CSqlDataProvider + CGridView

Hi

I need to use CSqlDataProvider to fill the data in a CGridView, because I’m using a complex query

Is it kind of working … I still need to specify columns

Any example of how to do it ?

Also I need to use filter in this grid … is it possible ? if yes how ?

Thanks a lot in advance

Ps: I wont post my code here because it has no relevance, I just need an example

Solved by using CDbCriteria to create the query

Cheers

Would you mind posting your solution? Many thanks!

sure

its a big piece of code, but I’ll explain it

first transform the code into criteria, use in the select "as" to define the keys names

then define the keys as public variables of the model and use it in the compare method (also add it to the rules as safe on search)

then in the grid view use the defined keys as columns

here is the code of how I did

declare the variables that you will use later




public $e_Tipo,$e_Serial,$e_Marca,$e_Modelo;

public $u_Nombre_Completo,$u_Sede,$u_Cargo;



Search function




public function searchRel()

	{

		$criteria=new CDbCriteria;

    	$criteria->select='

            	t.OC, 

            	t.Instalacion, 

            	t.Responsable_Une, 

            	t.Telefono,

            	t.Agendamiento,

            	t.Observaciones,

            	usuarios.Nombre_Completo as u_Nombre_Completo,

            	usuarios.Cargo as u_Cargo,

            	usuarios.Sede as u_Sede,

            	tipo_equipos.Tipo as e_Tipo,

            	tipo_equipos.Serial as e_Serial,

            	tipo_equipos.Marca as e_Marca,

            	tipo_equipos.Modelo as e_Modelo';

    	$criteria->with=array('tipo_equipos','usuarios');

    	

    	$criteria->compare('t.OC',$this->OC);

		$criteria->compare('t.Serial',$this->Serial,true);

		$criteria->compare('t.Identificacion',$this->Identificacion);

		$criteria->compare('t.Instalacion',$this->Instalacion,true);

		$criteria->compare('t.Responsable_Une',$this->Responsable_Une,true);

		$criteria->compare('t.Telefono',$this->Telefono,true);

		$criteria->compare('t.Agendamiento',$this->Agendamiento,true);

		$criteria->compare('t.Observaciones',$this->Observaciones,true);

    	$criteria->compare('tipo_equipos.Tipo',$this->e_Tipo,true);

    	$criteria->compare('tipo_equipos.Serial',$this->e_Serial,true);

    	$criteria->compare('tipo_equipos.Marca',$this->e_Marca,true);

    	$criteria->compare('tipo_equipos.Modelo',$this->e_Modelo,true);

    	$criteria->compare('usuarios.Nombre_Completo',$this->u_Nombre_Completo,true);

    	$criteria->compare('usuario.Cargo',$this->u_Cargo,true);

    	$criteria->compare('usuario.Sede',$this->u_Sede,true);

    	return new CActiveDataProvider(get_class($this),array(

        	'criteria'=>$criteria

    	));

	}



the relations




public function relations()

	{

		return array(

        	'usuarios'=>array(self::BELONGS_TO,'Usuarios','Identificacion'),

        	'tipo_equipos'=>array(self::BELONGS_TO,'TipoEquipos','','on'=>'t.OC=tipo_equipos.OC'),//the foreign key is not primary

		);

	}



the grid




$this->widget('zii.widgets.grid.CGridView', array(

	'dataProvider'=>$model->searchRel(),

	'filter'=>$model,

	'columns'=>array(

    	'OC',

    	'Instalacion',

    	'Responsable_Une',

    	'Telefono',

    	'Agendamiento',

    	'Observaciones',

    	'u_Nombre_Completo',

    	'u_Cargo',

    	'u_Sede',

    	'e_Tipo',

    	'e_Serial',

    	'e_Marca',

    	'e_Modelo',

	),

)); 



controller




function actionConsulta1(){

	$model=new ControlReposHabUne('search');

	$model->unsetAttributes();  

	if(isset($_GET['ControlReposHabUne'])){

		$model->attributes=$_GET['ControlReposHabUne'];

	}

	$this->render('consulta1',array(

		'model'=>$model,

	));

}



I use the same approach for my grids. But I can’t make these columns sortable. Do you have the same problem? If so, any solutions?

I wasn’t able to make it sortable, but didn’t try much cuz it was not in my todo list

Hi Gustavo,

I have been revisiting my code again and there is a fundamental difference between your example and my data. You are only referring to data that have relations within your model, whilst my original SQL code grabs info from other places as well. Let me show you how it currently is set up:




                        $sql='SELECT `profiles`.phone,

			`users`.id,

			`profiles`.`name` as user_name,

			`profiles`.company,

			course_event_registration.registered,

			course_event_registration.event_id,

			course_event.title as event_title,

			course_event.start_date,

			course_module.`name`

			FROM users INNER JOIN `profiles` ON users.id = `profiles`.user_id

			 INNER JOIN course_event_registration ON course_event_registration.user_id = users.id

			 INNER JOIN course_event ON course_event_registration.event_id = course_event.id

			 INNER JOIN course_module ON course_event.module_id = course_module.id';




The model on this scenario is the course_event_registration, which only has relations to users and course_event.

Would this work if transformed to criteria? Here’s how I am implementing the widget:




$this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'course-event-registration-grid',

	'cssFile' => Yii::app()->baseUrl . '/css/gridViewStyle/gridView.css',

	'pager' => array('cssFile' => Yii::app()->baseUrl . '/css/gridViewStyle/gridView.css'),

	'dataProvider'=>$model->sqlSearch(),

	'filter'=>$model,

	'filterPosition'=>'body',

	'columns'=>array(

		'user_name',

		'company',

		'phone',

	 	array(

         'name'=>'Event',

                'filter'=>CHtml::listData(CourseEvent::model()->findAll(), 'id', 'title'), // fields from modules table

			    'value'=>'$data["event_title"]'

        ),

		'registered',

	),

));



Any insights on how to take this matter further?

Many thanks,

Cass

and what about if your sql statement has a union all from another model? no relationships.

Please check this link http://www.yiiframework.com/forum/index.php/topic/40156-how-to-filter-data-in-cgridview-when-records-get-from-different-models/

and send me the suggestion