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

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.

Answer 2

The answer can be found here: http://www.yiiframework.com/doc/guide/1.1/en/database.dao#executing-sql-statements

In your case:

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

$connection = Yii::app()->db;
$command = $connection->createCommand($sql);
$results = $command->queryAll();

@pestaa is right that sometimes you have to throw active record out the window. This is especially true if you're doing mass updates where looping through numerous models is horribly inefficient.

Answer 3

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.