Laravel 8 - Left Outer Join with multiple conditions

Question

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?

you could just use leftJoin in the same way as you'd use join — apokryfos
– apokryfos, Commented Feb 8, 2021 at 7:43

Anurat Chapanond · Accepted Answer · 2021-02-08 06:14:56Z

1

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')

answered Feb 8, 2021 at 6:14

Anurat Chapanond

2,9873 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel 8 - Left Outer Join with multiple conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related