I’d like to use CGridView to show a dataset from joined tables.
CGridView should preferably use CActiveDataProvider.
In my model class “User”, I’m returning CActiveDataProvider from search().
In search(), I’m setting the CDbCriteria join property to include the desired second table as such:
$criteria->join='LEFT JOIN tbl_role ON tbl_role.userid = id ';
Unfortunately, the returned dataset contains only fields from tbl_user with which the User model is associated.
The criteria->join property isn’t completely ignored by CActiveDataProvider because if a reference is made to an invalid table or column an error occurs.
What is the proper way to display a dataset from joined tables with CGridView? If it’s not possible, is there an alternative?
I actually had a hard time getting CGridView to work as expected with joined tables. In the end it turns out to be relatively easy.
Basically, the relations have to be defined, and that only works properly (in my case) when you also define the foreign keys in the database. Once I had a hint that these keys were needed, it turned out that the database type that I had selected did not support foreign keys (in mysql) and I converted all tables to InnoDB (in mysql) which supports foreign keys. After setting the keys in myqsl, I regenerated the models that magically included the relations.
These relations have a name. For instance each device would have one user and one user can have many devices. Therefore, a devices points to a user_id and there would be a relation ‘user’.
Then, in the configuration for the columns of CGridView, just add ‘user.name’ for instance if you have a name in the user relationship.
It then worked like a charm for me. I haven’t tried on big tables yet though.
@ le_top: Thanks for the valuable tip. I switched to using the InnoDb engine with MySQL as you did and can now create models having automatically created relationships. After having gone through the Yii blog tutorial, I didn’t know relationships could otherwise be created automatically if the db had foreign keys which are only possible if the db engine supports them.
