CGridView to display data from two tables?

Yii 1.1 General Discussion
1

I’m new to this yii framework…

I’d like to set up a CGridView. I’m looking to display data from 2 different tables in one view. The data is from a table called student, and another table called employee.

employee is refereed by student(reg_no)

student table

reg_no (primary key)

s_name

dept

f_name

employee table

e_no(primary key)

e_name

desig

salary

reg_no(foreign key)

I want to display the (reg_no,s_name,f_name) from student and (design,salary) from employee table in a single grid view, can anyone please give an idea or any tutorial,

2

I am assuming that:

  1. Your CActiveRecord classes are called Student and Employee respectively.

  2. The relations() function in your Employee class was properly defined. If it’s properly defined,

    then you can reference the student-related info for a given instance of Employee using


$employee->student

.

Note: Gii will generate the relations() function perfectly, as long as you have created the foreign key correctly in your database.

Your view–using a CDataProvider comprised of Employee records–can then look something like this:




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

   array(

      'id'=>'my-grid',

      'dataProvider'=>$dataProvider,

      'filter'=>$model,

      'columns'=>array(

           array('name'=>'student.reg_no',  'value'=>'$data->student->reg_no'),   // student id

           array('name'=>'student.s_name',  'value'=>'$data->student->s_name'),   // student name

           'salary'  // employee.salary

        ),

);

An aside. I find your column names confusing, and Yii’s active records make it so that prepending “e_” and “s_” is not needed.

Yii best practices advises you NOT to use prefixes in column names, and to name primary keys simply "id". (See http://www.yiiframework.com/wiki/227/guidelines-for-good-schema-design/).

Following those practices, you would end up with much simpler column names, and no need for guesswork:

student

id

name

dept

first_name

employee

id

student_id

name

design

salary

:mellow:

3

One other thought. student and employee tables can share the same id. And why not? A given employee should have only ONE record in student, right? This would eliminate the need for a column in the employee table, giving you:




employee

------------

id           # This is the primary key, and it's a foreign key --> student.id

name

design

salary

If you make that change, your Employee::relations() function becomes more like:




public function relations()   {

                return array(

                        'student' => array(self::BELONGS_TO, 'Student', 'id'),

                );

        }

>>>> END OF TWO CENTS

:mellow:

4

those "values" should be quoted, like that





           array('name'=>'studentRegNo',  'value'=>'$data->student->reg_no'),   // student id

           array('name'=>'studentName',  'value'=>'$data->student->s_name'),   // student name

don’t forget they’re evaluated for each grid row! ;)

5

Thanks Migajek! I corrected my reply above.

:mellow:

6

Thank you… :P

7

I got the output of student and employee with join by doing d following changes…

I changed my employee relation as…




public function relations()

	{

		return array(

				'reg_no'=>array( self::BELONGS_TO, 'Student', 'reg_no' ),

		);

	}

Used another search cirteria as ($model->searchEmployees())…




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

	'id'=>'employee-view-grid',

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

	'filter'=>$model,

	'columns'=>array(

		'e_no',

                array('name'=>'studentName', 'value'=>'$data->student->reg_no'),   // student id

                array('name'=>'fatherName', 'value'=>'$data->student->s_name'),   // student name

		'e_name',

		'desig',

		'reg_no',

		array('class'=>'CButtonColumn',

			'template'=>'{view}'),

		),

)); ?>

Added another search cirteria to my employee model that returns the data as a CActiveDataProvider.




public function searchEmployees()

	{

		$criteria=new CDbCriteria;

		$criteria->alias = 'i';

   	 		

		$criteria->compare('e_no',$this->e_no);

		$criteria->compare('e_name',$this->e_name,true);

		$criteria->compare('desig',$this->desig,true);

		$criteria->compare('reg_no',$this->reg_no);

		$criteria->join= 'JOIN employee d ON (i.reg_no=d.reg_no)';

		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			

		));

	}

Now i’m getting the join of employee and student as output… But still i have a doubt of including the 2 fields from student table to employee using the code said by Emily Dickinson…




 array('name'=>'studentName',  'value'=>'$data->student->reg_no'),   // student id

 array('name'=>'fatherName',  'value'=>'$data->student->s_name'),   // student name

If i include this code in my gridview when it displayed an error message as "studentName not in the employee" like that…

pls hell me to solve this problem… :mellow:

