Как сделать запрос через тройную связь, при это получая все связанные значения 3 таблицы в массив к 1 записи?

Question

[Артем Прохоров]

3 таблицы: users, project_user, roles.
Входные данные: user_id, project_id (связей с проектом может быть несколько, так как на каждую связь накидывается свой role_id).
Я хочу, чтобы в случае если юзер имеет 2 связи с одним и тем же проектом (соответственно имеет 2 разные роли на нем), получался мною после запроса СРАЗУ 1 строкой (а не двумя), и в этой строке имел массив названий обеих ролей, то есть что то вроде этого:

{ id: 1, "username": "some", "project_id": 1, "roles": ['Вышибала', 'Кто-то'] }

.

Comments

[Akina]

Выложите пример данных (создайте online fiddle, например, на https://dbfiddle.uk/?rdbms=mysql_8.0, и дайте ссылку, или хотя бы дайте CREATE TABLE + INSERT INTO) и покажите требуемый результат запроса для этих данных. Также укажите точную версию MySQL.

[Ипатьев]

Вложенный массив получить из БД нельзя. Можно только строку с разделителями.

Answer 1

[Slava Rozhnev]

Mysql:

select json_object(
	'id', user_id,
	'username', username,
	'project_id', project_id,
	'roles', json_arrayagg(role)
) roles
from user_projects
join users on users.id = user_id
group by user_id, project_id;

MySQL json_arrayagg, json_object

Comments

[Артем Прохоров]

Вы просто спасли!

[Артем Прохоров]

Еще такой вопрос, после небольшой переделки под свой пример:

SELECT 
            'id', user.id,
            'username', user.username,
            'roles', json_arrayagg(role.title) roles
        FROM user

        JOIN project_user on user.id = project_user.user_id
        JOIN role on project_user.role_id = role.id
        LEFT JOIN access_component_user ON user.id = access_component_user.user_id

        WHERE project_user.project_id = $project_id AND user.id != $user_id
        GROUP BY project_user.user_id, project_user.project_id, access_component_user.user_id

Появились дубликаты в roles такого вида:

roles: "[\"Координатор\", \"Координатор\", \"Супервайзер\", \"Супервайзер\"]"

Как это исправить? Сами дубликаты появились после добавления 3 джоина(лефт джоина)

[Slava Rozhnev]

Артем Прохоров, выложите пример с вашими данными на SQLize.online

Answer 2

[Ипатьев]

GROUP_CONCAT()
Но это редко бывает полезно поскольку обычно нужны не только названия ролей, но и их идентификаторы
Если хочется "одной строчкой", то надо осваивать продвинутые ORM типа Doctrine