Как сделать мягкое удаление и использовать уникальные ключи?

Question

[WebDev]

Есть таблица books, в которой уникальное поле alias.
Делаю "мягкое" удаление, теперь добавилось поле deleted_at, и, чтобы удаленные книги не мешали создавать новые с таким же alias, я установил unique key на два поля: alias, deleted_at.
Теперь создавать новые записи с таким же alias можно, даже если есть такая "удаленная" запись.
Но теперь новая проблема: deleted_at по умолчанию null, поэтому можно сколько угодно создавать записей с одинаковыми alias.
Как сохранить уникальность записей?

Comments

[sim3x]

Сделать урл, в котором алиас будет вспомогательным, а ид главным

/books/<book_id>_<alias>
/books/<alias>-<book_id>

[Модератор]

WebDev дорогой пользователь, настоятельно рекомендуем еще раз обратить самое пристальное внимание на п. 3.1 регламента работы сервиса (и, в особенности, на его последний абзац).
В противном случае ваши вопросы будут удаляться по причине тег-спама, а систематические нарушения приведут к блокировке учетной записи.

Answer 1

[Gomonov]

Я бы не использовал deleted_at как флаг, что сущность удалена - это больше справочная информация.
Завёл бы поле is_delete

Comments

[Алексей Уколов]

Чем это поможет в решении описанной проблемы?

[WebDev]

Алексей Уколов, ну теоритически, можно было бы действительно добавить еще одно поле, например, is_alive, которое по умолчанию = 1, а после удаления становится null, и повесить уникальный индекс alias + is_alive. Тогда "живая" запись всегда была бы гарантировано одна, а "удаленных" сколько угодно.

[Алексей Уколов]

Точно, забыл, что null в проверке уникальности не участвует.

Answer 2

[Алексей Уколов]

На уровне БД (конкретно в MySQL) такое реализовать нельзя, нужно эту логику закладывать в приложение.
UPD: выше написана ерунда, читайте комментарии к ответу.

Примерно так это будет выглядеть (код из живого проекта, поэтому сущности другие, но смысл тот же):

class UniqueAmongActiveClients implements \Illuminate\Contracts\Validation\Rule
{
    public function passes($attribute, $value): bool
    {
        return Client::shouldBeUniqueAndItIs($attribute, $value, auth()->id());
    }
}

class Client extends Eloquent
{
    public static function shouldBeUniqueAndItIs(string $attribute, $value, ?int $excludedId = null): bool
    {
        $query = static::withoutTrashed()->where($attribute, $value);

        if ($excludedId) {
            $query->where('id', '<>', $excludedId);
        }

        return $query->doesntExist();
    }
}

P.S. Стандартное поле deleted_at заменять костылями, как советуют в соседних ответах, конечно, не нужно.

Comments

[Gomonov]

Выше TC отписал как это реализовать на уровне БД, и сразу ответил на ваш вопрос в комменте к моему ответу. По поводу заменять костылями поле deleted_at - ни кто не предлагал его заменять - читайте внимательнее.

[vism]

На уровне БД (конкретно в MySQL) такое реализовать нельзя

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

[Алексей Уколов]

vism, вы правы.

[Алексей Уколов]

Gomonov

ни кто не предлагал его заменять

Я бы не использовал deleted_at как флаг, что сущность удалена

[Gomonov]

Алексей Уколов,

Я бы не использовал deleted_at как флаг, что сущность удалена - это больше справочная информация

- я не предлагал заменить deleted_at на is_delete. Я предложил добавить is_delete и использовать для запросов его. deleted_at оставить для справки

[Алексей Уколов]

Gomonov, ну вот о том и речь, что вы предлагаете заменить стандартный функционал Laravel самописным. Колонку добавить нужно, но использовать её исключительно в индексе и заполнять тригером, как заметил vism. А внутри приложения продолжать использовать готовый механизм.

[vism]

Алексей Уколов, Gomonov, Как говорится, какой вопрос, таков ответ.
Уже не первый раз замечаю, как хорошие решения пропускаются и отмечаются ответом решения уровня junior.
Теперь как на форум сюда захожу, в коментах развлекаться.

[WebDev]

vism, скажите пожалуйста, чем вариант с использованием scope лучше варианта с использованием events?
Мой вариант достаточно прописать в одном месте, shouldBeUniqueAndItIs, который предложил автора ответа, нужно добавить в каждый запрос. Да это более наглядно, но мой вариант гарантирует блокировку на уровне СУБД, даже если кто-то руками захочет сделать insert, а shouldBeUniqueAndItIs проверяет все только в контексте использования Eloquent.
Вариант несомненно хороший, но говорить что он единственно верный - это лишнее.

[Алексей Уколов]

shouldBeUniqueAndItIs проверяет все только в контексте использования Eloquent.

События тоже только в этом контексте ведь работают.

Правильное решение — использовать триггеры субд вместо событий , потому что так надёжнее и ближе к данным.

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

[vism]

Алексей Уколов, WebDev, Алексей верно говорит, плюсую. Он добрый, ему не лень всё объяснить. Если не понимаете зачем так делать, задайте вопросы более подробные, чтоб понять смысл.

Answer 3

[WebDev]

Готовый вариант такой:
1. Добавил поле is_alive tinyint(1) NULL [1]
2. Установил UNIQUE на alias + is_alive
3. Добавил обработчики на удаление и восстановление:

Book::deleting(function ($book) {
            $book->update(['is_alive' => null]);
        });

        Book::restoring(function ($book) {
            $book->update(['is_alive' => 1]);
        });

Comments

[vism]

Фиговый вариант, по рукам за такой бил бы у меня в команде.
Никто не используйте его.
вот в коментах под другим ответом всё расписали как делать правильно.

[WebDev]

vism, такие советы без аргументов никакой ценности в себе не несут. Я не нашел в комментах ничего толкового.

[Alex]

vism, а как бы вы в команде это делали? вынесли проверку в код приложения?
А кто гарантирует целостность бд? Где гарантии что не появится другой приложеньки которая будет общаться с этой базой?

[vism]

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

[Alex]

vism, Он UNIQUE флаг ставит на два поля.

[vism]

Alex, управление
сервисным
флагом

Answer 4

[Alex]

Default value deleted_at

Comments

[WebDev]

Если я буду устанавливать какую-то дату по умолчанию, то запись будет считаться удаленной.

[Alex]

поле isDeleted: boolean
и по нему ключ

[Алексей Уколов]

Чем это поможет в решении описанной проблемы?