8

Exactly i got a output as i want… The following code may be help some one in future…

simply i added a column in my grid view with function searchEmployees(employee/index)…




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

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

	'filter'=>$model,

	'columns'=>array(

		'e_no',

		'e_name',

		array('header'=>'Father Name','value'=>array($model,'searchEmployees')),

		'desig',

		'reg_no',

		array('class'=>'CButtonColumn',

			'template'=>'{view}'),

		),

)); ?>

The respective function in my model(models/Employee)




public function searchEmployees($data,$row)

	{

		$connection=Yii::app()->db; 	

		$sql="SELECT * FROM student where reg_no ='$data->reg_no'";

		$command=$connection->createCommand($sql);

		$dataReader=$command->query();

		$rows=$dataReader->readAll();

		$ans=array();

		foreach($rows as $data)

		{

			$ans=$data['f_name'];

		}

		return $ans;

	}

Thank you… :o

9

Good that you got things to work, but you’re not taking advantage of what Yii gives you for free. Your Employee model already has the student data in it. You’ve made your view and controller unnecessarily complex.

There was an error in the code I posted above. Clearly the column names have to be real column names. I corrected it above to read:




  'columns'=>array(

           array('name'=>'student.reg_no',  'value'=>'$data->student->reg_no'),   // student id

           array('name'=>'student.s_name',  'value'=>'$data->student->s_name'),   // student name

           'salary'  // employee.salary

:mellow:

10

Hello Emily,

With your approach, [color="#008000"]reg_no[/color] and [color="#008000"]s_name[/color] columns will display ok, but without sorting and filtering capabilities. The only way I’ve found to solve this is the one explained in this link:

Searching and sorting a column from a related table in a CGridView

Do you know if there is a way to perform sorting and filtering, following the approach that you adopted?

Thanks

11

Actually I have done these things,but I am not getting the output.Is it that I need to pass something from controller or model or use it directly in the view as $data->a->b?

12

hi i am getting this error:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘tbl_a.id’ in ‘on clause’. The SQL statement executed was: SELECT COUNT(*) FROM tbl_ab i JOIN tbl_ab d ON (tbl_a.id=d.uid)

can anybody plz help me…??

13

Hi I want to display data from another model in yii CGidView.Here is my code below.Its showing the error as ‘Trying to get property of Non object’. Can anybody pls help me…

model.php

public function relations()

{

return array(

	'Brand'=&gt;array(self::BELONGS_TO,'Brand','Brand'),


	);

}

Here is my View.

<?php $this->widget(‘zii.widgets.grid.CGridView’, array(

'id'=&gt;'ppomas-grid',


'dataProvider'=&gt;&#036;model-&gt;search(),


'filter'=&gt;&#036;model,


'columns'=&gt;array(


	'Brand'=&gt; array(


	'name' =&gt; 'Brand',


	'value' =&gt; '&#036;data-&gt;Brand-&gt;BrandName',


	'filter'=&gt; CHtml::listData(Brand::model()-&gt;findAll(array('order'=&gt;'BrandName')), 'id', 'BrandName')


	), 


	'Product',


	array(


		'class'=&gt;'CButtonColumn',


	),


),

)); ?>

Pls help me anybody…

14

works if ‘name’ is replaced by ‘header’




 array('name'=>'studentName',  'value'=>'$data->student->reg_no'),   // student id

 array('name'=>'fatherName',  'value'=>'$data->student->s_name'),   // student name







 array('header'=>'studentName',  'value'=>'$data->student->reg_no'),   // student id

 array('header'=>'fatherName',  'value'=>'$data->student->s_name'),   // student name

[/code]

15

use this i.e., replace name with header




array('header'=>'Brand',  'value'=>'$data->Brand->BrandName','filter'=> CHtml::listData(Brand::model()->findAll(array('order'=>'BrandName')), 'id', 'BrandName')),
16

Good Day!

What if the student has another table connected to him not connected to the Employee Table and I want to show the data on it. How can I view it. Thanks!

17

I recommend that you check my extension: www.yiiframework.com/extension/relatedsearchbehavior/ which is very usefull for this. Check out the demo for it: there are many tables referenced in a single grid and all columns are sortable/searcheable!

18

I am new here.having the same problem. Can anyone please explain where and what to write for the following variables:

$dataProvider

$model

$data

1 Like