Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
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')
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.