CODEWITHSUNDEEP
phpmysqllaravel
user5621153
user5621153

Reputation:

How to transform MySQL query to Laravel Eloquent query?

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

Upvotes: 1

Views: 232

Answers (4)

Antony Thompson
Antony Thompson

Reputation: 1638

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

Upvotes: 0

Anshul Gupta
Anshul Gupta

Reputation: 26

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.

Upvotes: 0

Jobin
Jobin

Reputation: 8282

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.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

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

Upvotes: 0

Related Questions