MySQL: как выбрать строки с учетом специфичности?

Question

[sudo rm -rf /]

На стороне базы
Есть таблица, содержащая шаблоны сообщений бота Discord:

Для каждого шаблона задаётся область применения (специфичность), с использованием колонок server_id (далее S), role_id (далее R), user_id (далее U) (не является уникальным ключом).
Могут быть заданы:

• общесерверные шаблоны (S=null, R=null, U=null);
  
• для конкретного пользователя (S=null, R=null, U="some_id_here");
  
• для пользователя на каком-то сервере (S="some_id_here", R=null, U="some_id_here");
  
• для роли на сервере (S="some_id_here", R="some_id_here", U=null);
  
• для конкретного пользователя с установленной специальной ролью (S=null, R="some_id_here", U="some_id_here");
  
• и так далее.
  

Понятно, что специфичность столбцов по-отдельности задаётся как S<R<U,
но вкупе, например, (S="some_id_here", R=null, U="some_id_here") > (S=null, R=null, U="some_id_here").

На стороне бота
Со стороны бота нам известны сервер - ServerID, адресат - UserID, список ролей адресата - UserRoles[].
Имея этот набор данных, требуется выбрать из базы все шаблоны, подпадающие под них и имеющие максимальную специфичность.

Ограничения
Бот написан на TypeScript, обращение к базе осуществляется через библиотеку Knex.js.
В связи с этим есть ряд ограничений. Бот должен уметь сам создавать таблицу в случае её отсутствия, поэтому таблица должна быть максимально проста. Триггеры отпадают.

Вопрос
Как организовать такую выборку?
Вообще, она осуществима в рамках заданных ограничений?

P.S. Бот написан не мной, связи с разработчиком нет. Я пишу модуль для него, и лезть в структуру бота не могу, поэтому ограничен его api.

Comments

[Stalker_RED]

А как сочетаются все и максимальная специфичность одновременно? Если выбираются все, то среди них будут и с максимальной специфичностью, и не очень.

[sudo rm -rf /]

Stalker_RED, может быть несколько шаблонов с одинаковой специфичностью. Нужно выбрать записи с максимальным значением специфичности.

[sudo rm -rf /]

Stalker_RED, все те, что имеют максимальное значение специфичности. Например, если для для одной роли на сервере задано сразу 4 шаблона, то получаем их все.

[Stalker_RED]

sudo rm -rf /, а как определяется у какого из шаблонов больше специфичность?
Допустим

для пользователя на каком-то сервере (S="some_id_here", R=null, U="some_id_here");
для роли на сервере (S="some_id_here", R="some_id_here", U=null);

у них у обоих одинаковая специфичность?

Полей, по которым производится выборка, всего три?

[sudo rm -rf /]

Stalker_RED, у пользователя специфичность больше, чем у роли; у роли больше, чем у сервера.
Можно определить это числом URS, где U, R и S - битовые флаги, показывающие, заданы ли указанные критерии для для пользователя, роли и сервера соответственно.
Для пользователя на каком-то сервере (U="some_id_here", R=null, S="some_id_here") => 101.
Для роли на сервере(U=null, R="some_id_here", S="some_id_here") => 011.
101 > 011.
При наличии триггеров можно сделать отдельное поле, в которое при обновлении записывать эти числа и организовывать сортировку по этому полю.
НО даже так, как сделать корректную выборку по этим параметрам в одном запросе - вопрос.

Answer 1

[Андрей]

А нагрузка большая сейчас и в планах?
Если нет, то я бы сделал несколько запросов:
1. С ограничениями по всем параметрам
2. Последовательно отбрасывая менее весомые критерии.

Если никакие фильтры не вернули ни одного ряда - то все критерии в null

Comments

[sudo rm -rf /]

Нагрузка планируется небольшая.
Каждый сервер ежедневно для себя формирует список своих участников, специфичный для конкретного дня,
роли каждого пользователя известны, затем сервер для каждого пользователя из списка запрашивает подходящие шаблоны, фильтруя по описанным критериям. Из полученного списка "подходящих" шаблонов случайно выбирается один. Всё.

[sudo rm -rf /]

Попробую реализовать такой подход, спасибо

Answer 2

[AUser0]

А как такой вариант? Правда сам вес каждой записи, и случай наскольких записей с одинаковым весом нужно обрабатывать уже в скрипте, получающем данные...

SELECT * FROM table
WHERE user_id=X OR role_id IN (Y, Z, U) OR server_id=W
ORDER BY user_id=X DESC, role_id IN (Y, Z, U) DESC, server_id=W DESC

Comments

[sudo rm -rf /]

А (Y, Z, U) - это что?

[AUser0]

Какие-то три значения из массива UserRoles[], конечно же.

Answer 3

[Stalker_RED]

SELECT u, r, s, template,
CAST(
  CONCAT(
    CASE u WHEN null THEN 1 ELSE 0 END,
    CASE r WHEN null THEN 1 ELSE 0 END,
    CASE s WHEN null THEN 1 ELSE 0 END
  )
AS BINARY) priority
FROM tbl

WHERE (u=1 AND r=2 AND s=3)
  OR (u=1 AND r=2)
  OR (u=1 AND s=3)
  OR (r=2 AND s=3)
  OR u=1
  OR r=2
  OR s=3

having priority = max(priority) -- ну или order by

Как-то так, код не проверял, возможны опечатки.