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.

lostintheriver · Accepted Answer · 2016-11-27 15:58:22Z

1

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();

answered Nov 27, 2016 at 15:58

lostintheriver

1971 gold badge1 silver badge12 bronze badges

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

Comments

Sachith Muhandiram · Accepted Answer · 2016-11-27 13:44:37Z

0

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();

answered Nov 27, 2016 at 13:44

Sachith Muhandiram

2,97811 gold badges54 silver badges113 bronze badges

2 Comments

lostintheriver Over a year ago

Sachith, thank you for your answer but is not returning exactly what. Anyway, I found the answer by myself already:

Sachith Muhandiram Over a year ago

@lostintheriver good, please post your answer. it will help others who would have the same scenario

Collectives™ on Stack Overflow

Convert mysql query into Eloquent laravel query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related