Hi @ all,
I have one "simple" question.
If I have a database structure like here: http://www.yiiframework.com/doc/guide/1.1/en/database.arr
And I have a gridview where i want to display the categories as a list inside the gridview and also want to sort my post items in order of the categogories.name.
I render the catgories with some renderpartitial template i a simple list. Everything works fine.
But Now I wanted to sort the table after the categories names.
I added this code to the controller:
$criteria->together=true; $criteria->with=array('categories'); $sort = new CSort(); $sort->modelClass=get_class($this); $sort->attributes = array( 'categories'=>array( 'asc'=>'categories.category ASC', 'desc'=>'categories.category DESC', 'default'=>'asc', ), '*', );
And added the sort to the CActiveDataProvider.
The sorting works as expected “BUT” because of the multiple categories which can be selected for each post I get only less entries then my selected ‘limit’.
e.g. if i have a limit of 10 and 2 post have 2 categories i will only get 8 results displayed on page 1 (when i sorted for the categories names).
But how can i fix this?
I thought if selecting a ‘virtual column’ which can be used for sorting with the group concated categorie names of the selected post entries.
the select for that column could be something like this:
SELECT post_id, GROUP_CONCAT(category) FROM tbl_category LEFT OUTER JOIN `tbl_post_category` ON (`tbl_category`.`id`=`tbl_post_category`.`category_id`) GROUP BY tbl_post_category.post_id;
which result would something like
post_id ID of Post GROUP_CONCAT(category) absteigend 3 3rd category 14 3rd category 1 2nd category,1st category 15 2nd category,1st category 13 2nd category 19 1st category
in my example the posts with id 1 and 15 had multiple categories.
With this column the sorting would work as normal.
but how can i add this to my CActiveDataprovider select so its selected with the other columns?
Is it possible to add a relation which does this select?
Or is there a better solution? Maybe this problem is already fixed on the svn version?