需要在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.* from contracts inner join users on contracts.condesid = users.des_id inner join role_user on role_user.role_id = 3 where contracts.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屋！