Можно ли в sql сделать поле isactive, чтобы активной единовременно могла быть только одна запись?

Question

[Вячеслав Правильный]

У меня есть переключение активных записей на фронте. Сейчас я делаю так, при получении запроса на активацию записи, на бэкенде даю запрос в бд на снятие активности в записях бд, и проставлении активности в нужной.
Получается два запроса в бд, и как то чувство что это неправильно. Может накручиваю себе, но думаю в sql уже есть какое то решение для этого? Или все делают это через два запроса?
Нагуглить не смог, не знаю как составить запрос чтобы выдало не инфу по настройкам, а именно по ситуации.

Comments

[alexalexes]

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

Answer 1

[BorLaze]

А это принципиально - иметь именно флаг?

Можно сделать по-другому - заводим поле типа DATE, называем его ACTIVATED (например), и при активации записи пишем в него текущую дату. Потом, когда нам нужно найти активную запись, просто ищем запись с самой старшей датой.

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

Comments

[Михаил Е]

Это решение подходит только для конкретных задач, например если надо брать последнюю информацию по ключевым полям. Хотя даже в этом случае сначала придётся получить дату последней записи. Если задача встанет "просуммируй активные записи", представьте ваш запрос, а в случае с флагом добавится только условие Where active = true.

[BorLaze]

Михаил Е, во-первых, в условии сказано однозначно "чтобы активной единовременно могла быть только одна запись".

Во-вторых, что значит "если надо брать последнюю информацию по ключевым полям" и чем это помешает выборке по другому полю?

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

Например, у нас есть три записи, из них активна вторая:
ID, NAME, ACTIVATED
1, Andrew, null
2, Boris, 2020-12-01
3, Galina, null

Получаем активную: select * from T order by ACTIVATED desc limit 1 --> (2, Boris, 2020-12-01)

Переключаем активную на запись №1: update T set ACTIVATED=now() where id = 1
ID, NAME, ACTIVATED
1, Andrew, 2021-02-02
2, Boris, 2020-12-01
3, Galina, null

Получаем активную: select * from T order by ACTIVATED desc limit 1 --> (1, Andrew, 2021-02-02)

Все! Дешево и сердито. Без всяких транзакций при апдейте и прочих излишеств.

[Михаил Е]

BorLaze, возможно я не совсем правильно понял условия задачи, или вы :
в заголовке "чтобы активной единовременно могла быть только одна запись"
в теле "У меня есть переключение активных записей на фронте."
В таком случае поправлюсь : Если речь про то, чтобы в таблице всегда была только одна запись активная, тогда думаю ваш вариант прокатит... если их больше 1-й и речь идёт об одной активной в рамках какого-то измерения (например Name) тогда скорее всего придётся :
- сначала получить группы и последние даты, а затем получать данные из этих дат.
Например, если таблица такого вида(изначально):
ID, NAME, Instance, ACTIVATED
1, Andrew, UK, 1
2, Andrew, US, 0
3, Andrew, DE, 0
4, Boris, US, 1
5, Boris, UK, 0
6, Boris, DE, 0

То после применения даты в поле ACTIVATED получим :
ID, NAME, Instance, ACTIVATED
1, Andrew, UK, 2021-02-01
2, Andrew, US, 2021-01-02
3, Andrew, DE, 2021-01-01
4, Boris, US, 2021-01-31
5, Boris, UK, 2021-01-02
6, Boris, DE, 2021-01-01

В этом случае активна получается только запись UK ... а чтобы это узнать необходимо сначала получить макс по полю ACTIVATED, а затем уже узнать данные из строки (Instance)

[Вячеслав Правильный]

Михаил Е, на самом деле интересный подход, есть над чем подумать. По условиям подходит. Надо посмотреть нет ли подводных камней

[Вячеслав Правильный]

Михаил Е, да, единственное что надо наверное не с датой работать а с временем, таймстамп допустим, тогда единовременно может быть активна одна запись только.

[BorLaze]

Вячеслав Правильный, timestamp или unixtime - это само собой. Дату я привел только для примера.

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

Впрочем, лечится такой кейс просто - достаточно объявить это поле unique.

Answer 2

[gsaw]

Ничего такого специального нету. База данных только свалка данных. Ты можешь указать по каким правилам их туда сваливать, но логику твоего приложения база данных не знает и лучше если не узнает. Так как тогда привяжешься навсегда к одному вендору.

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

update mytable set isactive = case when id = :active_id then 1 else 0 end where search_criteria = :filter

active_id это уникальный ключ, если id записи совпадает с заданным active_id, то isactive будет установленно в 1, для всех остальных записей удвлетворяющих условию в 0.

Comments

[BorLaze]

Будет работать, если записей десяток. А если их миллионы... то будет "ой!"

Мы ведь, фактически, при смене активной записи будем апдейтить ВСЮ таблицу.

[gsaw]

BorLaze, это понятно. Если записей много, то да, лучше двумя апдейтами. Только в клиенте тогда надо два айдишника хранить и не факт, что старый активный действительно активный.

Автору должно быть виднее.

Answer 3

[Андрей Мохов]

Я бы предложил использовать триггеры, но многим они не нравятся, т.к. бизнес-логика размывается между приложением и базой данных.
Но если вас не пугает сей факт, то решение очень даже хорошее: один раз настроили и забыли и можно не париться по поводу смены активности.