How to transform MySQL query to Laravel Eloquent query?

Question

Here's the Query:

SELECT * FROM `users` as m
left join `tutor_segments` as ts on m.id = ts.tutor_id
left join subject_segments as seg on ts.segment_id = seg.id 
left join users_area as ua on ua.user_id=m.id
left join area as a on ua.area_id=a.id
where m.user_type=2 and (seg.segment_name like '%s%'
or a.area_name like '%s%'  or m.area like '%s%') 
group by m.id;

how to build a laravel query with query builder for this part and (seg.segment_name like '%s%' or a.area_name like '%s%' or m.area like '%s%')

Jobin · Accepted Answer · 2015-12-01 05:24:23Z

1

Try this,

$users = DB::table('users')
    ->leftJoin('tutor_segments', 'users.id', '=', 'tutor_segments.tutor_id')
    ->leftJoin('subject_segments', 'tutor_segments.segment_id', '=', 'subject_segments.id')
    ->leftJoin('users_area', 'users_area.user_id', '=', 'users.id')
    ->leftJoin('area', 'user_area.area_id', '=', 'area.id')
    ->where('users.user_type', 2)
    ->where(function($query){
     $query->where('area.area_name', 'LIKE', '%s%')
        ->OrWhere('users.area_name', 'LIKE', '%s%')
        ->OrWhere('subject_segments.segment_name', 'LIKE','%s%');
     })       
    ->groupBy('users.id')
    ->get();

read the doc as well.

edited Dec 1, 2015 at 5:24

answered Dec 1, 2015 at 5:18

Jobin

8,2822 gold badges36 silver badges54 bronze badges

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

Comments

Anshul Gupta · Accepted Answer · 2015-12-01 05:27:35Z

0

You can write raw queries in laravel whereRaw()

$users = DB::table('users')
->leftJoin('tutor_segments', 'users.id', '=', 'tutor_segments.tutor_id')
->leftJoin('subject_segments', 'tutor_segments.segment_id', '=', 'subject_segments.id')
->leftJoin('users_area', 'users_area.user_id', '=', 'users.id')
->leftJoin('area', 'user_area.area_id', '=', 'area.id')
->whereRaw("users.user_type=2 and (subject_segments.segment_name like '%s%' or area.area_name like '%s%' or users.area like '%s%')")
->select('users.*')
->groupBy('users.id')
->get();

@Antony Thompson he need to select all data from users table so he need to use ->select('users.*') instead of ->select('*') and your query will generate error as you haven't created aliases for the selected tables.

answered Dec 1, 2015 at 5:27

Anshul Gupta

263 bronze badges

2 Comments

user5621153 Over a year ago

this is simple and i used this one , thanks for your valuable time to help me

Antony Thompson Over a year ago

@user1912870 Good to know, I just cooked up something after reading the laravel docs. I'm just about to start my first project with laravel today actually :-) and loving it so far.

Tim Biegeleisen · Accepted Answer · 2015-12-01 05:22:19Z

0

DB::table('users')
        ->leftJoin('tutor_segments', 'users.id', '=', 'tutor_segments.tutor_id')
        ->leftJoin('subject_segments', 'subject_segments.segment_id', '=', 'tutor_segments.tutor_id')
        ->leftJoin('users_area', 'users_area.user_id', '=', 'users.id')
        ->leftJoin('area', 'area.id', '=', 'users_area.area_id')
        ->where('users.user_type', 2)
        ->where(function($query){
            $query->where('subject_segments.segment_name', 'LIKE', '%s%')
            ->orWhere('area.area_name', 'like', '%s%')
            ->orWhere('users.area', 'like', '%s%')
           })
        ->groupBy('users.id')
        ->get();

answered Dec 1, 2015 at 5:22

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Comments

Antony Thompson · Accepted Answer · 2015-12-01 23:44:11Z

0

Maybe like this? I havn't used laravel much at all but according to the docs thats how you do it.

http://laravel.com/docs/4.2/eloquent

http://laravel.com/docs/5.1/queries

Code

$users = DB::table('users')
    ->leftJoin('tutor_segments', 'users.id', '=', 'tutor_segments.tutor_id')
    ->leftJoin('subject_segments', 'tutor_segments.segment_id', '=', 'subject_segments.id')
    ->leftJoin('users_area', 'users_area.user_id', '=', 'users.id')
    ->leftJoin('area', 'user_area.area_id', '=', 'area.id')
    ->select('users.*')
    ->whereRaw(
        'users.user_type=2 and 
        (
            subject_segments.segment_name like ?
            or 
            area.area_name like ?  or users.area like ?
        )', array('%s%', '%s%', '%s%')
    )
    ->groupBy('users.id')
    ->get();

edited Dec 1, 2015 at 23:44

answered Dec 1, 2015 at 5:12

Antony Thompson

1,6381 gold badge13 silver badges22 bronze badges

1 Comment

user5621153 Over a year ago

thank you very much for your responce , will try this

Collectives™ on Stack Overflow

How to transform MySQL query to Laravel Eloquent query?

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related