Convert mysql query into Eloquent laravel query

Question

I am trying to make the following query in laravel:

SELECT a.name AS subname, a.area_id, b.name,  u. id, u.lawyer_id,u.active_search, 

                            FROM subarea a
                            LEFT JOIN user_subarea u ON u.subarea_id = a.id
                            AND u.user_id = ?
                            LEFT JOIN branch b ON a.area_id = b.id

The idea is to obtain the subareas and see if the search is activated by the user.

The user_subarea table might have a record that matches the id of the subarea table where the active_search is equal to 0 or 1. If it doesn't exist I would like the query to return null.

While I was able to achieve this in raw SQL when I try the same with eloquent in Laravel I am not returning any value. I have done the following:

$query = DB::table('subarea')

            ->join('user_subarea', function($join)
        {
            $value = \Auth::user()->id;
            $join->on( 'subarea.id', '=', 'user_subarea.subarea_id')->where('user_subarea.user_id', '=',$value);
        })
            ->leftJoin('branch', 'subarea.area_id', '=', 'branch.id')
            ->select('branch.name', 'subarea.name as subarea', 'user_subarea.active_search_lawyer', 'user_subarea.id' )

            ->get();

Any help will be much appreciated.

Answer 1

Try this one, If you get a problem, please comment.

$value = \Auth::user()->id;

$query = DB::table('subarea')
             ->where('user_subarea.user_id', '=',$value)
             ->leftJoin('user_subarea', 'subarea.id', '=', 'user_subarea.subarea_id')
             ->leftJoin('branch', 'subarea.area_id', '=', 'branch.id')
             ->select('subarea.name AS subname','subarea.area_id', 'branch.name', 'user_subarea.id','user_subarea.lawyer_id','user_subarea.active_search')
             ->get();

Answer 2

I found by myself the answer it was just to add a lefjoin in the first join. It is not in the laravel docs but works too.

$query = DB::table('subarea')

            ->lefjoin('user_subarea', function($join)
        {
            $value = \Auth::user()->id;
            $join->on( 'subarea.id', '=', 'user_subarea.subarea_id')->where('user_subarea.user_id', '=',$value);
        })
            ->leftJoin('branch', 'subarea.area_id', '=', 'branch.id')
            ->select('branch.name', 'subarea.name as subarea', 'user_subarea.active_search_lawyer', 'user_subarea.id' )

            ->get();