LINUX.ORG.RU

Нужен хитрый SQL запрос с объединением трёх таблиц с доп логикой

 ,


0

1

Вводная:
Захотел в списке пользователей отмечать звёздочкой тех, кто является владельцем. Признак владельца храниться в виде роли, которая прикрепляется к пользователю. У пользователя может быть несколько ролей. Роль храниться в виде строки в отдельной таблице.

Структура БД:

Users:
UserId|UserName
     1|Вася
     2|Петя
     3|Коля
     4|Боришполь

Roles:
RoleId|RoleName
     1|Owner
     2|AnotherRole

UserRoles:
UserId|RoleId
     1|1
     1|2
     3|2

Я хочу получить следующее: если у пользователя есть роль Owner, то в столбце должно быть true, иначе false. Не важно есть там какая-нибудь роль или null.

UserId| UserName|IsOwner
     1|     Вася|true
     2|     Петя|false
     3|     Коля|false
     4|Боришполь|false

Для удобства набросал схемку можно где-нибудь онлайн запустить, например здесь.

СУБД постгря 10+.

★★★

Первое что пришло в голову эрреэй_аг плюс контейнс. Хотя вероятно юнион запросов только овнеры + только не овнеры может работать быстрее.

ya-betmen ★★★★★ ()
with owners as (select u."UserId" from "Users" u left join "UserRoles" ur on u."UserId" = ur."UserId" left join "Roles" r on r."RoleId" = ur."RoleId" where r."RoleName" = 'Owner')

select u."UserId", u."UserName", case when owners."UserId" = u."UserId" then 'true' else 'false' end from "Users" u inner join owners;

Первое, что в голову пришло

hippi90 ★★★★ ()

предлагаю создать дополнительный столбец UserCachedJSON, куда заносить в формате JSON всякую мелочь, вот вы назначили пользователя «владельцем», значит достаньте содержимое UserCachedJSON, распакуйте, добавьте туда звёздочку и запишите обратно.

Вместо потребления процессорного времени по всякой ерунде потребляйте память на диске. Вот.

Spoofing ★★★★★ ()
SELECT "Users"."UserId" AS "UserId", "Users"."UserName" AS "UserName",
CASE WHEN "UserRoles"."RoleId" = (SELECT "RoleId" FROM "Roles" WHERE "RoleName" = 'Owner') THEN true ELSE false END AS "IsOwner"
FROM "Users"
JOIN "UserRoles" ON "UserRoles"."UserId"  = "Users"."UserId"

И никогда не делай названия баз/таблиц/полей с заглавными буквами.

karaien ()

Например, как-то так, подзапросом:

SELECT
  *,
  EXISTS(SELECT * FROM UserRoles INNER JOIN Roles USING(RoleId) WHERE RoleName = 'Owner' AND UserRoles.UserId = Users.UserId) AS IsOwner
FROM Users;

Джойнами:

SELECT
  UserId, UserName, coalesce(bool_or(RoleName = 'Owner'), false) AS IsOwner
FROM Users
LEFT OUTER JOIN UserRoles USING(UserId)
LEFT OUTER JOIN Roles USING(RoleId)
GROUP BY UserId, UserName;

Либо сделать нормальную структуру БД с enum типом ролей, и массовом ролей для пользователя вместо промежуточных таблиц.

slovazap ★★★★★ ()
Последнее исправление: slovazap (всего исправлений: 1)
select u.*, e.isOwner from Users u 
  inner join (
    select 
      u.UserId, 
      (CASE WHEN max(r.RoleId) IS NOT NULL THEN 'true' else 'false' end) as isOwner 
    from users u 
    left join UserRoles ur on u.UserId = ur.UserId
    left join Roles r on r.RoleId = ur.RoleId and r.RoleName='Owner'
  group by u.UserId) e on e.UserId = u.UserId;
Tanger ★★★★★ ()
Ответ на: комментарий от Shadow

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

slovazap ★★★★★ ()
Последнее исправление: slovazap (всего исправлений: 1)
select
	Users.UserId,
	Users.UserName,
	case
		when UserRoles.RoleId is not null then true
		else false
	end as IsOwner
from
	Users
left outer join UserRoles on
	UserRoles.UserId = Users.UserId;

Не важно есть там какая-нибудь роль или null

Условие некорректно. Если роли нет то там null.

micronekodesu ★★ ()
Последнее исправление: micronekodesu (всего исправлений: 1)

Нужен хитрый SQL запрос

Это суть SQL.

Обычно в нем многие на разработку запроса неделю тратят …
Простые запросы же в нем удобны и это создает иллюзию «крутизны» SQL.

Что дает SQL?

Выполнить какие-либо действия в базе данных /select, insert, …/.

Для этого SQL должен предоставить, соответствующий синтаксис, который позволит спроектировать алгоритм.

Так вот /как было сказано ранее/ эти возможности удобны лишь для какой-то «ниши» алгоритмов.

Почему?

Потому что SQL не предоставляет возможности записи произвольного алгоритма.
Отсюда и все проблемы.
Когда алгоритм прост - SQL великолепен.
Если алгоритм сложен, то «ноют» на форумах по две недели.

В Firebird TSQL правда частично предоставляет возможность разработки алгоритмов.
Но все равно «тараканы лезут и лезут» …
Да TSQL сглаживает сложность разработки алгоритма, но там много иных проблем …

Успехов вам, любители SQL и «не ищущих легких путей».

PS: Технологии разработки языков программирования, SQL были изобретены еще пятьдесят лет назад и все «плачут от радости».

Владимир

anonymous ()

Захотел в списке пользователей отмечать звёздочкой тех, кто является владельцем. Признак владельца храниться в виде роли, которая прикрепляется к пользователю. У пользователя может быть несколько ролей. Роль храниться в виде строки в отдельной таблице.

Кстати в 1С «роли, права, … на работу с данными» хорошо реализованы …

Шутка

1С - супер продвинутый SQL.

Владимир

anonymous ()

Предлагаю не страдать хернёй. Пользователи у тебя уже должны быть загружены (иначе куда ты собрался лепить звёздочку). Поэтому остаётся только загрузить роли и проверить есть ли там owner роль. Ну или, если все роли не требуются, то загружать только owner роль.

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

no-such-file ★★★★★ ()
Последнее исправление: no-such-file (всего исправлений: 1)
Ответ на: комментарий от anonymous

SQL не предоставляет возможности записи произвольного алгоритма

Потому, что это не его задача. Он должен применяться в сочетании с другими средствами.

hobbit ★★★★★ ()
Ответ на: комментарий от karaien

У меня ASP.NET MVC Core. Это встроенные таблицы фреймворка. Соглашение требует писать с большой буквы.

Выполнил твой запрос. Есть дублирование пользователей. И не все попали в список.

ox55ff ★★★ ()
Ответ на: комментарий от anonymous

Кстати в 1С «роли, права, … на работу с данными» хорошо реализованы …

  1. Нет, но и не плохо.

  2. В PostgreSQL всё хорошо с правами, автор темы изобретает лисапед для чего-то другого.

anonymous ()
Ответ на: комментарий от no-such-file

Предлагаю не страдать хернёй.

Структура БД навязана фреймворком. Я бы вообще юзеру столбец добавил и не страдал бы. По поводу чихов, то производительность тут особая не нужна. Пользователей будет мало. Выполнятся этот запрос будет крайне редко. Только при входе в настройки.

ox55ff ★★★ ()