Как реализовать широкий многоуровневый запрос PostgreSQL с самообъединением?
У меня есть несколько строк, которые представляют собой person. У каждого person есть столбец для bio_mother _uuid и bio_father_uuid. Я использовал эту страницу (я упоминаю об этом, потому что на нее есть ссылка в аналогичном вопросе) для реализации этого соединения.
SELECT
base.uuid base_uuid, base.created_at base_created_at, base.givenname base_givenname, base.bio_mother_uuid base_bio_mother_uuid, base.bio_father_uuid base_bio_father_uuid,
mother.uuid mother_uuid, mother.created_at mother_created_at, mother.givenname mother_givenname, mother.bio_mother_uuid mother_bio_mother_uuid, mother.bio_father_uuid mother_bio_father_uuid,
father.uuid father_uuid, father.created_at father_created_at, father.givenname father_givenname, father.bio_mother_uuid father_bio_mother_uuid, father.bio_father_uuid father_bio_father_uuid
FROM person AS base
LEFT JOIN person AS mother ON base.bio_mother_uuid = mother.uuid
LEFT JOIN person as father ON base.bio_father_uuid = father.uuid;
Я надеюсь разложить строки в эту структуру Go.
type Person struct{
UUID string
Givenname string
Bio_mother *Person
}
Если я делаю запрос, используя приведенный выше запрос, я получаю NULL для каждого человека, у которого нет данных о матери или отце (это хорошо), но затем при построении данных о матери/отце я в конечном итоге создаю неполные экземпляры Person, поскольку я не в запросе нет «прародительских» данных. Я мог бы решить эту проблему, внедрив логику в код для повторного запроса, если существует UUID, но это значительно замедляет работу программы, чем каким-то образом делать это в запросе.
Для меня это учебный проект, но я не совсем уверен, что нужно гуглить, чтобы получить хороший ответ.
Как я могу адаптировать свой запрос для обработки моего варианта использования и возврата только в одной строке? Это будет связано с рекурсивным CTE, но я не совсем уверен, как его реализовать после просмотра некоторых страниц учебника по PostgreSQL.
Редактировать: Я понял, что отсутствие данных означает необходимость динамически называть эти столбцы или, может быть, какой-то код? Может быть, аддитивные префиксы? В этом примере вывода я просто использую «псевдоним». Обратите внимание, что Гертруде не хватает данных о матери: в строках есть пробел. Это потому, что в модели всегда на первом месте были матери, и я не уверен, что вообще согласен с тем, что моделирование модели имеет смысл, но это модель, которую я могу придумать прямо сейчас для этих данных. Пожалуйста, дайте мне знать, если я стреляю не в том направлении.
(Я также сократил столбцы до «другие вещи» после первых нескольких итераций для удобства чтения, и я сажусь в поезд без Wi-Fi, поэтому хочу заранее подготовить этот вопрос и отключить его)
Пример ввода и вывода
Пример данных
Желаемый результат
@NickW Абсолютлети. Я перефразировал свой вопрос, чтобы попытаться лучше объяснить мою проблему, и фактически задал вопрос там :) Спасибо за отзыв о том, как лучше задать вопрос.
Добавьте несколько строк примеров данных и ожидаемый результат. Рекурсивный CTE полностью способен возвращать нужные вам данные. Альтернативные, явные левые соединения также могут это сделать.
@TheImpaler добавил примеры данных! Я думаю, что то, как будет работать именование, меня сбивает с толку. Я попробовал создать несколько рекурсивных CTE, но обнаружил, что некоторые руководства конфликтуют друг с другом, и мне не удалось экстраполировать их в свой вариант использования. Тогда я решил опубликовать этот вопрос.
Как далеко назад вы хотите пойти? (если мать представлена как Person, то включаем ли мы мать матери?). В зависимости от того, что вам нужно, это может быть не так сложно, как вы думаете (см. эту скрипту), но требования все еще не совсем ясны.
@Brits Я думаю, что это произвольное число. На самом деле, он никогда не будет моделировать более двух поколений, поэтому, возможно, для безопасности можно использовать 5 уровней.
@Sai - Хорошо, если вы хотите вернуться так далеко назад, у вас может получиться много столбцов (32 - количество атрибутов на человека). Я бы предложил вернуть запись каждого человека и объединить ее в вашем коде (по одной записи на человека, а не привязывать все к одной строке). Можно было бы сделать это в одну строку, но вам нужно будет написать код для демаршалинга, и я чувствую, что это будет более запутанно. Альтернативой может быть возврат данных в формате JSON (немного сложнее написать запрос, но, вероятно, его легче демаршалировать).
Да, я пошел по этому пути, потому что заметил, как запросы для каждого родственника сильно замедляли работу базы данных, поэтому я попытался попытаться применить больше логики в запросах, чтобы справиться с этим. Думаю, попробую найти какую-нибудь комбинацию. Знаете ли вы, как будет выглядеть запрос для 3 поколений (1 ребенок, 2 родителя, 4 бабушки и дедушки). Не знаю, как обрабатывать динамическое именование такого количества строк или как писать этот рекурсивный запрос, и простое написание этого запроса вручную кажется многословным.
Ответы 1
Примечание. Это ответ, отражающий обсуждение в комментариях.
Учитывая пример структуры/данных:
create table people
(
uuid text,
givenname text,
surname text,
bio_mother_uuid text,
bio_father_uuid text
);
insert into people (uuid, givenname, surname, bio_mother_uuid, bio_father_uuid)
values ('aa', 'david', 'smith', 'ab', 'ac'),
('ab', 'mary', 'smith', 'ad', 'ae'),
('ac', 'john', 'smith', 'af', 'ag'),
('ad', 'gertude', 'snow', null, 'ah'),
('ae', 'Richard', 'snow', null, null),
('af', 'Katie', 'smith', null, null),
('ag', 'Mathew', 'smith', null, null),
('ah', 'Winston', 'Snow', null, null);
Вы можете запустить рекурсивный CTE, например:
with recursive t as (
select
p.uuid as request_uuid,
p.uuid as uuid,
p.givenname as givenname,
p.surname as surname,
p.bio_mother_uuid as bio_mother_uuid,
p.bio_father_uuid as bio_father_uuid,
0 as generation
from people p
union all
select
child.request_uuid as request_uuid,
p.uuid as uuid,
p.givenname as givenname,
p.surname as surname,
p.bio_mother_uuid as bio_mother_uuid,
p.bio_father_uuid as bio_father_uuid,
child.generation + 1 as generation
from
t child join
people p on p.uuid = child.bio_mother_uuid or p.uuid = child.bio_father_uuid
)
select * from t where request_uuid = 'aa' and generation < 3
Результат (рабочий пример):
Затем это предоставляет всю информацию, необходимую для заполнения вашей структуры (для этого потребуется некоторый специальный код). Получение всех данных в одном запросе, как этот, вероятно, будет быстрее, чем выполнение нескольких запросов (но это будет зависеть от ряда факторов).
Хотя могут быть способы вернуть все эти данные в одну строку, я чувствую, что с этим будет сложнее справиться, чем с описанным выше (вам все равно понадобится специальный код для демаршалинга, и у вас возникнет дополнительная сложность, связанная с необходимостью обработки различное количество столбцов). Альтернативой может быть возврат структуры в формате JSON (это усложнит SQL, но упростит ваш код Go).
Отлично! Не могли бы вы указать мне какие-либо ресурсы, позволяющие как вернуть это в одной строке, так и вернуть JSON. Я думаю, что попробую все разные варианты и посмотрю, что мне скажут тесты. Благодаря тонну!
На самом деле только функции Postgres JSON (работать с ними может быть немного неудобно). Скорость вряд ли будет существенно отличаться, поскольку вы по-прежнему будете выполнять один запрос и передавать сопоставимый объем данных. Ref возвращает одну строку; это будет просто запрос с большим количеством самосоединений...
Я думаю, что причина, по которой я хочу, чтобы они все были в одной строке, заключается в том, что если мне нужно запросить несколько строк, я демаршализую строку за строкой буквально через цикл for, поэтому мне придется затем обходить этот итератор и иметь некоторая дополнительная логика, чтобы выяснить, где я нахожусь, и что я не напутал при построении дерева. Но я МОГУ это сделать, поэтому буду работать над этим.
Вероятно, проще сначала просто поместить результаты в map (тогда довольно просто построить дерево, рекурсивно просматривая предков).
Я хотел вернуться и оставить огромное спасибо. Я использовал то, что вы сказали, и поместил результаты на карту, а затем рекурсивно перестроил базовую строку. Вы можете проверить, что я сделал, если хотите, в репозитории GitHub, указанном в сообщении по адресу /routes/person и /models/person. Также имеется ссылка, поэтому, если кто-нибудь еще зайдет на этот пост, они смогут проверить, как я собрал его в окончательном варианте приложения.
Привет, ты вообще-то не задал вопрос. Кроме того, пожалуйста, не ссылайтесь на внешние сайты, вопросы на этом форуме должны быть самостоятельными, поэтому, пожалуйста, четко объясните, с чем вы столкнулись, а затем задайте вопрос по этому поводу.