Как правильно сделать выборку со связью многие ко многим?

Question

[yazux]

Добрый день!
Долго использовал Laravel Eloquent ORM и уже подзабыл SQL, сейчас в силу событий требуется написать запрос на чистом SQL и с этим у меня возникла проблема.
БД используется PostgreSQL 9.5

Есть следующие таблицы:
user:
- id
- name
-------------
role
- id
- name
-------------
user_role
- id
- user_id
- role_id
-------------
Связи между таблицами соответсвенно установлены.

Требуется получить список пользователей и их ролей. То есть на выходе должно быть следующее:

[
  {
     id: 1,
     name: "user_name_1"
     roles: [
        {
           id: 1,
           name: "role_name_1"
        },
        {
           id: 2,
           name: "role_name_2"
        }
     ]
  },
  {
     id: 2,
     name: "user_name_2"
     roles: [
        {
           id: 2,
           name: "role_name_2"
        },
        {
           id: 3,
           name: "role_name_3"
        }
     ]
  },
]

Сейчас написал такой запрос:

SELECT user.id, user.name, array_agg(user_role.role_id) as role_id FROM user JOIN user_role ON user_role.user_id=user.id WHERE users.id IN (1,2) GROUP BY users.id

На выходе получаю следующее, что не совсем то, что нужно:

[
  {
    id: 1,
    name: "user_name_1",
    role_id: "1,2"
  },
  {
    id: 2,
    name: "user_name_2",
    role_id: "2,3"
  }
]

Подскажите пожалуйста как написать правильный запрос.

Answer 1

[Yan-s]

Желаемый результат не получить на выходе RAW запроса. Когда вы строите запрос через ORM, eloquent может сделать несколько запросов, затем проходится по полученным результатам в цикле и собирается всё в один результирующий объект. То есть получение нужной вам структуры объекта лежит на стороне сервера.

Comments

[yazux]

Спасибо, вы оказались правы. Решения при помощи одного запроса найти не вышло. В итоге сделал с помощью двух запросов и последующей обработки результатов этих запросов.

Первый запрос:
SELECT id, name, created_at FROM users

Второй:

SELECT name, description, user_role.user_id AS pivot_user_id, user_role.role_id AS pivot_role_id FROM roles INNER JOIN user_role ON roles.id = user_role.role_id WHERE user_role.user_id in (8, 10, 13, 1, 2)

И обработка результатов запросов:

users = users.map(user => {
    user.roles = roles.filter(role => {
      return (user.id === role.pivot_user_id) ? role : false;
    });
    return user;
  });

В итоге получаю следующий результат:

[
        {
            "id": "8",
            "name": "user_name_8",
            "roles": []
        },
        {
            "id": "1",
            "name": "user_name_1",
            "roles": [
                {
                    "name": "role_name_3",
                    "pivot_user_id": "1",
                    "pivot_role_id": "3"
                },
                {
                    "name": "role_name_2",
                    "pivot_user_id": "1",
                    "pivot_role_id": "2"
                }
            ]
        },
        {
            "id": "2",
            "name": "user_name_2",
            "roles": [
                {
                    "name": "role_name_2",
                    "pivot_user_id": "2",
                    "pivot_role_id": "2"
                }
            ]
        }
    ]