Yii Framework Forum: SELECT WHERE WHERE COUNT from 4 relation tables - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

SELECT WHERE WHERE COUNT from 4 relation tables user, service, workdays, workhours

#1 User is offline   iSystems 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 19-July 14
  • Location:Russia - Moscow, Armenia - Yerevan

Posted 18 May 2015 - 08:55 AM

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.
0

#2 User is offline   iSystems 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 19-July 14
  • Location:Russia - Moscow, Armenia - Yerevan

Posted 18 May 2015 - 08:57 AM

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

0

#3 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,542
  • Joined: 12-October 09
  • Location:Croatia

Posted 19 May 2015 - 03:38 AM

NOTE: moved to proper section (from General Discussion for Yii 1.1.x)
Find more about me.... btw. Do you know your WAN IP?
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users