join table to recursive query of other table in potresql - Postgresql

anonymous
February 24, 2023
248 views
0 votes
2 Answers

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_id	topics_id
100	3
100	6
101	1

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_id	name	topics_name	topics_id
100	1+2+3+4	sum values	6
100	1+2+3+4	Arithmetics	5
100	1+2+3+4	Progression	3
100	1+2+3+4	Algebra	2
100	1+2+3+4	Mathematics	1
101	1+2	Mathematics	1

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

;

but I don’t know ho to join it to task by id.
How should I solve this problem?

Answers

First cte (WITH RECURSIVE) is to get topics parents,
cte2 to convert the array of topics to rows,
Then we join data together to get the expected result.

  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
),
cte2 as (
  select *, unnest(path) AS linked_id
  from topic_parent
)
select task_id, max(task) as task_name, max(name) as topic_name, linked_id as topic_id
from cte2 c
inner join tasks_topics t on c.id = t.topics_id
inner join tasks t2 on t2.id = t.task_id
inner join topics t3 on t3.id = c.linked_id
group by task_id, linked_id
order by task_id asc, linked_id desc

Demo here

_task_id = 100
topics_from = (
    session.query(Tasks, Topics)
    .join(Topics, Tasks.topics)
    .filter(Tasks.id == _task_id)  # optional
).cte(name="alltopic", recursive=True)

# union or union_all depending on if you want to filter out duplicates (top level)
allq = topics_from.union(
    session.query(Tasks, Topics)
    .filter(Topics.id == topics_from.c.parent_id)
    .filter(Tasks.id == topics_from.c.id)
)

should produce result as per below:

(100, '1+2+3+4', 5, 'sum values', 3)
(100, '1+2+3+4', 6, 'Number sequences', 4)
(100, '1+2+3+4', 3, 'Arithmetics', 1)
(100, '1+2+3+4', 4, 'Progression', 2)
(100, '1+2+3+4', 1, 'Mathematics', 0)
(100, '1+2+3+4', 2, 'Algebra', 1)

via SQL along these lines:

WITH RECURSIVE alltopic(id, task, id_1, name, parent_id) AS
  (SELECT tasks.id AS id,
          tasks.task AS task,
          topics.id AS id_1,
          topics.name AS name,
          topics.parent_id AS parent_id
   FROM tasks
   JOIN tasks_topics AS tasks_topics_1 ON tasks.id = tasks_topics_1.task_id
   JOIN topics ON topics.id = tasks_topics_1.topics_id
                topics.parent_id AS topics_parent_id
   FROM tasks,
        topics,
        alltopic
   WHERE topics.id = alltopic.parent_id
     AND tasks.id = alltopic.id)
SELECT alltopic.id,
       alltopic.task,
       alltopic.id_1,
       alltopic.name,
       alltopic.parent_id
FROM alltopic

Please signup or login to give your own answer.

Click here to cancel reply.

join table to recursive query of other table in potresql – Postgresql

Answers