[laravel] A JOIN With Additional Conditions Using Query Builder or Eloquent

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


$results = DB::select('
                LEFT JOIN bookings  
                   ON rooms.id = bookings.room_type_id
                  AND (  bookings.arrival between ? and ?
                      OR bookings.departure between ? and ? )
                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')
    ->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)

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?

This question is related to laravel laravel-4 laravel-query-builder

The answer is

$results = DB::table('rooms')
                     ->leftJoin('bookings', function($join)
                             $join->on('rooms.id', '=', 'bookings.room_type_id');
                     ->where('bookings.room_type_id', '=', NULL)

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


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

Similar questions with laravel tag:

Similar questions with laravel-4 tag:

Similar questions with laravel-query-builder tag: