mysql - Laravel DB 查询连接多个表并获取条件数据

Question

我有下面的表列表，我想从中查询数据

users 
id email   
1 user1@test.com
2 user2@test.com          

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   user1@test.com    member  user2@test.com

简而言之，我想获取用户参与的项目列表以及他的角色和项目所有者。

我想出了以下查询

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