具有多个 where orwhere 和内部连接的 laravel mysql 查询

laravel mysql query with multiple where orwhere and inner join

提问by Alberto

How can I prepare the following MySQL query in Laravel?

如何在 Laravel 中准备以下 MySQL 查询？

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:

条件必须是：

1. users.user_id == $clientId
2. users.firstname == '%'.$_POST['name'].'%' OR users.lastname == '%'.$_POST['name'].'%' OR users.email == '%'.$_POST['name'].'%'
3. inner join between users_roles and users crossing by users.id == users_roles.user_id when users_roles.role_id == Role::USER_PARTICIPANT

1. users.user_id == $clientId
2. users.firstname == '%'.$_POST['name'].'%' OR users.lastname == '%'.$_POST['name'].'%' OR users.email == '%'.$_POST['name'].'%'
3. users_roles 和穿越的用户之间的内连接 users.id == users_roles.user_id when users_roles.role_id == Role::USER_PARTICIPANT

回答by lukasgeiter

Okay it looks like your problem is with the nestedwhere

好的，看起来您的问题是嵌套的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();

回答by Phillip Harrington

I think you want to look into the possibility of using an inline function on the join clause... Your where clauses might be colliding - and really you want the role_id clause to be on your join.

我想你想研究在 join 子句上使用内联函数的可能性......你的 where 子句可能会发生冲突 - 实际上你希望 role_id 子句在你的 join 上。

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

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 produces this 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 is giving me one row 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 | [email protected] |
+---------+-----------+------------+--------------------+
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)