Как заставить LEFT JOIN получить все данные для GROUP_CONCAT?

Question

[Владлен Хеллсайт]

Доброе время суток, сделал запрос который должен получать из двух таблиц ID для таблицы roles, а уже потом в SELECT через GROUP_CONCAT собирать их через запятую, но запрос выводит всего первую запись из roles, а остальные только если получать данные по очереди будет примерно так:

Мне же необходимо получать вот в таком виде

Схема бд 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

Вопрос ещё будет изменятся, добавляя к нему ещё информацию через JOIN-ы.
UPD: Есть готовый запрос который работает, но его нужно было бы оптимизировать.

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

Результат получается вот такой

Answer 1

[Immortal_pony]

Во-первых, мне кажется, что у вас ошибка в архитектуре, так как судя по схеме каждому пользователю может соответствовать лишь одна группа. Данное решение немного сбивает с толку, так как обычно один пользователь может входить в несколько групп.
Во-вторых, связь таблицы "users" и "roles_users" в схеме указана по полю "user" в таблице "users", а вот в запросе фигурирует связь по полю "id" из таблицы "users". Предположу, что правильный все же второй вариант.

Решение:

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`

Comments

[Владлен Хеллсайт]

В схеме действительно ошибся, но запрос остаётся неверным, он не получает сразу всё в одну строку roles
imgur.com/a/AYc74

[Владлен Хеллсайт]

Обновил вопрос, прикрепил рабочий которые отдаёт данные как надо, но его нужно оптимизировать.

[Immortal_pony]

Владлен Хеллсайт, вы точно не изменяли мой запрос? В нем явно указана группировка по users.id, тогда как в результате, ее нет.

[Владлен Хеллсайт]

Immortal_pony: в MYSQL 5.7 необходимо указать, иначе запрос не будет работать.

GROUP BY `users`.`id`, `user_roles_details`.`id`, `usergroup_roles_details`.`name`

[Immortal_pony]

Владлен Хеллсайт, не надо этого указывать - это необратимым образом изменит запрос.
Просто выполните:
SET sql_mode='';