SELECT WHERE WHERE COUNT from 4 relation tables

Yii 1.1 Tips, Snippets and Tutorials
1

Hello.

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.

2

Our Query




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

                        ->select('id')

                        ->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))

                        ->order('id')

                        ->limit('1')

                        ->queryRow();
3

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