How to make LEFT JOIN to get all data for GROUP_CONCAT?

Good time of day, made a request to receive from two tables the ID tables for roles, and then SELECT using GROUP_CONCAT to gather them together, but the query displays only the first record of roles, and the rest only if to obtain data on the queue will be like this:
c65de2d5b97e42b389cbbf9c6b79ba23.PNG
I need to get like that
8e4406a5263c49599cfe0a73668e94e5.PNG
The schema of the database DBDesigner
SELECT u.user, GROUP_CONCAT(DISTINCT r.name SEPARATOR ",") AS roles
FROM users AS u
LEFT JOIN roles AS r ON r.id IN (
 SELECT role
 FROM roles_groups
 WHERE gid=u.gid
UNION
 SELECT role
 FROM roles_users
 WHERE user=u.id
)
WHERE u.id=1
GROUP BY u.id, r.id

Another question will be modified by adding more information through JOIN-s.
UPD: There are ready-made query that works, but it would need to be optimized.
SELECT `ur`.`id` AS `id`,
 `b`.`id` AS `botId`,
 `g`.`name` AS `groupName`,
 `ui`.`forever` AS `ignoreForever`,
 `ui`.`expires` AS `ignoreExpires`,
 GROUP_CONCAT(DISTINCT r.name SEPARATOR ",
 ") AS roles
FROM
 (SELECT `u`.`id` AS `id`,
 `u`.`user` AS `user`,
 `u`.`gid` AS `gid`,
 `ru`.`role` AS `role`
 FROM `users` AS `u`
 LEFT JOIN `roles_users` AS `EN`
 ON `EN`.`user` = `u`.`id`
UNION
 SELECT `u`.`id` AS `id`,
 `u`.`user` AS `user`,
 `u`.`gid` AS `gid`,
 `rg`.`role` AS `role`
 FROM `users` AS `u`
 LEFT JOIN `roles_groups` AS `rg`
 ON `rg`.`gid` = `u`.`gid`) AS `ur`
LEFT JOIN `bots` AS `b`
 ON `b`.`id` = 2
LEFT JOIN `users_ignores` AS `ui`
 ON `ui`.`user` = `ur`.`id`
 AND `ui`.`bot` = `b`.`id`
INNER JOIN `groups` AS `g`
 ON `g`.`id` = `ur`.`gid`
LEFT JOIN `roles` AS `r`
 ON `r`.`id` = `ur`.`role`
GROUP BY `ur`.`id`, `g`.`id`, `ui`.`id` limit 5

The result is that such
12d9133fa2214731bfd5866dd0c5d9e8.PNG
July 2nd 19 at 13:57
1 answer
July 2nd 19 at 13:59
First, I think you have a mistake in the architecture, since according to the scheme each user may correspond to only one group. This solution is a bit confusing, as usually one user can belong to multiple groups.
Second, the link in the table "users" and "roles_users" in the scheme specified in the field "user" in the table "users", but in the query appears in the link field id from the table "users". I suppose that is correct the second option.

Solution:
SELECT 
`users`.`id`,
 CASE WHEN (`user_roles_details`.`id` IS NOT NULL AND `usergroup_roles_details`.`id` IS NOT NULL)
 THEN CONCAT(GROUP_CONCAT(DISTINCT `user_roles_details`.`name`), ',', GROUP_CONCAT(DISTINCT `usergroup_roles_details`.`name`))
 WHEN (`user_roles_details`.`id` IS NOT NULL AND `usergroup_roles_details`.`id` IS NULL)
 THEN GROUP_CONCAT(DISTINCT `user_roles_details`.`name`)
 WHEN (`user_roles_details`.`id` IS NULL AND `usergroup_roles_details`.`id` IS NOT NULL)
 THEN GROUP_CONCAT(DISTINCT `usergroup_roles_details`.`name`)
 ELSE 
NULL
 END AS 'roles'
FROM 
`users`
 LEFT JOIN `roles_users` ON (`roles_users`.`user`=`users`.`id`)
 LEFT JOIN `roles` AS `user_roles_details` ON (`user_roles_details`.`id`=`roles_users`.`role`)
 LEFT JOIN `roles_groups` ON (`roles_groups`.`gid`=`users`.`gid`)
 LEFT JOIN `roles` AS `usergroup_roles_details` ON (`usergroup_roles_details`.`id`=`roles_groups`.`role`)
GROUP BY `users`.`id`
In the scheme really made a mistake, but the query is incorrect, he receives all at once in one line roles
imgur.com/a/AYc74 - emmet.Langosh35 commented on July 2nd 19 at 14:02
Updated the question, I attached a work which gives the data as it should, but it has to be optimized. - emmet.Langosh35 commented on July 2nd 19 at 14:05
you have changed my request? It explicitly grouping users.id, then, as a result, it is not. - Barrett.Little commented on July 2nd 19 at 14:08
in MYSQL 5.7, you must specify otherwise the query will not work.
GROUP BY `users`.`id`, `user_roles_details`.`id`, `usergroup_roles_details`.`name`
- emmet.Langosh35 commented on July 2nd 19 at 14:11
you don't have to specify is an irreversible way to change the query.
Simply run:
SET sql_mode="; - Barrett.Little commented on July 2nd 19 at 14:14

Find more questions by tags SQLMySQL