how to show a selected data in a single row?-CodePudding

I want to display a set of data in a single row, but I don't seem to implement it, I tried several ways, it still doesn't work. I want to display all permission names assigned for a role name in a single row.

for example :

| editor  | edit-user, delete-user, delete-role |
| deleter | delete-user                         |

here's my sql

 $datas = DB::SELECT(' SELECT roles.id, roles.name as roleName, permissions.name as PermName
                                FROM roles
                                LEFT JOIN role_has_permissions
                                ON (roles.id = role_has_permissions.role_id)
                                LEFT JOIN permissions
                                ON (role_has_permissions.permission_id = permissions.id)
                                ORDER BY roles.id');

thank you in advance :)

CodePudding user response：

Can you join the two middle joins in a subquery:

LEFT JOIN (
SELECT 
    permissions.name as PermName
    ,role_has_permissions.role_id 
FROM  role_has_permissions
LEFT JOIN permissions ON role_has_permissions.permission_id = permissions.id) 
ON roles.id = role_has_permissions.role_id