How to make criteria with complex query in Yii framework?

Question

I have query like this:

SELECT * FROM activity
WHERE (((userId = 1 OR userId IN(SELECT userId FROM follower WHERE followerId = 1))
AND activityType IN(1, 2, 3))
OR (targetId = 24 AND aType IN(1, 2, 3, 4, 5)))
ORDER BY id DESC;

I have try to use model()->findAllBySql($sql) and it works. But I want to make it using CDbCriteria, if you have another solutions let me know it :D

Answer 1

You could still build this statement with a CDbCriteria I think... something like:

$criteria=new CDbCriteria;
$criteria->condition = '
  (
    (
      userId = 1 OR 
      userId IN (SELECT userId FROM follower WHERE followerId = 1)
    )
    AND activityType IN(1, 2, 3)
  )
  OR (
    targetId = 24 
    AND aType IN(1, 2, 3, 4, 5)
  )
';
$criteria->order = 'id DESC';
$results=Activity::model()->findAll($criteria);

As this point you might as well just write a regular SQL statement, but there might be some benefits to doing it this way: binding params, merging criteria, adding additional criteria, etc.

Answer 2

As long as your plain SQL works, you're safe. There are many times when I have to throw Active Record away and just get the job done in the ol' saner way.

I tried to translate this query into a readable CDbCriteria construction. Bad idea. Yii sucks when it comes to query complex data.