php - SQL - Yii - If one of foreign entries matches sort 1 if none sort 2 -

i have table place :

id | name 

and table place_hours_opening :

id | day | place_id | time_start | time_end | time_start2 | time_end2 

nb : place 1 -> n place_hours_openging

in order order places (thanks orderby) open right know before place open later (in chronological order) , because others params come after.. attribute 1 if place opened right , 2 if not.

currently have :

  $criteria = new cdbcriteria();         $criteria->with = array('placehoursopenings');         $criteria->together = true;         $criteria->select = array(          "case             when (placehoursopenings.day = $yesterday ,                 ($ajdstart > $ajdend , $hierfirst <= '$date' , '$date' <= $ajdend )) 1             when (placehoursopenings.day = $yesterday ,                 ($ajdstart2 > $ajdend2 , $hiersecond <= '$date' , '$date' <= $ajdend2) ) 1             when (placehoursopenings.day = $day ,                 ($ajdstart > $ajdend , $ajdstart <= '$date' , '$date' <= $demend )) 1             when (placehoursopenings.day = $day ,                 ($ajdstart < $ajdend , $ajdstart <= '$date' , '$date' <= $ajdend )) 1             when (placehoursopenings.day = $day ,                 ($ajdstart2 > $ajdend2 , $ajdstart2 <= '$date' , '$date' <= $demend2 )) 1             when (placehoursopenings.day = $day ,                 ($ajdstart2 < $ajdend2 , $ajdstart2 <= '$date' , '$date' <= $ajdend2 )) 1                 else 2                 end opennow");         $criteria->condition = "t.id = placehoursopenings.place_id"; 

the problem place has several openings , although 1 returns 1 if next returns 2 erases previous one..

in mind, logical, think foreach "foreach openings place if 1 of next conditions returns 1 ( = true), attributes 1 entire , unique place".

i think exists if not couldn't attribute 2 , still want place.

how can improve statement ?

edit second try :

i try :

case             when open = 1 1                 else 2                 end opennow                 (             select count(*) open place_hours_opening po               po.place_id = t.id               , (               (po.day = $yesterday ,                 $ajdstart > $ajdend , $hierfirst <= '$date' , '$date' <= $ajdend )               or (po.day = $yesterday ,                 $ajdstart2 > $ajdend2 , $hiersecond <= '$date' , '$date' <= $ajdend2)               or (po.day = $day ,                 $ajdstart > $ajdend , $ajdstart <= '$date' , '$date' <= $demend )               or (po.day = $day ,                 $ajdstart < $ajdend , $ajdstart <= '$date' , '$date' <= $ajdend )               or (po.day = $day ,                 $ajdstart2 > $ajdend2 , $ajdstart2 <= '$date' , '$date' <= $demend2 )               or(po.day = $day ,                 $ajdstart2 < $ajdend2 , $ajdstart2 <= '$date' , '$date' <= $ajdend2 )               )               ) opalias 

but got generical error :

syntax error or access violation: 1064 have error in sql syntax; check manual corresponds mysql server version right syntax use near ' `placehoursopenings`.`id` 

maybe forgot comma ? mistaken somewhere ?

i found solution works me.

if can else :

$criteria = new cdbcriteria();         $criteria->with = array('placehoursopenings');         $criteria->together = true;         $criteria->select = array(         "case             when (             select count(*) open place_hours_opening po               po.place_id = t.id                , (               (po.day = $yesterday ,                 $ajdstart > $ajdend , $hierfirst <= '$date' , '$date' <= $ajdend )               or (po.day = $yesterday , po.time_start2 not null ,                 $ajdstart2 > $ajdend2 , $hiersecond <= '$date' , '$date' <= $ajdend2)               or (po.day = $day ,                 $ajdstart > $ajdend , $ajdstart <= '$date' , '$date' <= $demend )               or (po.day = $day ,                 $ajdstart < $ajdend , $ajdstart <= '$date' , '$date' <= $ajdend )               or (po.day = $day , po.time_start2 not null ,                 $ajdstart2 > $ajdend2 , $ajdstart2 <= '$date' , '$date' <= $demend2 )               or(po.day = $day , po.time_start2 not null ,                 $ajdstart2 < $ajdend2 , $ajdstart2 <= '$date' , '$date' <= $ajdend2 )               )               ) = 1 1                 else 2                 end opennow         ");         $criteria->condition = "t.id = placehoursopenings.place_id";          if($sort == 2) {             $criteria->order = "opennow asc";         }           $this->getdbcriteria()->mergewith($criteria);          return $this; 

other things take account :

• i must use $criteria->select = array("something", "else") instead of $criteria->select = "something, else") because use 2 sql case in select (not presents in above sample code clearness). without array second case interpreted column name.

• i formate dates cdbexpression , str_to_string :

   $ajdstart = new cdbexpression("str_to_date(concat('$todaydate', po.time_start),'%y-%m-%d %h:%i:%s')"); 

hope helps.