Complex CDbCriteria query

Yii 1.1 General Discussion
1

I have a complex sql query that I need to translate to a CDbCriteria query because I am passing the query results to a CGridView via CActiveDataProvider, but I haven’t been able to get the CDbCriteria right. My sql is:




select Rant.rantId, Rant.mp3, Rant.title, Rant.topicId, Rant.votes, Topic.extension, User.userId from Rant join (select Topic.topicId, Topic.extension from Topic order by Topic.startDate desc limit 1,2) as Topic ON Topic.topicId = Rant.topicId left join User on User.userId = Rant.userId

My CDbCriteria is structured as:




		$criteria=new CDbCriteria(array(

			'with'=>array('user', 'topic'),

			'together'=>true,

			'order'=>'topic.topicId desc, createDate desc',

			

		));

But I need to figure out how to get the subquery in there. Is it possible to use raw sql and still pass the results to a CGridView?

2

i dunno if it’s possible with CDbCriteria.

but you can try this(not tested)




$criteria->join='INNER JOIN (select Topic.topicId, Topic.extension from Topic order by Topic.startDate desc limit 1,2) as Topic ON Topic.topicId = Rant.topicId LEFT JOIN User on User.userId = Rant.userId';

or you can use CSqlDataProvider to solve this…