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

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

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;

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

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

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

Сделай VIEW с INNER JOIN.

select
u.*,
(ur.role_id is not null) as is_owner
from
users u
left outer join user_roles ur on u.id = ur.user_id and ur.role_id = 1

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"

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

Мрак. Просто эталонное «так делать нельзя».

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

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 типом ролей, и массовом ролей для пользователя вместо промежуточных таблиц.

В питоне orm peewee такое делает джойнами (правда, с внешними индексами).
Разве с вложенным селектом не будет в разы медленнее?

GROUP BY UserId, UserName;

А если UserName неуникален?

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

А вы посмотрите внимательно на процитированный GROUP BY.

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.

Реклама твоего boobstrap и то была бы уместнее, чем это.

А вы посмотрите внимательно на процитированный GROUP BY.

Не. Проблема не в глазах. Надо бы мозг ребутнуть подушкой.

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

Это суть SQL.

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

Что дает SQL?

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

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

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

Почему?

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

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

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

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

Владимир

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

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

Шутка

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

Владимир

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

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

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

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

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

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

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

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

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

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

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

Спасибо за подсказки. Сейчас протестирую и что-нибудь выберу.