A JOIN With Additional Conditions Using Query Builder or Eloquent

$results = DB::table('rooms')                     ->distinct()                     ->leftJoin('bookings', function($join)                         {                             $join->on('rooms.id', '=', 'bookings.room_type_id');                             $join->on('arrival','>=',DB::raw("'2012-05-01'"));                             $join->on('arrival','<=',DB::raw("'2012-05-10'"));                             $join->on('departure','>=',DB::raw("'2012-05-01'"));                             $join->on('departure','<=',DB::raw("'2012-05-10'"));                         })                     ->where('bookings.room_type_id', '=', NULL)                     ->get();

Not quite sure if the between clause can be added to the join in laravel.

Notes:

• DB::raw() instructs Laravel not to put back quotes.
• By passing a closure to join methods you can add more join conditions to it, on() will add AND condition and orOn() will add OR condition.

You can replicate those brackets in the left join:

LEFT JOIN bookings                 ON rooms.id = bookings.room_type_id              AND (  bookings.arrival between ? and ?                  OR bookings.departure between ? and ? )

is

->leftJoin('bookings', function($join){    $join->on('rooms.id', '=', 'bookings.room_type_id');    $join->on(DB::raw('(  bookings.arrival between ? and ? OR bookings.departure between ? and ? )'), DB::raw(''), DB::raw(''));})

You'll then have to set the bindings later using "setBindings" as described in this SO post:How to bind parameters to a raw DB query in Laravel that's used on a model?

It's not pretty but it works.

If you have some params, you can do this.

    $results = DB::table('rooms')    ->distinct()    ->leftJoin('bookings', function($join) use ($param1, $param2)    {        $join->on('rooms.id', '=', 'bookings.room_type_id');        $join->on('arrival','=',DB::raw("'".$param1."'"));        $join->on('arrival','=',DB::raw("'".$param2."'"));    })    ->where('bookings.room_type_id', '=', NULL)    ->get();

and then return your query

return $results;