sort for many_many related column

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?

regards Horizons

If i add something like this to my post:search function i could sort for the category texts.




  $sort = new CSort();

        $sort->modelClass=get_class($this);

        $sort->attributes = array(

         'categories'=>array(

              'asc'=>'category_sort.category_sort ASC',

              'desc'=>'category_sort.category_sort DESC',

              'default'=>'asc',

            ),

            '*',

);




$criteria->join = 'LEFT OUTER JOIN (SELECT post_id, GROUP_CONCAT(category) as category_sort

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) AS category_sort ON (`t`.`id`=`category_sort`.`post_id`)';



But could this be done another ‘simple’ way through some relation?

regards Horizons