CODEWITHSUNDEEP
sqllaravellaravel-5.5
artfulrobot
artfulrobot

Reputation: 21437

Laravel query builder join with raw ON expression

I'm trying to achieve something like the following simplified example in Laravel 5.5 Query builder:

SELECT *
FROM t
INNER JOIN access
  ON (t.field1 = access.field1 OR t.field2 = access.field2)
     AND access.user_id = :user_id

Note that the ON has a compound expression on multiple fields and also uses a parameter.

I'm really struggling to get the ON statement to work. I've tried every combination of join() and DB:raw() I can think of.

How can this be achieved with Laravel's query builder?

Upvotes: 3

Views: 4191

Answers (1)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25936

DB::table('t')->join('access', function($query) use($user_id) {
    $query->where(function($query) {
        $query->on('t.field1', '=', 'access.field1')
            ->orOn('t.field2', '=', 'access.path_field2');
    })->where('access.user_id', '=', $user_id);
});

The ->where('access.user_id', '=', $user_id) condition doesn't have to be inside the JOIN closure. I just put it there to exactly match your query. Placing it outside simplifies the code:

DB::table('t')->join('access', function($query) {
    $query->on('t.field1', '=', 'access.field1')
        ->orOn('t.field2', '=', 'access.path_field2');
})->where('access.user_id', '=', $user_id);

Upvotes: 5

Related Questions