CODEWITHSUNDEEP
laravellaravel-8
Shimul
Shimul

Reputation: 468

Laravel 8 - Left Outer Join with multiple conditions

In a certain portion of my Laravel apps, I need to fetch data using Left outer join. If the join requires only one condition then I can easily handle this (by adding left outer as a param in the Laravel join clause), but I need to use two conditions in the left outer join. So far I write the following query:

$events = DB::table('events AS ev')
                    ->join('event_registrations AS er', function ($join) {
                        $join->on('ev.id', '=', 'er.event_id')
                            ->where('er.status', '=', 'confirmed');
                    })
                    ->select('ev.id', 'ev.event_name', 'ev.event_link', 'ev.description', 'ev.total_tickets', 'ev.logo_path', DB::raw("IFNULL( count(er.id), 0 ) as total_confirmed"))
                    ->groupByRaw("ev.id, ev.event_name, ev.event_link, ev.description, ev.total_tickets, ev.logo_path, ev.total_tickets")
                    ->get();

Which creates an inner join query. I have tried to add left outer as the following way:

$events = DB::table('events AS ev')
                    ->join('event_registrations AS er', function ($join) {
                        $join->on('ev.id', '=', 'er.event_id')
                            ->where('er.status', '=', 'confirmed');
                    }, 'left outer')
                    ->select('ev.id', 'ev.event_name', 'ev.event_link', 'ev.description', 'ev.total_tickets', 'ev.logo_path', DB::raw("IFNULL( count(er.id), 0 ) as total_confirmed"))
                    ->groupByRaw("ev.id, ev.event_name, ev.event_link, ev.description, ev.total_tickets, ev.logo_path, ev.total_tickets")
                    ->get();

But it still produces inner join.
Does anyone know how to create a left outer join query using multiple conditions in Laravel?

Upvotes: 0

Views: 1186

Answers (1)

Anurat Chapanond
Anurat Chapanond

Reputation: 2987

If you look at the source code at Illuminate\Database\Query\Builder.php
The join method is defined like this.

/**
 * Add a join clause to the query.
 *
 * @param  string  $table
 * @param  \Closure|string  $first
 * @param  string|null  $operator
 * @param  string|null  $second
 * @param  string  $type
 * @param  bool  $where
 * @return $this
 */
public function join($table, $first, $operator = null, $second = null, $type = 'inner', $where = false)

So type is actually the fifth parameter then your join should be

->join('event_registrations AS er', function ($join) {}, null, null, 'left outer')

Upvotes: 1

Related Questions