Joining Two Tables With Foreign Key Relation

Yii 1.1 General Discussion
1

I am trying to join two tables student and marks.Tring to acheive this:


select * from 

 student s

inner join marks m on s.studentId = m.sid

To implement this via yii,

In model file, relation is set as




	return array(

'marks' => array(self::HAS_MANY, 'Marks', 'sID',),

);

In controller,


$test 		  = Student::model()->with('marks')->findAll();    

   print_r($test->mark1);

But, the above displays error as "Trying to get property of non-object".

Any idea abt what is wrong in the above code?

2

findAll() returns an array, so $test is an array, so $test->mark1 does not work.

3

Yes,it returns array anf if I try to loop through the array,it is returning the columns of first table(student).But while trying to

retreive the columns of second table joined(marks), it is returning error as ‘Property “Student.mark1” is not defined.’

I have two separate models for each table ‘student’ and ‘marks’.

4

Try this:


foreach(Student::model()->with('marks')->findAll() as $student) {

	foreach($student->marks as $mark) {

		CVarDumper::dump($mark->attributes,10,true);

	}

}
5

Thanks. It shows the result.

  1. Can you please tell how to pass as dataprovider to display in gridview?

  2. IS there a provision to see the sql statement executed in yii?

6

  1. To display in a CGridView, I suggest to use my RelatedSearchBehavior extension . Get the demo, check it out, make some changes to get the feel of it and then apply to your case.

  2. There are many ways to see the SQL statements, all use the log routes. I use http://www.yiiframework.com/extension/yiidebugtb/ to see the requests in my browser in a not too intrusive and efficient way.

7

Thanks.

The query seems to be correct in the debugging screen with joining two tables as expected.

To displlay the result in a grid,


$data->studentName //first table's field

is working.

But not getting the joined table’s(marks) columnname.


$data->relationName->secondtablecolumn name

is showing as property undefined.

8

Hi

I guess that we are back to the initial issue: your relation is a ‘HAS_MANY’ which means that ‘$data->relationName’ returns an array so you can not do ‘->secondtablecolumn’ on the array, you must loop on it.

If you want to show all marks, use the marks as the base model and determine the student for each mark in your gridview. Then you’ll have a ‘HAS_ONE’ relation.

9

Solved by accessing this way.

‘$data->marks==null?"":$data->marks[0]->mark1’,

10

that is a solution, but you get only the first mark!