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

Question

[Слава]

При изменении записи через crud пишется история изменений и она сразу пишется в две таблицы между которыми нет связи.

исходная таблица: users (id, name, birthay)

таблицы итсории: names (id, name, user_id)

birthays (id, birthay, user_id)

Кадый раз, когда изменяется users в каждую из двху табилц добавляются по записи даже если поля записи не изменились.

Вопрос: Как получить выборку всех записей из двху таблиц для конкретного user,id но там чтобы в результатирующей выборке было столько же строчек сколько в любой из этих двух таблиц для данного пользователя.

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

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

Вроде как нужно в виртуальную таблицу считать каждую таблицу и сделать динамический id у каждой виртуальной таблицы и потом по этому id группировать. Или как еще можно?

Таблицу Pivot - это уже крайний случай. хотелось бы без этого обойтись.

Comments

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

Покажите пример данных и пример искомого результата для них.

[Слава]

Исходная таблица users

1, Иван, 22.01.2003
2, Петр, 11.02.1955
3, Катя, 05.01.1995

Потом вносим 2 изменения в Катю, одно изменение в Петю и третье изменение в Катю. Получается так:

names_history:

1, Екатерина, 3
2, Екатерина Ивановна, 3
3, Петя, 2
4, Екатерина Ивановна, 3

birthdays_history

1, 05.01.1995, 3
2, 05.01.1995, 3
3, 11.02.1955, 2
4, 07.01.1995, 3

для пользователя Катя должна сформироваться такая выборка в результате запроса. Чтоб вывести только записи с user_id = 3 .

Екатерина, 05.01.1995, 3
Екатерина Ивановна, 05.01.1995, 3
Екатерина Ивановна, 07.01.1995, 3

Я бы по id их связал между собой, но вдруг id будет с разрывом. Поэтому надо связывать их именно по измененному по рядковому номеру записи для данного пользователя.

из таблицы Имен первая с первой таблицы дней рождения вторая со второй и т.п.

[Сергей Водаков]

Вы как-то не внятно сформулировали проблему, и создается такое впечатление что проблемы как-бы и нет. По крайней мере я вас понял так, что если сделать выборку по таблице users и левым соединением по user_id прицепить к ней таблицы names и birthays, то и получится то что вы хотите.

[Слава]

нелегко сформулировать. все было бы хорошо, но может так получиться, что нумерация не сквозная, а например, у таблицы names id начинаются со 100, а у таблицы birthdays с 1 поэтому их конектить по id этих двух таблиц не годится.

[Everything_is_bad]

Слава, а при чем тут вообще "сквозная нумерация", ты придумал какую-то несуществующую проблему.

[Ivan Ustûžanin]

задача какая-то странная, но теоретическое решение есть
т.к. не указана СУБД, то примеров не будет, но суть: джойнить две таблицы изменений по user_id и по результату оконной функции rank() с разделением по user_id и сортировкой по id, типа rank() OVER (PARTITION BY user_id ORDER BY id ASC), но для этого нужно использовать или CTE, или подзапросы во FROM (забыл как оно называется)

Answer 1

[alexalexes]

Чтобы любую таблицу обложить историей изменения, нужно создать следующую обвязку:
1. Создать таблицу <название исходной таблицы>__history
Поля таблицы:
Первичный ключ - history_id,
Копируем названия полей исходной таблицы как есть (всем полям даете возможность быть null).
Делаем внешние ключи таблицы истории к исходной таблице (обычно id- исходной таблицы нужно привязать) и к другим таблицам, такие же как в исходной таблице, но опускаем ограничение уникальности ключей. Не используем каскадное обновление значение ключей.
Добавляете поле - вид операции над строкой исходной таблицы (вставка, обновление, удаление).
И самое главное, в таблице истории должно быть поле next_history_id - делаете по нему связанный список истории изменения одного поля. Как это поле работает? - см. пункт 2.
2. Создаете триггер/ы на события вставки, изменения, удаления для исходной таблицы в ней пишите тело со следующими действиями:
Читаете id тронутой событием записи из исходной таблицы.
Определяете history_id по данной id, где next_history_id is null - запоминаете в локальной переменной как before_history_id.
Если действие не удаление, то создаете запись в таблице истории из new-полей, которые пришли в insert/update для исходной таблицы, добавляете значение поля вида действия, next_history_id делаете null, получаете history_id этой вставленной записи - записываете в переменную new_history_id.
Если действие - удаление, то new поля вычитывать не нужно, нужно записать значение поля вида действия, next_history_id делаете null, получаете history_id этой вставленной записи - записываете в переменную new_history_id.
Делаете обновление поля next_history_id = new_history_id у записи истории со значением ключа before_history_id.
Тело триггера готово.
На любое действие в исходной таблице будет создаваться запись в истории, с указанием действия, а также будет формироваться цепочка изменений конкретной записи через атрибут next_history_id.
Чтобы вычитать последнее изменение по строчке исходной таблицы, нужно обратиться в таблицу истории по id от исходной таблицы и next_history_id is null.