Mysql laravel query with multiple points where or near and inner join

Question

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

Conditions must be:

• users.user_id == $clientId
• users.firstname == '%'.$_POST['name'].'%' OR users.lastname == '%'.$_POST['name'].'%' OR users.email == '%'.$_POST['name'].'%'
• internal connection between users_roles and users crossing users.id == users_roles.user_id when users_roles.role_id == Role::USER_PARTICIPANT

Answer 1

Ok, it looks like your problem is with nested where

Try the following:

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

Answer 2

I think you want to explore the possibility of using the built-in function in the join clause ... your where clauses can collide with each other - and really you want the role_id clause to be in your connection.

http://laravel.com/docs/4.2/queries#joins

 $clientId = 1; $name = 'Phillip'; $escaped = '%' . $name . '%'; $userList = DB::table('users') ->where('users.user_id', $clientId) ->where('firstname', 'LIKE', $escaped) ->orWhere('lastname', 'LIKE', $escaped) ->orWhere('email', 'LIKE', $escaped) ->join('users_roles', function($join) { $join->on('users.user_id', '=', 'users_roles.user_id') ->where('users_roles.role_id', '=', Role::USER_PARTICIPANT); })->get(); var_dump($userList); 

The above query:

 select * from `users` inner join `users_roles` on `users`.`user_id` = `users_roles`.`user_id` and `users_roles`.`role_id` = ? where `users`.`user_id` = ? and `firstname` LIKE ? or `lastname` LIKE ? or `email` LIKE ? 

Which gives me one line back, given the following database:

 mysql> desc users; +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | user_id | int(11) unsigned | NO | PRI | NULL | auto_increment | | firstname | varchar(255) | NO | | NULL | | | lastname | varchar(255) | NO | | NULL | | | email | varchar(255) | NO | | NULL | | +-----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.02 sec) mysql> desc users_roles; +---------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+-------+ | user_id | int(11) unsigned | NO | | NULL | | | role_id | int(11) unsigned | NO | | NULL | | +---------+------------------+------+-----+---------+-------+ 2 rows in set (0.03 sec) mysql> select * from users; +---------+-----------+------------+--------------------+ | user_id | firstname | lastname | email | +---------+-----------+------------+--------------------+ | 1 | Phillip | Harrington | [e-mail] | +---------+-----------+------------+--------------------+ 1 row in set (0.00 sec) mysql> select * from users_roles; +---------+---------+ | user_id | role_id | +---------+---------+ | 1 | 1 | +---------+---------+ 1 row in set (0.00 sec)