Однажды я столкнулся с проблемой, когда нужно было в одной таблице создать 2 столбца и скопировать данные из колонки одной таблицы в другую с привязкой к идентификатору из первой таблицы. Звучит немного сложно, но по факту оказалось все очень просто. Давайте же вместе это сделаем!
Изначально я столкнулся с похожей задачей на прошлой работе. Самое печальное, что тогда я ее не смог решить ни я, ни мой лид. Сейчас, столкнувшись с такой же задачей, я крепко выругался — ее решить нужно было обязательно, к тому же опыта у меня набралось уже достаточно много. Возможно, решение пришло мне из-за того, что я делал эту задачу с помощью knex — помощника в создании запросов для PostgreSQL, CockroachDB, MSSQL, MySQL, MariaDB, SQLite3, Oracle, и Amazon Redshift, созданного быть гибким, портативным и приятным в использовании (прим. — официальный сайт). И именно таким knex и оказался, чему я несказанно рад.
Для начала опишу более подробно проблему и приведу схемы таблиц, затем расскажу как и с помощью чего я решил эту проблему.
Проблема
У нас есть 2 связанные таблицы. В одной из таблиц есть внешний ключ, допустим event_id, во второй таблице у нас первичный ключ — это самое id. Из колонки одной таблицы нам нужно перенести данные в другую, при этом ориентируясь на event_id, тк во второй таблице может быть несколько записей, относящихся к одному event_id.
Мотивация
Вдаваться в подробности почему я решил сделать так а не иначе, я не буду, поскольку на проекте, где эта ситуация случилась, была определенная архитектура под определенные требования, но требования изменились и пришлось менять архитектуру, чтобы приложение работало согласно требованиям.
Дано
Как описывал выше, у нас есть 2 таблицы
events, колонки —id,name,datenotifications, колонки —id,text,event_id
Как написано в проблеме — мы переносим одну колонку с сохранением отношения к внешнему ключу, в данном случае — event_id. Переносить будем колонку date.
Решение
Выше я упоминал, что на проекте используется knex, и что он и документация постгрес помогли найти верное решение, которое было максимально короткое и элегантное. Но изначально были ошибочные решения, которые могли стоить большого количества использованной памяти на выполнении скрипта (в обоих таблицах суммарно десятки тысяч записей). Я залил решение на мой гитхаб я залил решение, разбив его на коммиты, чтобы вы могли пошагово проследить за тем как решалась задача. Также я добавил туда дамп истории SQL запросов с сайта https://sqliteonline.com/.
Кстати по поводу https://sqliteonline.com/ (не реклама). Это очень крутая и удобная в использовании sql песочница, в которой для проверки различных решений или построения таблиц. В нем можно выбрать различные варианты sql а также произвести множество действий, например импортировать/экспортировать sql, базу данных, либо историю запросов в формате sql. Поскольку я не могу использовать базу данных и код проекта, я решил создать таблицы с нужной структурой онлайн и этот инструмент мне показался самым удобным из топ 5 поиска.
Первый шаг
Процитирую начало статьи knex — помощник в создании запросов для PostgreSQL, CockroachDB, MSSQL, MySQL, MariaDB, SQLite3, Oracle, и Amazon Redshift, созданный быть гибким, портативным и приятным в использовании (прим. — официальный сайт).
И я полностью согласен с этим высказыванием.
Knex хороший инструмент для работы с sql в JavaScript/TypeScript, который позволяет, действительно легко, работать со схемой таблиц и с данными. В наличии есть возможность как выполнить любой запрос с помощью knex.schema.raw или knex.raw (первый для работы со схемой бд, второй для работы с данными), так и воспользоваться конкретными методами для работы с информацией и схемой.
Гугл помог познакомится с knex’ом поближе и воспоминания о невыполнимой задаче перестали так сильно давить. Ведь knex позволяет построить цепочку запросов с помощью промисов, возвращая (если нужно) каждый раз результат работы sql крипта. То есть, теоретически, вы можете выполнить SELECT с каким либо условием, затем использовать полученные данные в условии следующего запроса и так далее.
knex('users')// Type of users is inferred as Pick[]
.select('id')
.select('age')
.then((users) => {
});
(прим. — пример из документации knex)
Решение моей проблемы не заставило себя ждать:
export function up(knex: Knex): Promise {// Cоздаем колонку Date в таблице Notifications.
return knex.schema.table('Notifications', (table) => {
table.specificType('Date', 'date').defaultTo('');
}).then(() => {
return knex.raw(`
SELECT ID, Date from Events;
`);
}).then((data) => {
ID: 1,
Date: ’05-22-2021′,
let query: string = '';
data.rows.forEach((row: Record) => {
query += `UPDATE Notifications(Date)
VALUES('${row.date')
WHERE EventID='${row.id}';`;
});
return knex.raw(query);
});
}
Вроде бы, хорошо — скрипт выполняется и записывает все данные как нужно, но это не самое элегантное решение (перфекционист во мне говорит, что это самое неэлегантное решение). Я подумал, что произойдет с данным скриптом, если будет 1000 записей, а если 1000000, а если 1000000000? В общем это потенциально быть бутылочное горлышко. Поэтому было принято решение сделать лучше.
Финальное решение
Поиск более элегантного решения привел меня к документации постгрес на сайте postgrespro.ru:
UPDATE accounts SET contact_first_name = first_name,
contact_last_name = last_name
FROM salesmen WHERE salesmen.id = accounts.sales_id;
Это именно то, что мне нужно сделать!
Изменив этот код под себя я получил следующее:
export function up(knex: Knex): Promise {
return knex.schema.table('Notifications', (table) => {
table.specificType('Date', 'date').defaultTo('');
}).then(() => {
return knex.raw(`
UPDATE Notifications
SET Date = Events.date,
FROM Events WHERE Event.ID = Notifications.EventID;
`);
});
}
Запускаем скрипт, проверяем таблицу — все работает.
В случае, если вы не используете knex, можно использовать цепочку SQL запросов (либо один UPDATE-SET-FROM). Листинг SQL запросов с сайта https://sqliteonline.com/ можно найти в моем репозитории, как и код, представленный в статье. Файл с историей sql запросов можно легко импортировать с помощью https://sqliteonline.com/, чтобы проверить, что все работает как надо и поэкспериментировать с запросами. Код из репозитория разбит на коммиты, чтобы можно было посмотреть решение пошагово. Если же у вас появятся какие-либо вопросы — пишите мне, я постараюсь на них ответить.
Заключение
Три строки SQL кода — все, что нам нужно, чтобы скопировать данные из колонки одной таблицы в другую с сохранением связи (в нашем случае по ID события).
Всю мою карьеру программистом меня всегда поражало то, сколько различных путей для решения задач существует, а также то, что порой, казалось бы, сложная задача решается очень просто. Достаточно немного погрузиться в чтение документации…
