есть запрос WITH RECURSIVE topicparent(id, path) AS (SELECT id, ARRAY[id] FROM topics…
- User
есть запрос WITH RECURSIVE topic_parent(id, path) AS (SELECT id, ARRAY[id] FROM topics WHERE parent = 0 UNION SELECT t.id, path || t.id FROM topics t INNER JOIN topic_parent rt ON rt.id = t.parent ), limited_tasks AS (SELECT * FROM public.tasks ORDER BY id ASC LIMIT 100 ) SELECT limited_tasks.id, limited_tasks.task, limited_tasks.answer, array_agg(DISTINCT classes.name) AS class_name, array_agg(DISTINCT topics.name) AS topic_name, array_agg(DISTINCT authors.name) AS authors_name FROM limited_tasks INNER JOIN tasks_topics ON limited_tasks.id = tasks_topics.task_id INNER JOIN (SELECT *, unnest(path) AS linked_id FROM topic_parent) cte2 ON cte2.id = tasks_topics.topics_id INNER JOIN topics ON topics.id = cte2.linked_id INNER JOIN tasks_classes ON limited_tasks.id = tasks_classes.tasks_id INNER JOIN classes ON classes.id = tasks_classes.classes_id INNER JOIN tasks_authors ON limited_tasks.id = tasks_authors.tasks_id INNER JOIN authors ON authors.id = tasks_authors.authors_id GROUP BY limited_tasks.id, limited_tasks.task, limited_tasks.answer ORDER BY limited_tasks.id ASC Этот запрос рекурсивно добавляет topics верно?
- Assistant
Да, этот запрос использует рекурсивное выражение WITH RECURSIVE, чтобы добавить темы (topics) рекурсивно. Он начинает с выборки тем верхнего уровня, у которых parent = 0, а затем рекурсивно объединяет их с их дочерними темами.
- User
есть еще одна таблица exams. В ней есть такие же столбцы, как и в Topics. Я хочу также добавить таблицу Exams. Как я это могу сделать?
- Assistant
Для добавления таблицы "exams" в запрос, вам нужно выполнить следующие шаги:
- Изменить первую часть рекурсивного выражения, чтобы выбирать данные из таблицы "exams" вместо "topics":
WITH RECURSIVE topic_parent(id, path) AS (SELECT id, ARRAY[id] FROM exams WHERE parent = 0 UNION SELECT e.id, path || e.id FROM exams e INNER JOIN topic_parent rt ON rt.id = e.parent )-
Заменить все упоминаемые в запросе "topics" соответствующими столбцами в таблице "exams". Например, если вам нужно заменить "topics.name", используйте "exams.name".
-
Скорректировать все соответствующие JOIN-условия с использованием таблицы "exams". Например, замените "tasks_topics.topics_id" на "tasks_topics.exams_id" и "topics.id" на "exams.id".
SELECT limited_tasks.id, limited_tasks.task, limited_tasks.answer, array_agg(DISTINCT classes.name) AS class_name, array_agg(DISTINCT exams.name) AS exam_name, array_agg(DISTINCT authors.name) AS authors_name FROM limited_tasks INNER JOIN tasks_topics ON limited_tasks.id = tasks_topics.task_id INNER JOIN (SELECT *, unnest(path) AS linked_id FROM topic_parent) cte2 ON cte2.id = tasks_topics.exams_id INNER JOIN exams ON exams.id = cte2.linked_id -- остальные JOIN-условия и GROUP BYОбратите внимание, что вы также должны убедиться, что столбцы таблицы "exams" имеют такие же имена, как и столбцы таблицы "topics", чтобы сохранить совместимость с оригинальным запросом.
- User
WITH RECURSIVE topic_parent(id, path) AS (SELECT id, ARRAY[id] FROM topics WHERE parent = 0 UNION SELECT t.id, path || t.id FROM topics t INNER JOIN topic_parent rt ON rt.id = t.parent ), RECURSIVE exams_parent(id, path2) AS (SELECT id, ARRAY[id] FROM exams WHERE parent_of_exam = 0 UNION SELECT t2.id, path2 || t2.id FROM exams t2 INNER JOIN exams_parent rt2 ON rt2.id = t2.parent_of_exam ), limited_tasks AS (SELECT * FROM public.tasks ORDER BY id ASC LIMIT 90 ) SELECT limited_tasks.id, limited_tasks.task, limited_tasks.answer, array_agg(DISTINCT classes.name) AS class_name, array_agg(DISTINCT topics.name) AS topic_name, array_agg(DISTINCT authors.name) AS authors_name FROM limited_tasks INNER JOIN tasks_topics ON limited_tasks.id = tasks_topics.task_id INNER JOIN (SELECT *, unnest(path) AS linked_id FROM topic_parent) cte2 ON cte2.id = tasks_topics.topics_id INNER JOIN topics ON topics.id = cte2.linked_id INNER JOIN tasks_classes ON limited_tasks.id = tasks_classes.tasks_id INNER JOIN classes ON classes.id = tasks_classes.classes_id INNER JOIN tasks_authors ON limited_tasks.id = tasks_authors.tasks_id INNER JOIN authors ON authors.id = tasks_authors.authors_id INNER JOIN solution ON limited_tasks.id = solution.tasks_id GROUP BY limited_tasks.id, limited_tasks.task, limited_tasks.answer ORDER BY limited_tasks.id ASC ERROR: syntax error at or near "exams_parent" LINE 11: ON rt.id = t.parent ), RECURSIVE exams_parent(id, pa..
- Assistant
В запросе вы применяете два рекурсивных выражения подряд (
topic_parentиexams_parent), но синтаксисWITH RECURSIVEне позволяет объявлять несколько рекурсивных выражений в одном запросе. Вам нужно объединить оба рекурсивных выражения в одно и использовать их вместе.Вот как вы можете объединить рекурсивные выражения
topic_parentиexams_parent:WITH RECURSIVE recursive_parent(id, path, parent_type) AS ( -- Выборка тем верхнего уровня (SELECT id, ARRAY[id], 'topic' AS parent_type FROM topics WHERE parent = 0 UNION SELECT t.id, path || t.id, 'topic' AS parent_type FROM topics t INNER JOIN recursive_parent rp ON rp.id = t.parent) -- Объединение с рекурсивным выражением для экзаменов UNION ALL -- Выборка экзаменов верхнего уровня (SELECT id, ARRAY[id], 'exam' AS parent_type FROM exams WHERE parent_of_exam = 0 UNION SELECT e.id, path || e.id, 'exam' AS parent_type FROM exams e INNER JOIN recursive_parent rp ON rp.id = e.parent_of_exam) ) -- Остаток запроса SELECT limited_tasks.id, limited_tasks.task, limited_tasks.answer, array_agg(DISTINCT classes.name) AS class_name, array_agg(DISTINCT topics.name) AS topic_name, array_agg(DISTINCT exams.name) AS exam_name, array_agg(DISTINCT authors.name) AS authors_name FROM limited_tasks INNER JOIN tasks_topics ON limited_tasks.id = tasks_topics.task_id INNER JOIN recursive_parent rp ON rp.id = tasks_topics.topics_id LEFT JOIN topics ON topics.id = rp.id AND rp.parent_type = 'topic' LEFT JOIN exams ON exams.id = rp.id AND rp.parent_type = 'exam' -- Другие JOIN-условия и GROUP BY ORDER BY limited_tasks.id ASC;Обратите внимание на добавление новой колонки
parent_type, чтобы отличить между темами и экзаменами. Затем вы можете использовать это значение для выбора соответствующей таблицы для JOIN. - User
WITH RECURSIVE recursive_parent(id, path, parent_type) AS ( (SELECT id, ARRAY[id], 'topic' AS parent_type FROM topics WHERE parent = 0 UNION SELECT t.id, path || t.id, 'topic' AS parent_type FROM topics t INNER JOIN recursive_parent rp ON rp.id = t.parent) UNION ALL (SELECT id, ARRAY[id], 'exams' AS parent_type FROM exams WHERE parent_of_exam = 0 UNION SELECT e.id, path || e.id, 'exams' AS parent_type FROM exams e INNER JOIN recursive_parent rp ON rp.id = e.parent_of_exam) ), limited_tasks AS (SELECT * FROM public.tasks ORDER BY id ASC LIMIT 90 ) SELECT limited_tasks.id, limited_tasks.task, limited_tasks.answer, array_agg(DISTINCT classes.name) AS class_name, array_agg(DISTINCT topics.name) AS topic_name, array_agg(DISTINCT authors.name) AS authors_name FROM limited_tasks INNER JOIN tasks_topics ON limited_tasks.id = tasks_topics.task_id INNER JOIN (SELECT *, unnest(path) AS linked_id FROM topic_parent) cte2 ON cte2.id = tasks_topics.topics_id INNER JOIN topics ON topics.id = cte2.linked_id INNER JOIN tasks_classes ON limited_tasks.id = tasks_classes.tasks_id INNER JOIN classes ON classes.id = tasks_classes.classes_id INNER JOIN tasks_authors ON limited_tasks.id = tasks_authors.tasks_id INNER JOIN authors ON authors.id = tasks_authors.authors_id INNER JOIN solution ON limited_tasks.id = solution.tasks_id GROUP BY limited_tasks.id, limited_tasks.task, limited_tasks.answer ORDER BY limited_tasks.id ASC ERROR: recursive reference to query "recursive_parent" must not appear within its non-recursive term LINE 8: INNER JOIN recursive_parent rp ON rp.id = t.parent)
- Assistant
Проблема возникает из-за того, что в рекурсивном выражении
topic_parentвы используете ссылку на выражениеrecursive_parent, которое объявлено позже в запросе.Вам нужно переместить рекурсивное выражение
recursive_parentвыше, перед использованием его внутриtopic_parent. Таким образом, вы объявитеrecursive_parentпрежде, чем он будет ссылаться на негоtopic_parent.Ниже приведен измененный запрос с правильным порядком рекурсивных выражений:
WITH RECURSIVE recursive_parent AS ( -- Выборка тем верхнего уровня (SELECT id, ARRAY[id] AS path, 'topic' AS parent_type FROM topics WHERE parent = 0 UNION SELECT t.id, path || t.id, 'topic' AS parent_type FROM topics t INNER JOIN recursive_parent rp ON rp.id = t.parent) UNION ALL -- Выборка экзаменов верхнего уровня (SELECT id, ARRAY[id], 'exams' AS parent_type FROM exams WHERE parent_of_exam = 0 UNION SELECT e.id, path || e.id, 'exams' AS parent_type FROM exams e INNER JOIN recursive_parent rp ON rp.id = e.parent_of_exam) ), limited_tasks AS ( SELECT * FROM public.tasks ORDER BY id ASC LIMIT 90 ) SELECT limited_tasks.id, limited_tasks.task, limited_tasks.answer, array_agg(DISTINCT classes.name) AS class_name, array_agg(DISTINCT topics.name) AS topic_name, array_agg(DISTINCT authors.name) AS authors_name FROM limited_tasks INNER JOIN tasks_topics ON limited_tasks.id = tasks_topics.task_id INNER JOIN recursive_parent rp ON rp.id = tasks_topics.topics_id LEFT JOIN topics ON topics.id = rp.id AND rp.parent_type = 'topic' LEFT JOIN exams ON exams.id = rp.id AND rp.parent_type = 'exams' -- Другие JOIN-условия и GROUP BY ORDER BY limited_tasks.id ASC;Обратите внимание, что я также удалил использование
unnest(path), так как рекурсивное выражение уже содержит путьpath, который можно использовать напрямую. - User
WITH RECURSIVE recursive_parent AS ( (SELECT id, ARRAY[id] AS path, 'topic' AS parent_type FROM topics WHERE parent = 0 UNION SELECT t.id, path || t.id, 'topic' AS parent_type FROM topics t INNER JOIN recursive_parent rp ON rp.id = t.parent) UNION ALL (SELECT id, ARRAY[id], 'exams' AS parent_type FROM exams WHERE parent_of_exam = 0 UNION SELECT e.id, path || e.id, 'exams' AS parent_type FROM exams e INNER JOIN recursive_parent rp ON rp.id = e.parent_of_exam) ), limited_tasks AS ( SELECT * FROM public.tasks ORDER BY id ASC LIMIT 90 ) SELECT limited_tasks.id, limited_tasks.task, limited_tasks.answer, array_agg(DISTINCT classes.name) AS class_name, array_agg(DISTINCT topics.name) AS topic_name, array_agg(DISTINCT authors.name) AS authors_name FROM limited_tasks INNER JOIN tasks_topics ON limited_tasks.id = tasks_topics.task_id INNER JOIN recursive_parent rp ON rp.id = tasks_topics.topics_id LEFT JOIN topics ON topics.id = rp.id AND rp.parent_type = 'topic' LEFT JOIN exams ON exams.id = rp.id AND rp.parent_type = 'exams' INNER JOIN tasks_classes ON limited_tasks.id = tasks_classes.tasks_id INNER JOIN classes ON classes.id = tasks_classes.classes_id INNER JOIN tasks_authors ON limited_tasks.id = tasks_authors.tasks_id INNER JOIN authors ON authors.id = tasks_authors.authors_id INNER JOIN solution ON limited_tasks.id = solution.tasks_id GROUP BY limited_tasks.id, limited_tasks.task, limited_tasks.answer ORDER BY limited_tasks.id ASC ERROR: recursive reference to query "recursive_parent" must not appear within its non-recursive term LINE 8: INNER JOIN recursive_parent rp ON rp.id = t.parent)
- Assistant
Ошибки в вашем запросе возникают из-за каких-то других проблем, не связанных с рекурсивным выражением. Ошибки могут быть вызваны неправильными JOIN-условиями, отсутствующими столбцами или неправильным порядком операторов.
Ниже приведены некоторые рекомендации для проверки и исправления запроса:
-
Убедитесь, что все таблицы (topics, exams, limited_tasks, tasks_topics, tasks_classes, classes, tasks_authors, authors, solution) существуют в вашей базе данных и соответствующие столбцы присутствуют.
-
Проверьте правильность JOIN-условий для каждого JOIN-оператора. Убедитесь, что столбцы, используемые для соединения таблиц, существуют и имеют правильные имена.
-
Удостоверьтесь, что таблица
recursive_parentобъявлена до использующих ее JOIN-операторов. Это может быть проведено перемещением объявленияrecursive_parentперед первым использованием в запросе или путем помещения всего определенияWITH RECURSIVEв самом начале запроса. -
Проверьте наличие правильного синтаксиса для остальных операторов и выражений в запросе.
Если после выполнения этих шагов ошибка все еще возникает, пожалуйста, предоставьте дополнительные детали об ошибках и структуре базы данных, чтобы я смог помочь вам более точно.
-