A JOIN With Additional Conditions Using Query Builder or Eloquent

Question

I'm trying to add a condition using a JOIN query with Laravel Query Builder.

<?php  $results = DB::select('        SELECT DISTINCT            *           FROM               rooms                  LEFT JOIN bookings                      ON rooms.id = bookings.room_type_id                   AND (  bookings.arrival between ? and ?                       OR bookings.departure between ? and ? )           WHERE                 bookings.room_type_id IS NULL           LIMIT 20',     array('2012-05-01', '2012-05-10', '2012-05-01', '2012-05-10') ); 

I know I can use Raw Expressions but then there will be SQL injection points. I've tried the following with Query Builder but the generated query (and obviously, query results) aren't what I intended:

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

This is the generated query by Laravel:

select distinct * from `room_type_info`     left join `bookings`  on `room_type_info`.`id` = `bookings`.`room_type_id`  where `arrival` between ? and ?      and `departure` between ? and ?      and `bookings`.`room_type_id` is null 

As you can see, the query output doesn't have the structure (especially under JOIN scope). Is it possible to add additional conditions under the JOIN?

How can I build the same query using Laravel's Query Builder (if possible) Is it better to use Eloquent, or should stay with DB::select?

Answer 1

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

Answer 2

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.