使用 Yii 查询构建器的 mysql 查询

2022-01-04 00:00:00 php mysql yii

有什么方法可以在 Yii 查询生成器中编写以下查询

Hi is there any way to write below query in Yii query builder

        SELECT t.email
        FROM otz_user_header t
        JOIN otz_customers r ON t.user_id = r.customer_user_id
        JOIN otz_customer_ratings cr ON cr.customer_user_id = r.customer_user_id
        WHERE r.rate_auto_approve =0
        AND r.rate_email_time IS NOT NULL
        AND r.total_rating_count IS NOT NULL
        AND cr.rating_date < CURDATE( )
        AND cr.rating_date >DATE_SUB( CURDATE( ) , INTERVAL 7
        DAY )

我已经使用 Yii DAO 方法编写了上述查询，但我希望将此查询与查询构建器相匹配，这可能吗?

i have written above query using Yii DAO method but i am looking to fit this query with query builder, is this possible ?

推荐答案

是的，确实如此.:-) 哦，你的意思是，你是怎么做到的?;-)

Yep, sure is. :-) Oh, you mean, how do you do it? ;-)

从这里开始:http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder

$emails = Yii::app()->db->createCommand()
    ->select('t.email')
    ->from('otz_user_header t')
    ->join('otz_customers r', 't.user_id = r.customer_user_id')
    … // some of this left out as an exercise for the reader
    ->where(
        array('and','r.rate_auto_approve=0'
            … // more left out here
            array('and',new CDbExpression('cr.rating_date < CURDATE()')),
        ),
    ),
    ->queryAll();

CDbExpression 项目尚未经过测试，但其余的应该可以正常工作.注意:查看有关 where 语法的文档，它有点棘手.本质上，每个 AND/OR 都成为嵌套在原始 where 数组中的另一个数组行.

The CDbExpression item hasn't been tested, but the rest should work fine. Note: take a look at the documentation on where syntax, it gets a bit tricky. In essence, every AND/OR becomes another array line nested inside your original where array.

相关文章