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