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?

Abishek · Answer 1 · 2019-06-10T16:51:33.207

228

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

edited Jun 10 '19 at 16:51

answered May 31 '13 at 03:18

Abishek

11,191
19
72
111

Thanks for the answer. Unfortunately, the generated query is slightly different since the join condition now have **`and arrival >= ? and arrival <= ? and departure >= ? and departure <= ?`** instead **`AND ( r.arrival between ? and ? OR r.departure between ? and ? )`** (please notice the **`OR`** clause). This adds additional rows to result that shouldn't be there. I guess it's not possible to generate all types of conditions in join using QB. – dede May 31 '13 at 12:00
1

Actually I noticed that the ? is not added into the ON clauses. Those values in the ON are not parameterized and not protected from SQL injection. I've posted a [question](http://stackoverflow.com/q/17733978/1110941) trying to figure out the solution. – prograhammer Jul 18 '13 at 22:26
3

this didn't answer the original question which had an or clause that was ignored in this response. – ionescho May 20 '15 at 11:38
Perfect solution. Just remember to use DB::raw when using a value, otherwise Laravel (at least in 5.6.29) add back quotes and "break" the goal. – Adrian Hernandez-Lopez Sep 26 '18 at 07:43

score 41 · Answer 2 · answered Jun 17 '14 at 15:35

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;

in case of conditions on join you can use $join->where() inside the left join instead of DB::raw — TTT, Jul 05 '21 at 05:41

rickywiens · Answer 3 · 2017-11-18T05:09:56.867

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.

score 29 · Answer 4 · edited Jul 11 '18 at 04:31

29

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);
    });
})

edited Jul 11 '18 at 04:31

eaglebeagle2

98
6

answered Aug 01 '16 at 11:45

Majbah Habib

8,058
3
36
38

Used this structure to build my queries that require multiple 'on' clauses, but does laravel has a specific command for those situations? like using the eager loading with multiple 'on' conditions? – Daniel Saito Oct 13 '22 at 03:46

Abid Ali · Answer 5 · 2016-08-03T07:41:25.053

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();

score 8 · Answer 6 · answered Mar 04 '22 at 09:24

8

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();

answered Mar 04 '22 at 09:24

Jaydeep Mor

1,690
3
21
39

In my case I chose to chain ->on as where was creating a binding where I needed a table reference. – Hobbes Jun 18 '23 at 01:36

score 3 · Answer 7 · answered May 31 '13 at 08:45

3

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.

answered May 31 '13 at 08:45

Jason Lewis

18,537
4
61
64

Does that mean that parameters are automatically escaped (save from SQL injection)? – dede May 31 '13 at 12:06
2

Yes, it's just a wrapper for PDO prepared statements. – Jason Lewis May 31 '13 at 13:29
Is there any way to use that technique in an ON clause in a left join? See my question [here](http://stackoverflow.com/q/17733978/1110941). I tried inside my leftJoin closure to do `$join->on('winners.year','=',DB::select('?',array('2013'));` but didn't work. – prograhammer Jul 18 '13 at 22:37
that's not really how eloquent is designed to operate. you might as well just do a DB::raw() and make it yourself – mydoglixu Jun 09 '15 at 16:09

score 2 · Answer 8 · answered Mar 01 '21 at 12:08

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 );
        })
        ;
    })

score 0 · Answer 9 · answered Jun 15 '23 at 19:58

Hint from newer version:

$rooms = Room::select('rooms.*', 'bookings.type3')
        ->join('bookings', function (JoinClause $join) {
            $join->on(['bookings.cat1' => 'rooms.cat1', 'bookings.cat2' => 'rooms.cat2']);
        })->get();;

Instead of uses two on-condition(AND-Conditions) you also can use orOn()-Conditions within all its functions as asBetween,...

A JOIN With Additional Conditions Using Query Builder or Eloquent

9 Answers9

Linked