需要在laravel中集成具有多个条件的多个表 [英] Need to Integrate multiple table with multiple conditions in laravel
问题描述
我在具有des_id
的详细信息页面中,
我需要按详细信息des_id
以及是否有角色人员来获取所有用户.
I'm in details page that has des_id
,
I need to get all the users by the details des_id
and if they have Role staff.
$asignTo=Contracts::find($id)
->where('contracts.id',$id)
->join('users','contracts.condesid','users.des_id')
->join('role_user',function($join){
$join->on('role_user.role_id', 3);
})
->select('users.*')
->get();
那行不通给我:
SQLSTATE [42S22]:找不到列:1054'on中的未知列'3' 子句"(SQL:从
contracts
内部联接users
中选择users
.*contracts
.condesid
=users
.des_id
内部联接role_user
在role_user
.role_id
=3
其中contracts
.id
= 8)
SQLSTATE[42S22]: Column not found: 1054 Unknown column '3' in 'on clause' (SQL: select
users
.* fromcontracts
inner joinusers
oncontracts
.condesid
=users
.des_id
inner joinrole_user
onrole_user
.role_id
=3
wherecontracts
.id
= 8)
但是如果我使用下面的代码,它将为我提供所有具有角色职员的用户,而无需des_id.
but if I used the below code, it gives me all users with role staff only without des_id.
$staff = [];
$users_list =User::all();
foreach ($users_list as $item){
if($item->hasRole('staff')){
$staff[] = $item;
}
}
刀片:
<select class="form-control custom-select-value" name="assigto" required="required">
<option value="">Select User </option>
@foreach($asignTo as $user)
<option value="{{$user->id}}"> {{$user->name}}</option>
@endforeach
</select>
推荐答案
假设这是您的数据布局,并且des_id
和condesid
已连接:
Assuming this is your datalayout, and des_id
and condesid
are connected:
users
+----+------+--------+
| id | name | des_id |
+----+------+--------+
role_user
+---------+---------+
| role_id | user_id |
+---------+---------+
contracts
+----+----------+
| id | condesid |
+----+----------+
要检索按合同和角色ID过滤的用户记录,所需的查询可能如下所示:
To retrieve user records filtered on a contract and role ID, the desired query might look like this:
SELECT *
FROM users U
JOIN contracts C ON C.condesid = U.des_id AND C.id = 2
JOIN role_user RU ON RU.user_id = U.id AND RU.role_id = 2
当转换为查询生成器语句时:
When translated to a Query Builder statement:
$users = User::select('U.*')
->from('users AS U')
->join('contracts AS C', 'C.condesid', 'U.des_id')
->join('role_user AS RU', 'RU.user_id', 'U.id')
->where('RU.role_id', 3)
->where('C.id', 8)
->get();
这篇关于需要在laravel中集成具有多个条件的多个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!