Блог

У меня есть одна таблица пользователей.

 users

| user_id | name   |
| ------- | ------ |
| 1       | Jerry  |
| 2       | George |
| 3       | Elaine |
| 4       | Kramer |
  

У меня есть одна таблица, которая связывает роли с пользователями, а роли назначаются в дереве.

 user_roles

| user_id | role_id | tree_id |
| ------- | ------- | ------- |
| 1       | 5       | 1       |
| 1       | 5       | 2       |
| 2       | 6       | 1       |
| 3       | 7       | 1       |
| 4       | 8       | 1       |
  

Мне нужно возвращать результаты только в тех случаях, когда роль пользователя назначается с определенным tree_id, поэтому я проверяю все роли и деревья. В конце я хочу, чтобы он возвращал одну строку для каждого пользователя.

Я использую Knex и выполняю запрос, который выглядит так:

 knex('users')
  .leftJoin('user_roles', {'user.user_id': 'user_roles.user_id'})
  .whereIn('user_roles.tree_id', arrayOfTreeIds)
  .andWhere(moreFilters)
  

 SELECT *
FROM users
LEFT JOIN user_roles on users.user_id = user_roles.user_id
WHERE user_roles.tree_id in (1, 2, 3)
  

Однако я получаю пять результатов вместо четырех. Если я попытаюсь SELECT DISTINCT , он скажет мне, что мне нужно GROUP BY , но я не могу заставить это работать. Что мне нужно сделать, чтобы получить только один результат для каждого идентификатора пользователя?

У вас есть пользователь, который совпадает с двумя разными tree_id s, поэтому это умножает строки.

В чистом SQL вы могли бы использовать exists вместо join :

 SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM user_roles ur
    WHERE ur.user_id = u.user_id AND ur.tree_id in (1, 2, 3)
)
  

Другой вариант — агрегирование:

 SELECT u.*
FROM users u
INNER JOIN user_roles ur on u.user_id = ur.user_id
WHERE ur.tree_id in (1, 2, 3)
GROUP BY u.user_id
  

Я изменил значение LEFT JOIN на an INNER JOIN , потому что это, по сути, то, что вы хотите (и что делает ваш исходный запрос).

Вы даже можете перечислить совпадающие роли с помощью агрегирования строк:

 SELECT u.*, STRING_AGG(ur.tree_id::text, ',' ORDER BY ur.tree_id) tree_ids
FROM users u
INNER JOIN user_roles ur on u.user_id = ur.user_id
WHERE ur.tree_id in (1, 2, 3)
GROUP BY u.user_id
  

Отказ от ответственности: я не знаю, как написать это в knex!

Демонстрация на скрипке DB