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

Question

MySQL

Good day, I made a query that should receive IDs from two tables for the roles table, and only then in SELECT via GROUP_CONCAT collect them separated by commas, but the query displays only the first entry from roles, and the rest only if the data is received one by one, it will be something like this:

I need to get the DBDesigner

schema in this form

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

The question will still be changed, adding more information to it through JOINs.
UPD: There is a 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 `ru`
        ON `ru`.`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 like this

Answer 1

Firstly, it seems to me that you have a mistake in the architecture, since judging by the scheme, only one group can correspond to each user. This solution is a little confusing, since usually one user can belong to several groups.
Secondly, the relationship between the "users" table and "roles_users" in the schema is specified by the "user" field in the "users" table, but the query contains the relationship by the "id" field from the "users" table. I assume that the second option is correct.
Decision:

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`