Select inside select in CDbCriteria

How can i recreate this sql query with CDbCriteria to use with CActiveDataProvider?


FROM messages AS message

WHERE message.create_time = (

SELECT MAX( create_time )

FROM messages AS message2

WHERE message2.subject = message.subject )

ORDER BY message.create_time DESC

Any help would be appreciated :D

Did you try to put the nested query as is into the $criteria->where property?


No, I didn’t try that, I figured there was a way without parsing sql syntax as the criteria?

No, there’s no support for subqueries that I’m aware of. It seems like you don’t need to access the content of the subquery from AR so it may work well in your case. (IIRC I once tried a subquery in $criteria->select, but not in $criteria->where)

Edit: Of course it is $criteria->condition, not $criteria->where.


Ok, thanks guys… atleast you got me in the right direction, I ended up using a criteria condition like this.

$criteria->condition='t.create_time = (SELECT MAX( create_time ) FROM messages AS message WHERE message.subject = t.subject )';

If there is anyone with a better/more correct way to do it, please let me know :D


you can also use CSqlDataProvider instead of CActiveDataProvider