Right dataProvider for CListView

Yii 1.1 General Discussion
1

I have two tables - ‘teachers’ and ‘students’ joined in a many-to-many relationship via a 3rd table ‘TeachersHasStudents’.

I’ve used 2 foreach loops to build an array of all the student objects belonging to the currently logged in teacher.


// the models to work with

		$teachers = Teachers::model()->findAll($condition="teacher_id='$userId'");

		$students = Students::model()->findAll();

		

		foreach($teachers as $teacher)

		{

			// get all students for this teacher

			foreach($teacher->students as $student)

			{

				echo $student->student_name_first . "<br />"; // works - echo's each name

				// build array

				$rawData=array($student);

			}

		}

		echo $rawData[0]['student_name_first'];  //works - echo's the name of first student in the array

		// build dataProvider - the bit I'm stuck on??

		$dataProvider=new CArrayDataProvider($rawData);

I want to display them using the CListView widget - which dataProvider should I use and how would I set it up?

Thanks in advance for any help

O

2

Hi Ohawk,

You cannot use array inside the dataProvider.

My suggestion is to use CDBCriteria (Ref: http://www.yiiframework.com/doc/api/1.1/CDbCriteria) to "filter" your data, follow by using CDataProvider to use that CDbCriteria that you have created and display them in the CListView.

As for table to table, you should use the relationship inside the Model (Model’s relations() function) to define the relationship between your students and teachers table. (Ref: http://www.yiiframework.com/doc/guide/1.1/en/database.arr#declaring-relationship)

Hope it will help.

3

You should definitely be making your life a lot simpler with ActiveRecord here!

Firstly, why are you doing a findAll on Teachers? I assume that since you are giving a condition = $userId you are selecting only one particular teacher and that userId is your primary key for the teachers table - in which case you should simply use findByPk($userId), which will return the appropriate teacher.

Secondly, as oiiooiio said, you should be defining relations inside the models to join teachers and students, rather than using SQL criteria everywhere.

For example, in your relations array for your Teacher model, you might have:


'students' => array(self::MANY_MANY, 'Student', TeachersHasStudents(teacher_id, student_id)'),

and in your Student model:


'teachers' => array(self::MANY_MANY, 'Teacher', TeachersHasStudents(student_id, teacher_id)'),

These two lines tell AR that a Teacher has many students (mode Student) who can be found using the TeachersHasStudents table, and vice versa. This then allows you to find your teacher using $teacher = Teacher::model()->findByPk($userId), and then access that teacher’s students simply through $teacher->students.

To use this in a CListView you would do it slightly differently. Presuming that you want to list the students, you would create a CActiveDataProvider on the Student model, passing in a CDbCriteria array


                $dataProvider = new CActiveDataProvider('Student', array(

			'criteria'=>array(

				'with' => array(

					'teachers' => array(

						'alias' => 'studentTeachers',

                                                'on' => 'studentTeachers.id = :teacherId',

                                                'params' => array(

                                                        ':teacherId' => $userId,

                                                ),

					),

				),

			),

			'pagination' => array(

				'pageSize' => 10,

			),

		));

Please note - I haven’t tested these CDbCriteria, but something like that should do the job.

4

@oiiooiio That’s what I was affraid of but this is the only approach that I’ve gotten close with. It’s based on a ‘mini-tutorial’ posted in the forum by Jefftulsa (on the Yii dev team) See here. Again, there seems to be no definitive advice about how to handle many-to-many relationships inside a Yii webapp.

@redrabbit Thanks for the input - I’ll give your version of CActiveDataProvider a shot. I do have all the neccessary relations set in both models. What I can’t do is get them into a format (i.e. a dataProvider) that the listview widget will accept. Also, without using a findAll on teachers the outermost foreach loop won’t work.

Will let you know how I get on.

O

5

My point is more why you should need that loop anyway, since presumably you are only ever getting the users for one teacher.

If you use a CActiveDataProvider, you don’t need to construct any of these arrays anyway, you just pass it the model to use, and the CDbCriteria and it will take care of the rest (getting item counts for pagination and loading the appropriate models etc). All you then have to do is pass this dataProvider to your view, call the CListView widget with it in your view, and create the appropriate item view to create the html for each item (accessing the individual student properties through $data, such as $data->student_name_first).

It is honestly made very easy in Yii, you just need to get the selections right. I would suggest first making sure that your CActiveDataProvider is getting the right selection simply by enabling logging and copying the generated SQL to whatever MySQL GUI you are using to see if it gives you the results you expect. Once that’s working you’ll just need to configure the CListView as you need.

I use MANY_MANY relationships myself, so I’m sure it will work. The only thing I haven’t done is base my criteria on a selection on the subrelation. Start using just the:


                $dataProvider = new CActiveDataProvider('Student', array(

                        'criteria'=>array(

                                'with' => array(

                                        'teachers' => array(

                                                'alias' => 'studentTeachers',

                                        ),

                                ),

                        ),

                        'pagination' => array(

                                'pageSize' => 10,

                        ),

                ));

(which will obviously give you all the students, but should show you that it works), and then add your condition.

6

Thanks RedRabbit you’ve really given me something to work with there. Providing I have any energy after work today I’m going to get into it. Will post results, thx again. O

7

Can’t seem to get the condition placement right. This does indeed list all students fine.


$dataProvider = new CActiveDataProvider('Student', array(

                        'criteria'=>array(

                                'with' => array(

                                        'teachers' => array(

                                                'alias' => 'studentTeachers',

                                        ),

                                ),

                        ),

                        'pagination' => array(

                                'pageSize' => 10,

                        ),

                ));

This gives me Unknown column ‘teachers.teacher_id’ in ‘where clause’


$dataProvider = new CActiveDataProvider('Students', array(

                        'criteria'=>array(

                                'with' => array(

                                        'teachers' => array(

						'condition'=>"teachers.teacher_id=1",

                                                'alias' => 'studentTeachers',

                                        ),

                                ),

                        ),

                        'pagination' => array(

                                'pageSize' => 10,

                        ),

                ));

I’ll be honest I have no clue what I’m doing - I’m literally just trying ‘condition’ in loads of different places! Yii seems to be doing a lot of separate SQL statements in the debug output - makes it very hard to interpret.

8

I’m afraid I can’t be bothered to make up an application to mirror what you are doing, but if you want to send me a zip of your application with a sample database, I’ll take a look at it.

The instant error that leaps out is that the alias entry specifies what the join table is to be called, so if you’re calling it studentTeachers, then the condition should be studentTeachers.teacher_id = 1. However, I think this may be better in the ‘on’ rather than the ‘condition’ (this is more visible in debugging again). Do you already have the SQL statement that does what you want?

9

Interestingly this


$dataProvider = new CActiveDataProvider('Students', array(

                        'criteria'=>array(

                                'with'=>array('teachers'=>array(

					'condition'=>"studentTeachers.teacher_id=1",

					'alias'=>'studentTeachers')),

                        ),

                        'pagination' => array(

                                'pageSize' => 10,

                        ),

                ));

creates an SQL query that returns the student_id’s of the right students for a teacher but in the view (using CListView widget) ALL the students are displayed.

10

And finally ladies and gentleman this:


$dataProvider = new CActiveDataProvider('Students', array(

                        'criteria'=>array(

                                'with'=>array('teachers'=>array(

					'condition'=>"studentTeachers.teacher_id=1",

					'together'=>'true',

					'alias'=>'studentTeachers')),

                        ),

                        'pagination' => array(

                                'pageSize' => 10,

                        ),

                ));

yields only those students that belong to the currently logged in teacher!!! Woo hoo! Sank a lot of hours into this - thanks forum (esp. RedRabbit) for all your help.

What does the ‘together’ clause tell Yii to do?

11

Cool - glad you found it! And I think that now you’ll find how easy it is to do many different things with your views etc - Yii has a lot to offer!

I haven’t delved deep enough into the ‘together’ clause to know exactly what it is doing in the SQL, but from what I’ve read, you use it when you have joins that Yii potentially chooses to split between multiple queries (as part of the optimization - sometimes if you make a lot of joins, it might keep the number of queries down but it can bring back a lot of superfluous information, so sometimes it is more efficient to make several smaller queries). There are a couple of articles that might help with things like that - ActiveRecord and Relational AR.

Ok, have fun with the rest!

12

Congratulation Ohawk! You did it!

What does the ‘together’ clause tell Yii to do?

Ans:

together: whether the table associated with this relationship should be forced to join together with the primary table and other tables. This option is only meaningful for HAS_MANY and MANY_MANY relations. If this option is set false, the table associated with the HAS_MANY or MANY_MANY relation will be joined with the primary table in a separate SQL query, which may improve the overall query performance since less duplicated data is returned. If this option is set true, the associated table will always be joined with the primary table in a single SQL query, even if the primary table is paginated. If this option is not set, the associated table will be joined with the primary table in a single SQL query only when the primary table is not paginated. For more details, see the section "Relational Query Performance". This option has been available since version 1.0.3.

Ref: http://www.yiiframework.com/doc/guide/1.1/en/database.arr