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

Solution 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.

Solution 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.

Solution 3

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.

Comments

[Andrzej Ośmiałowski]

A bit old question but I need to comment. When dealing with complex queries DO NOT use any ORM. You have to deal with pure SQL (AR provides a findAllBySql() method).

[aslingga]

Thank you @philip for your solution but now I want to use the CDbCriteria in my Active Record. I have try to use the sql and execute using Activity::model()->findAllBySql($sql) and I think it will give the same result as using SQL Comamnd. :D

[aslingga]

Thank you @pestaa, I hope Yii will give a solution for complex query :D

[Philip Walton]

@aslingga, is your question about using CDbCriteria and how to form such a statement? If so, let me know and maybe I can help. One of the advantages of CDbCriteria is it incorporates bound parameters, which is essential in preventing SQL injection if you're querying with user input. I haven't tried your statement above, but I see no reason why it wouldn't work.

[pestaa]

@aslingga It is fundamentally "misdesigned". I'm not saying I can do better, though.

[aslingga]

I want to combine it with CPanigation in Yii, is there any way to make it possible?

[realtebo]

can you add an example? i can't understand how to clone this in real life

[Tebe]

a bit later, but 5 years later, what framework would you recommend then?

[pestaa]

@A.Shulzhenko Within the realms of PHP, there is zero point in using anything other than Doctrine.