php 如何在 Yii 框架中使用复杂查询制定条件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4021890/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make criteria with complex query in Yii framework?
提问by aslingga
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
我已经尝试使用model()->findAllBySql($sql)并且它有效。但是我想使用它CDbCriteria，如果您有其他解决方案，请告诉我：D
回答by thaddeusmt
You could still build this statement with a CDbCriteria I think... something like:
您仍然可以使用我认为的 CDbCriteria 构建此语句……例如：
$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.
在这一点上，您不妨只编写一条常规 SQL 语句，但这样做可能有一些好处：绑定参数、合并条件、添加附加条件等。
回答by pestaa
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.
只要您的普通 SQL 有效，您就是安全的。很多时候我不得不扔掉 Active Record，然后以更理智的方式完成工作。
I tried to translate this query into a readable CDbCriteria construction. Bad idea. Yii sucks when it comes to query complex data.
我试图将这个查询翻译成一个可读的 CDbCriteria 结构。馊主意。Yii 在查询复杂数据时很糟糕。
回答by Philip Walton
The answer can be found here: http://www.yiiframework.com/doc/guide/1.1/en/database.dao#executing-sql-statements
答案可以在这里找到：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.
@pestaa 是对的，有时您必须将活动记录扔出窗外。如果您正在执行大量更新，其中循环遍历众多模型的效率非常低，则尤其如此。
回答by shark555
Just use CSqlDataProvider http://www.yiiframework.com/doc/api/1.1/CSqlDataProvider
只需使用 CSqlDataProvider http://www.yiiframework.com/doc/api/1.1/CSqlDataProvider
Disclaimer: I know it's not precise answer to this particural question but it might help work around the problem that was given. I suspect the main purpose of this question is getting way to use CGridView, CListView etc. with arbitrary SQL.
免责声明：我知道这不是对这个特定问题的准确答案，但它可能有助于解决所提出的问题。我怀疑这个问题的主要目的是通过任意 SQL 使用 CGridView、CListView 等。
回答by Mario
I use CDbCriteriafor complex queries in which I use the withfeature.
我CDbCriteria用于使用该with功能的复杂查询。
You can build complex criteria like this:
您可以像这样构建复杂的标准：
$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 have not checked how ORcan get into play here.
我还没有检查过OR这里怎么才能发挥作用。
By using scopes, you can also insert some more complex criteria and still keep your search condition readable.
通过使用范围，您还可以插入一些更复杂的条件，同时保持搜索条件的可读性。
This is pretty powerful. I didn't see a complete 'tutorial' about this yet; I kind of concluded this from the source code.
这是相当强大的。我还没有看到关于这个的完整“教程”；我从源代码中得出了这个结论。
