Yii Framework Forum: CGridView to display data from two tables? - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

CGridView to display data from two tables? Rate Topic: ***-- 1 Votes

#1 User is offline   Sathishkumar Shanmugam 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 25-May 12

Posted 28 May 2012 - 01:04 AM

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,
0

#2 User is offline   Emily Dickinson 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 201
  • Joined: 17-September 10
  • Location:Albuquerque, NM

Posted 28 May 2012 - 02:00 AM

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.yiiframew...-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:
0

#3 User is offline   Emily Dickinson 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 201
  • Joined: 17-September 10
  • Location:Albuquerque, NM

Posted 28 May 2012 - 02:05 AM

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:
0

#4 User is offline   migajek 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 81
  • Joined: 15-November 09

Posted 28 May 2012 - 03:04 AM

View PostEmily Dickinson, on 28 May 2012 - 02:00 AM, said:


...
      'columns'=>array(
           array('name'=>'studentRegNo',  'value'=>$data->student->reg_no),   // student id
           array('name'=>'studentName',  'value'=>$data->student->s_name),   // student name
           'salary'  // employee.salary
        ),
...



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! ;)
0

#5 User is offline   Emily Dickinson 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 201
  • Joined: 17-September 10
  • Location:Albuquerque, NM

Posted 28 May 2012 - 03:50 AM

Thanks Migajek! I corrected my reply above.
:mellow:
0

#6 User is offline   Sathishkumar Shanmugam 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 25-May 12

Posted 29 May 2012 - 02:41 AM

Thank you........ :P
0

#7 User is offline   Sathishkumar Shanmugam 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 25-May 12

Posted 29 May 2012 - 03:54 AM

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:
0

#8 User is offline   Sathishkumar Shanmugam 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 25-May 12

Posted 29 May 2012 - 07:32 AM

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
0

#9 User is offline   Emily Dickinson 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 201
  • Joined: 17-September 10
  • Location:Albuquerque, NM

Posted 29 May 2012 - 08:09 AM

View PostSathishkumar Shanmugam, on 29 May 2012 - 07:32 AM, said:

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

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:
0

#10 User is offline   Fran1978 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 24
  • Joined: 13-June 11

Posted 02 August 2012 - 04:36 AM

Hello Emily,
With your approach, reg_no and s_name 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
0

#11 User is offline   YearningForYii 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 63
  • Joined: 21-September 12

Posted 13 October 2012 - 04:15 AM

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?
0

#12 User is offline   Latha 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 43
  • Joined: 17-June 13

Posted 04 July 2013 - 03:43 AM

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..??
0

#13 User is offline   Khani 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 17-October 13

Posted 30 October 2013 - 05:32 AM

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'=>array(self::BELONGS_TO,'Brand','Brand'),
);
}

Here is my View.

<?php $this->widget('zii.widgets.grid.CGridView', array(
'id'=>'ppomas-grid',
'dataProvider'=>$model->search(),
'filter'=>$model,
'columns'=>array(
'Brand'=> array(
'name' => 'Brand',
'value' => '$data->Brand->BrandName',
'filter'=> CHtml::listData(Brand::model()->findAll(array('order'=>'BrandName')), 'id', 'BrandName')
),
'Product',
array(
'class'=>'CButtonColumn',
),
),
)); ?>

Pls help me anybody...
0

#14 User is offline   emmi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 48
  • Joined: 02-December 13

Posted 17 January 2014 - 04:31 AM

View PostSathishkumar Shanmugam, on 29 May 2012 - 03:54 AM, said:

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:



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]
0

#15 User is offline   emmi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 48
  • Joined: 02-December 13

Posted 17 January 2014 - 04:35 AM

View PostKhani, on 30 October 2013 - 05:32 AM, said:

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'=>array(self::BELONGS_TO,'Brand','Brand'),
);
}

Here is my View.

<?php $this->widget('zii.widgets.grid.CGridView', array(
'id'=>'ppomas-grid',
'dataProvider'=>$model->search(),
'filter'=>$model,
'columns'=>array(
'Brand'=> array(
'name' => 'Brand',
'value' => '$data->Brand->BrandName',
'filter'=> CHtml::listData(Brand::model()->findAll(array('order'=>'BrandName')), 'id', 'BrandName')
),
'Product',
array(
'class'=>'CButtonColumn',
),
),
)); ?>

Pls help me anybody...



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')), 

0

#16 User is offline   pjravs 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 06-February 14

Posted 11 February 2014 - 10:20 PM

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!
0

#17 User is offline   le_top 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 373
  • Joined: 08-June 10
  • Location:France (Ile-de-France/Val d'Oise)

Posted 24 February 2014 - 03:00 PM

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!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users