Having some serious frustration with this one…could do it using raw SQL but want to learn ‘the Yii way’
Three tables: teachers, students, and a many-to-many assignment table. Teachers have many students; Students have many teachers. Relations are defined in the model classes of both students and teachers.
How do I set up a dataProvider for CListView so that it only shows students who ‘belong’ to the currently logged in teacher?
I’ve tried using ‘with’=> and even using a foreach loop through an array of teacher objects. The best I can do is get a variable to store an array of the right student objects - but without being in a dataProvider CListView won’t display anything.
// $teacher is a CActiveRecord object representing one row in the teacher table,
// for the currently logged-in teacher:
$criteria=new CDbCriteria;
$criteria->compare('teacher_id', $teacher->id, false);
$dataProvider = new CActiveDataProvider('Student', array('criteria'=>$criteria));
Gives me a “Column not found: 1054 Unknown column ‘teacher_id’ in ‘where clause’” error. Will compare return student type objects for the view to display?
Have spent a few more hours on this with no luck. Teachers and students have a many-to-many relationship. Teacher and schools have a many-to-one relationship and this works (where schoolsSchool is the relation defined in the model).
$test = $teacher->schoolsSchool->school_name; // this gets the teacher's school
Using this in my students controller gives no errors but I can’t access any of the student’s attributes in the view file.
The Student active record class does not have a teacher_id column. The student and teacher tables both have foreign keys in a TeacherHasStudents table (to handle the many-to-many relationship between teachers and students).
Relations function for Students looks like this:
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'schoolsSchool' => array(self::BELONGS_TO, 'Schools', 'schools_school_id'),
'classes' => array(self::MANY_MANY, 'Classes', 'students_has_classes(students_student_id, classes_class_id)'),
'teachers' => array(self::MANY_MANY, 'Teachers', 'teachers_has_students(students_student_id, teachers_teacher_id)'),
);
}
As I say I’ve gotten one-to-many relations to work fine but not many-to-many. Neither the Yii book nor any of the documentation I’ve read give a clear (i.e. noob friendly explanation) of how to implement many-to-many relationships.
Edit: (removed some text with assumptions since I now have test results)
Which model do you want to use as primary in the query? I my answer to be consistent with the relationship declaration you posted. (Querying Students join Teachers through teachers_has_students.
Well here we are still trying to do a simple JOIN in Yii. Thank you for your suggestions @tri but they both don’t even compile when testing on the sever.
This has really put my whole project on hold. The only way forward seems to be to abandon Yii altogether and just write the SQL by hand. Why is there no definitive documentation or examples of working with many-to-many tables anywhere?
I’m going to go back to an earlier approach I found elsewhere in the forum but seriously I don’t know what else to do.
Yii has built in "old-fashioned" querying. You can tediously loop over MySql results, just like you did before you we were given the gift of PHP frameworks like Yii:
$dbCommand = Yii::app()->db->createCommand("SELECT login FROM `user`");
$results = $dbCommand->queryAll();
foreach ($results as $result) {
$login = $result['login'];
}
Not perfect. But give the Yii wizards time, and my thought is that many-to-many will get more robust.
Please see my reply on your other post - there is definitely a fairly simple way to do this as described, you just need to get the CDbCriteria right - some debugging using the Yii log to examine the generated SQL should see you right, we’re talking about a simple JOIN with condition here.
