How to set double level depth or where clause

user_7646114 0
Viewed 26

My query is currently like this:

    $erwachsene = $search_data['erwachsene'];
            $kinder = $search_data['kinder'];
            $rooms = Room::select('rooms.*')
            ->where('hotel_id', '=', $id)
            ->where('room_status', '=', 1)
            ->where(function($query) use ($erwachsene, $kinder) {
                $query->where("occupancy_adults1", ">=", $erwachsene);
                $query->where("occupancy_childs1", ">=", $kinder);
            })
            ->orWhere(function($query) use ($erwachsene, $kinder) {
                $query->where("occupancy_adults2", ">=", $erwachsene);
                $query->where("occupancy_childs2", ">=", $kinder);  
            })
            ->get();

This return query like this:

    SELECT `rooms`.* 
    FROM   `rooms` 
    WHERE  `hotel_id` = ? 
    AND `room_status` = ? 
    AND ( `occupancy_adults1` >= ? 
    AND `occupancy_childs1` >= ? ) 
    OR ( `occupancy_adults2` >= ? 
    AND `occupancy_childs2` >= ? )

I need to put the last AND and OR clauses under OR group:

SELECT `rooms`.* 
    FROM   `rooms` 
    WHERE  `hotel_id` = ? 
    AND `room_status` = ? 
    AND 
    (
          ( `occupancy_adults1` >= ? AND `occupancy_childs1` >= ? ) 
       OR ( `occupancy_adults2` >= ? AND `occupancy_childs2` >= ? ) 
    )

E.g. one more level parentheses. Is it possible with the Eloquent functions ?

laravel
1 Answers

You must cascade your where calls to get the expected SQL:

// ...
->where(function($query) use ($erwachsene, $kinder) {           // (
    $query->where(function($query) use ($erwachsene, $kinder) { //  (
        $query->where("occupancy_adults1", ">=", $erwachsene);  //    `occupancy_adults1` >= ?
        $query->where("occupancy_childs1", ">=", $kinder);      //   AND `occupancy_childs1` >= ?
    })->orWhere(function($query) use ($erwachsene, $kinder) {   //  ) OR (
        $query->where("occupancy_adults2", ">=", $erwachsene);  //   `occupancy_adults2` >= ?
        $query->where("occupancy_childs2", ">=", $kinder);      //   AND  `occupancy_childs2` >= ?
    });                                                         //  )
});                                                             // )

The first where level will create the outter parenthesis, the second where level will create the OR, and the third will create the inner AND.

user_34293230
Related