SELECT WHERE WHERE COUNT from 4 relation tables


I will explain you our project issue:

We Have 4 Relation Tables:

User: id, etc.

Services: id, user_id, etc.

Workdays: id, service_id, workday_id(Weeks - id, weekdayname, etc.)

Workhours: id, Service_id, start_id, end_id(Hours - id, hour, etc.)

Our Task:

Check if the user has at least one service, which open 24 hours 7 days per week!

We have solved this problem and share with you.

Our Query

$as247 = Yii::app()->db->createCommand()


                        ->from('tbl_services asv')

                        ->where('user_id=:user_id AND (SELECT count(*) FROM `tbl_workdays` wdv

                                WHERE `asv`.`id` = `wdv`.`service_id`) = 7 AND (SELECT `id` from `tbl_workhours` whv

                                WHERE `whv`.`start_id`=1 AND `whv`.`end_id`=25 AND `whv`.`service_id` = `asv`.`id`)', array(':user_id'=>$id))




[color="#006400"]NOTE: moved to proper section (from General Discussion for Yii 1.1.x)[/color]