Skip to content
The Chat Archive
gpt-3.5-turbo-0613· OpenAI WildChat-4.8M (AI2)

есть запрос WITH RECURSIVE topicparent(id, path) AS (SELECT id, ARRAY[id] FROM topics…

  1. 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 верно?

  2. Assistant

    Да, этот запрос использует рекурсивное выражение WITH RECURSIVE, чтобы добавить темы (topics) рекурсивно. Он начинает с выборки тем верхнего уровня, у которых parent = 0, а затем рекурсивно объединяет их с их дочерними темами.

  3. User

    есть еще одна таблица exams. В ней есть такие же столбцы, как и в Topics. Я хочу также добавить таблицу Exams. Как я это могу сделать?

  4. Assistant

    Для добавления таблицы "exams" в запрос, вам нужно выполнить следующие шаги:

    1. Изменить первую часть рекурсивного выражения, чтобы выбирать данные из таблицы "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 )
    
    1. Заменить все упоминаемые в запросе "topics" соответствующими столбцами в таблице "exams". Например, если вам нужно заменить "topics.name", используйте "exams.name".

    2. Скорректировать все соответствующие 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", чтобы сохранить совместимость с оригинальным запросом.

  5. 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..

  6. 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.

  7. 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)

  8. 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, который можно использовать напрямую.

  9. 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)

  10. Assistant

    Ошибки в вашем запросе возникают из-за каких-то других проблем, не связанных с рекурсивным выражением. Ошибки могут быть вызваны неправильными JOIN-условиями, отсутствующими столбцами или неправильным порядком операторов.

    Ниже приведены некоторые рекомендации для проверки и исправления запроса:

    • Убедитесь, что все таблицы (topics, exams, limited_tasks, tasks_topics, tasks_classes, classes, tasks_authors, authors, solution) существуют в вашей базе данных и соответствующие столбцы присутствуют.

    • Проверьте правильность JOIN-условий для каждого JOIN-оператора. Убедитесь, что столбцы, используемые для соединения таблиц, существуют и имеют правильные имена.

    • Удостоверьтесь, что таблица recursive_parent объявлена до использующих ее JOIN-операторов. Это может быть проведено перемещением объявления recursive_parent перед первым использованием в запросе или путем помещения всего определения WITH RECURSIVE в самом начале запроса.

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

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