How can i recreate this sql query with CDbCriteria to use with CActiveDataProvider?
SELECT *
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 
Did you try to put the nested query as is into the $criteria->where property?
/Tommy
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.
/Tommy
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
Hi,
you can also use CSqlDataProvider instead of CActiveDataProvider