query joining three tables yii

Question

How to convert mysql query to yii.?

I have 3 table

1. user_header
2. customers
3. customer_ratings

and this is my sql query

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) 

How to convert this query to yii ?

Thanks in advance.

Answer 1

"itachi" answer's right, but if you are looking for in activerecord way...

model: UserHeader

relations:

'activeCustomers' => array(
     self::HAS_MANY, 
     'Customer', 
     'customer_user_id', 
     'condition' => 'activeCustomers.rate_auto_approve=0 
                  AND activeCustomers.rate_email_time IS NOT NULL 
                  AND activeCustomers.total_rating_count IS NOT NULL'
),

model: Customer

relations:

'lastWeekRatings' => array(
     self::HAS_MANY, 
     'CustomerRating', 
     'customer_user_id', 
     'condition' => 'lastWeekRatings.rating_date < CURDATE() 
        AND lastWeekRatings.rating_date > DATE_SUB( CURDATE(), INTERVAL 7 DAY )'
),

and the below code returns the MODEL objects as same as your query. ( I haven't tested it )

$useremails = UserHeader::model()
     ->with('activeCustomers', 'activeCustomers.lastWeekRatings')
     ->findAll(array(
           'select' => 't.email'
      ));
print_r($useremails);