mysql query with Yii query builder

Question

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 )

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

Answer 1

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

Start here: 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();

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.