Laravel DB查询连接多个表和获取条件数据

Question

我的表下面的列表，我想从

users 
id email 
1 [email protected] 
2 [email protected]   

user_projects 
project_id user_id role    
1   1  member 
1   2  owner 

projects 
id name 
1 example project 

我想要得到的数据看起来像下面

project_id name    email    role owner 
1   example project [email protected] member [email protected] 

查询数据

总之，我想获取用户所属的项目列表以及他的角色和项目所有者。

我想出了以下查询

return DB::table('user_projects') 
     ->join('projects', 'user_projects.project_id', '=', 'projects.id') 
     ->join('users', 'user_projects.user_id', '=', 'users.id') 
     ->where('user_projects.user_id', '=', $userId) 
     ->select('users.email', 'projects.id', 'users.id as user_id','user_projects.role as userRole', 
      'projects.name') 
     ->get()->toArray(); 

我能够得到的项目列表和用户在项目中的作用，但无法找到该项目的电子邮件所有者。

Answer 1

我认为这是你需要的。

select projects.id, projects.name, users.email, up1.email as owner, up2.role role from 
projects, (select * from user_projects, users where role='owner' and users.id=user_projects.user_id) up1, user_projects up2, users 
where 
projects.id=up1.project_id and 
up1.project_id = up2.project_id and 
up2.user_id=users.id and users.id=1