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.

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;

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.

The sql query sample like this

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

Laravel join with multiple conditions

->leftJoin('bookings', function($join) use ($param1, $param2) {
    $join->on('rooms.id', '=', 'bookings.room_type_id');
    $join->on(function($query) use ($param1, $param2) {
        $query->on('bookings.arrival', '=', $param1);
        $query->orOn('departure', '=',$param2);
    });
})

I am using laravel5.2 and we can add joins with different options, you can modify as per your requirement.

Option 1:    
    DB::table('users')
            ->join('contacts', function ($join) {
                $join->on('users.id', '=', 'contacts.user_id')->orOn(...);//you add more joins here
            })// and you add more joins here
        ->get();

Option 2:
    $users = DB::table('users')
        ->join('contacts', 'users.id', '=', 'contacts.user_id')
        ->join('orders', 'users.id', '=', 'orders.user_id')// you may add more joins
        ->select('users.*', 'contacts.phone', 'orders.price')
        ->get();

option 3:
    $users = DB::table('users')
        ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
        ->leftJoin('...', '...', '...', '...')// you may add more joins
        ->get();

There's a difference between the raw queries and standard selects (between the DB::raw and DB::select methods).

You can do what you want using a DB::select and simply dropping in the ? placeholder much like you do with prepared statements (it's actually what it's doing).

A small example:

$results = DB::select('SELECT * FROM user WHERE username=?', ['jason']);

The second parameter is an array of values that will be used to replace the placeholders in the query from left to right.

For conditional params we can use where,

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

My five cents for scheme LEFT JOIN ON (.. or ..) and (.. or ..) and ..

    ->join('checks','checks.id','check_id')
    ->leftJoin('schema_risks', function (JoinClause $join) use($order_type_id, $check_group_id,  $filial_id){
        $join->on(function($join){
            $join->on('schema_risks.check_method_id','=', 'check_id')
                ->orWhereNull('schema_risks.check_method_id')
                ;
        })
        ->on(function($join) use ($order_type_id) {
            $join->where('schema_risks.order_type_id', $order_type_id)
                ->orWhereNull('schema_risks.order_type_id')
                ;
        })
        ->on(function($join) use ($check_group_id) {
            $join->where('schema_risks.check_group_id', $check_group_id)
                ->orWhereNull('schema_risks.check_group_id')
                ;
        })
        ->on(function($join) use($filial_id){
            $join->whereNull('schema_risks.filial_id');
            if ($filial_id){
                $join->orWhere('schema_risks.filial_id', $filial_id);
            }
        })            
        ->on(function($join){
            $join->whereNull('schema_risks.check_risk_level_id')
                ->orWhere('schema_risks.check_risk_level_id', '>' , CheckRiskLevel::CRL_NORMALLLY );
        })
        ;
    })