Query with multiple foreign keys

Yii 1.1 General Discussion
1

Hi,

I have a problem trying to construct a query to provide a list of student names within a district.

Please help. Thanks

My tables are as follows:




      +-------+      +------------+     +----------+      +--------+  

      |student|      |  class     |     |school    |      |district|

      +-------+      +------------+     +----------+      +--------+

      |id     |      |            |     |          |      |        |

      |name   |      |            |     |          |      |        |

      |classID|>-----|id          |     |          |      |        |

      |       |      |name        |     |          |      |        |

      |       |      |schoolID    |>----|id        |      |        |

      |       |      |            |     |name      |      |        |

      |       |      |            |     |districtID|>---- | id     |

      |       |      |            |     |          |      | name   |

      +-------+      +------------+     +----------+      +--------+
2

Hello, nice ASCII graphics there! :D

I think "through" is what you need, if you want to solve the problem using ActiveRecord.

3

public function getStdentWithDistict()

{


	$sql='select s.name,d.name from student as s,class as c,school as sc,district as d where s.classID=c.id and c.schoolID=sc.id and sc.districtID=d.id';


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


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


	$results = $command->queryAll();


	return $results;


}
4

Also if you have all relation specified, you can try:




public function findAllByDistrict($districtId) {

  $c = new CDbCriteria();

  $c->with = array('class.school.district');

  $c->compare('district.id', $districtId);

  $c->together = true;

  return Student::model()->findAll($c);

}
5

This will also create objects for classes and schools, but in the case of using "through" only instances of the Student class will be created. However in both cases all tables will be joined.

6

Thank you shailesh, Weavora team and andy_s for your quick replies. I’m currently working using Weavora team’s and andy’s solution but found errors in my relationship definitions. Will try shailesh’s solution if all fails.

Appreciated all your help. :)

7

[color=#1C2837][size=2]tell me please, Any More Help[ Needed?[/size][/color]<br style="color: rgb(28, 40, 55); font-size: 13px; line-height: 19px; background-color: rgb(250, 251, 252); ">[size="2"][color="#1c2837"]

[/color][/size]