Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

I have two tables with many to many relationship in Postresql database. The first Topic…

  1. User

    I have two tables with many to many relationship in Postresql database.

    The first Topic table consists of three columns. Their names are id, name and parent. Topic table has hierarchial structure:

    | id | name |parent| |----|-----------------|—| | 1 | Mathematics |0| | 2 | Algebra |1| | 3 | Progression |2| | 4 | Number sequences|3| | 5 | Arithmetics |1| | 6 | sum values |5|

    The second table has name task table. It has two columns - task id and task text:

    | id | task | | – | ---------| | 100| 1+2+3+4 | | 101| 1+2 |

    tasks_topics table is

    task_idtopics_id
    1003
    1006
    1011

    I need to get the table jointed to recursive query of topic. It should consists of four columns. THe first column should be task_id, the second task text, the third should be al parent names of topic tasks. And the last one should be parent topic id.

    The result should be:

    task_idnametopics_nametopics_id
    1001+2+3+4sum values6
    1001+2+3+4Arithmetics5
    1001+2+3+4Progression3
    1001+2+3+4Algebra2
    1001+2+3+4Mathematics1
    1011+2Mathematics1

    I can make recursive query to topic table

    WITH RECURSIVE topic_parent AS ( SELECT id, name, parent FROM topics WHERE id = 3 UNION SELECT topics.id, topics.name, topics.parent FROM topics INNER JOIN topic_parent ON topic_parent.parent = topics.id ) SELECT * FROM topic_parent

    ;

  2. Assistant

    This query will return all the parent topics for the topic with id 3.

    To get the desired result with the task and topics tables, we can join the task_topics table with the topic table, and then use a recursive CTE to get all the parent topics for each task, and finally join it with the task table to get the task text.

    Here is the complete query:

    WITH RECURSIVE topic_cte AS ( SELECT id AS topic_id, name AS topic_name, parent AS parent_id FROM topics UNION ALL SELECT topics.id AS topic_id, topics.name AS topic_name, topics.parent AS parent_id FROM topics JOIN topic_cte ON topics.id = topic_cte.parent_id ), task_topics_cte AS ( SELECT task_id, topic_id, topic_name, parent_id FROM task_topics JOIN topic_cte ON task_topics.topics_id = topic_cte.topic_id ) SELECT task_id, task, array_to_string(array_agg(topic_name), ' > ') AS topics_name, parent_id AS topics_id FROM task_topics_cte JOIN tasks ON task_topics_cte.task_id = tasks.id GROUP BY task_id, task, parent_id ORDER BY task_id;

    Explanation:

    • The first CTE (Common Table Expression), topic_cte, is a recursive CTE that gets all the parent topics for each topic in the Topic table. It starts with a base query that selects the id, name and parent of each topic with no parent, and then recursively joins with the same query selecting the id, name and parent of each child topic until there are no more parent topics left.

    • The second CTE, task_topics_cte, joins the task_topics table with the topic_cte CTE on the topic_id column, and selects the task_id, topic_id, topic_name and parent_id.

    • The main query joins the task_topics_cte CTE with the tasks table on the task_id column to get the task text. It then groups the result by task_id, task and parent_id, and concatenates the topic names into a single column using the array_agg and array_to_string functions, separated by " > ". Finally, it orders the result by task_id.

    Note: This query assumes that the root topic has parent_id = 0. If it has a different value, you will need to adjust the query accordingly.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.