Hello guys!
I’m relative new at Yii and I have a pretty simple question, but I haven’t managed to find a solution although I have tried a lot… So my last (but not the least!!! 
) hope is the community!
I have the following mysql DB scheme, shown in the attached figure.
I want to apply some aggregate functions (COUNT(*), AVG(rate_pst), etc.) on a subgroup A of tbl_Post records grouping (aggregate function results) by ascNum_pst field of tbl_Post. The corresponding query in MySQL would be for example something like:
SELECT A.ascNum_pst, COUNT(*), AVG(A.rate_pst)
FROM (SELECT rate_pst, ascNum_pst FROM tbl_Post a LEFT OUTER JOIN tbl_User b ON (a.userId_pst=b.id_usr) LEFT OUTER JOIN tbl_Department c ON (b.deptId_usr=c.id_dpt) WHERE c.id_dpt=4) AS A
GROUP BY A.ascNum_pst
I want also to use CActiveDataProvider in order to exploit the CGridView widget to display the aggregate functions results per ascNum. So the dataProvider for CGridView widget, should be a CActiveDataProvider in terms of a TblPost ActiveRecord object, since I want to display TblPost’s records.
What I have tried to do till now, is to exploit the Statistical Queries of TblUser AR to apply the aggregate functions that a want. In Particular, i created another one model TblUserReport, which is a copy of the comon model TblUser that Gii generated, with the only difference that the foreign key of the relation to the model TblPost is the ascNum_pst instead of the userId_pst. The reason is that as I have mentioned, statistical queries "group by" the foreign key of their relationship with the model of interest. The relations function of the TblUserReport model follows.
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'tblPosts' => array(self::HAS_MANY, 'TblPost', 'ascNum_pst'),
'countPosts' => array(self::STAT, 'TblPost', 'ascNum_pst'),
'averagePostsRate' => array(self::STAT, 'TblPost', 'ascNum_pst', 'select'=>'AVG(rate_pst)'),
);
}
However these statistical queries ignore the conditions that are set by the TblPost model (setting for example TblPost->userIdPstReport->userIdPst->deptId_usr=4, according to the previous MySQL query).
How could I associate Statistical Queries results with the constraints of the TblPost model? The crucial point according to my opinion is that the relationship between the TblUserReport model and the TblPost model, is made through the TblPost.ascNum_pst field (to serve me aggregation functions group by purposes) instead of the TblPost.userId_pst field, which is the real foreign key.
I hope that my problem is clear enough…
Thank you in advance for you time. Any suggestion or reply would be really appreciated!!!!