Cactivedataprovider - Cdbcriteria - Select Sub Query Implementation

I have SQL -


SELECT User.id, User.firstname, Pay.id, Pay.enddate

FROM User, (select id, userid, max(enddate) as enddate from payment group by userid) as Pay

WHERE User.id = Pay.userid

I have CRUD for User, I want to add ENDDATE column of the Payment table to the ADMIN/MANAGE USER option, So how to modify CDbCriteria & CActiveDataProvider for that.

I tried some of the Methods & Parameter for CDbCriteria but no results.

Thanks.

may be it can help you … with few changes you can have the things you want…

i have assumed user has multiple payments and payment and user mnodel has relation between them and payment class has relation user_payment in its model


 'user_payment'=>array(self::BELONGS_TO, 'User', 'id'),


Payment::model()->with('user_payment')->findAll(array('select'=>'id,enddate,user_payment.id,user_payment.firstname',condition'=>'userid = :userid','params'=>array(':userid'=>$userId)));