A query joining three yii tables

Question

How to convert mysql query to yii.?

I have 3 tables

• user_header
• of customers
• customer_ratings

and this is my sql request

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 request to yii?

Thanks in advance.

Answer 1

"itachi" answers correctly, but if you are looking in an 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 code below returns MODEL objects just like your request. (I have not tested it)

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

Answer 2

USE DAO (data access object)

MODEL

---

 public function test(){ $sql = "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 )"; return Yii::app()->db->createCommand($sql)->queryAll(); }