Geek Answers Handbook

How to make criteria with complex query in Yii structure?

I have a request:

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 try to use model()->findAllBySql($sql)and it works. But I want to do this using CDbCriteriaif you have other solutions let me know: D

+5
source share
5 answers

You can still build this statement with 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 can simply write a regular SQL query, but there may be some advantages to this: parameter binding, merging criteria, adding additional criteria, etc.

+7
source

SQL , . , Active Record -.

CDbCriteria. . Yii , .

+6

: http://www.yiiframework.com/doc/guide/1.1/en/database.dao#executing-sql-statements

:

$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 , . , , .

+1

CSqlDataProvider http://www.yiiframework.com/doc/api/1.1/CSqlDataProvider

: , , , . , - CGridView, CListView .. SQL.

0

I use CDbCriteriafor complex queries in which I use a function with.

You can create complex criteria as follows:

$dbCriteria->with=array(
   '<relation1>'=>array( 'condition'=>'<condition for given relation>',
        'with'=>array('<relation2:relation of relation1>'
            =>array(<conditions for relation2>)
        )
        'scopes'=><scopes for relation1>
    )
);

I did not check how it ORcan enter the game here.

Using scope, you can also insert some more complex criteria and still keep your search terms readable.

It is quite effective. I have not seen a complete “textbook” about this; I somehow concluded this from the source code.

0
source

More articles:


All Articles