laravel mysql query with multiple where orwhere and inner join

laravel, mysql, php

Solution

Okay it looks like your problem is with the nested where

Try this one:

$name = $_POST['name']; // I'd recommend you use Input::get('name') instead...

$user_list = DB::table('users')
    ->where('users.user_id' ,'=', $clientId)
    ->where(function($query) use ($name){
        $query->where('users.firstname', 'LIKE', '%'.$name.'%');
        $query->orWhere('users.lastname', 'LIKE', '%'.$name.'%');
        $query->orWhere('users.email', 'LIKE', '%'.$name.'%');
    })
    ->join('users_roles', 'users.id', '=', 'users_roles.user_id')
    ->where('users_roles.role_id', '=', Role::USER_PARTICIPANT)
    ->get();

Problem

How can I prepare the following MySQL query in Laravel? This is the code I used without success: ``` $user_list = DB::table('users') ->where('users.user_id' ,'=', $clientId) ->where('users.firstname', 'LIKE', '%'.$_POST['name'].'%') ->orWhere('users.lastname', 'LIKE', '%'.$_POST['name'].'%') ->orWhere('users.email', 'LIKE', '%'.$_POST['name'].'%') ->join('users_roles', 'users.id', '=', 'users_roles.user_id') ->where('users_roles.role_id', '=', Role::USER_PARTICIPANT) ->get(); ``` The conditions must be: - `users.user_id == $clientId` - `users.firstname == '%'.$_POST['name'].'%' OR users.lastname == '%'.$_POST['name'].'%' OR users.email == '%'.$_POST['name'].'%'` - inner join between users_roles and users crossing by `users.id == users_roles.user_id when users_roles.role_id == Role::USER_PARTICIPANT`

Original source