Finding parents in a many-to-many relationship using Postgres WITH RECURSIVE query

Posted on 22-01-2015 11:11 by graham
Let's imagine we have a many-to-many hierarchical relationship. E.g. let's have a type process that can be a part of any other process. We will define the process type in the following way:
CREATE TABLE processes
(
id serial NOT NULL,
name character varying(20),
CONSTRAINT processes_pkey PRIMARY KEY (id)
)

Any process can be a part of multiple other processes. Then the relationship between a process and its subprocesses with be defined as follows:
CREATE TABLE process_to_process
(
id serial NOT NULL,
parentprocess integer NOT NULL,
childprocess integer,
CONSTRAINT p2p_pkey PRIMARY KEY (id),
CONSTRAINT p2p_childprocess_fkey FOREIGN KEY (childprocess)
REFERENCES processes (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT p2p_processes_fkey FOREIGN KEY (parentprocess)
REFERENCES processes (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)

Now, our task is to find all processes of which a given process P1 is part - directly or indirectly. This can be achieved with the following query:
WITH RECURSIVE subprocesses (id, name, parentprocess, path, depth) AS (
SELECT proc.id, proc.name, p2p.parentprocess, ARRAY[p2p.parentprocess], 1
FROM processes proc
right JOIN p2p ON p2p.childgroup = proc.id
--WHERE p2p.parentprocess is null
UNION ALL

SELECT proc1.id, proc1.name, p2p1.parentprocess, p2p1.parentprocess || path, sp.depth + 1
FROM processes proc1
RIGHT JOIN p2p p2p1 ON p2p1.childgroup = proc1.id
INNER JOIN subprocesses sp ON sp.id = p2p1.parentprocess
)

-- select all superprocesses of process with ID = 1
SELECT path[array_length(path, 1)] as parent
FROM subprocesses
where id = 1
Comments
test
Added on 22-01-2015 11:12 by anonymous

 

Add comment

Has this tutorial been helpful to you? Or do you see anything wrong? We appreciate your opinion!
Your comment:
Show formatting hints
HTML is disallowed, but in your text you can use the following markup
  • [code][/code] for a block of code
  • [tt][/tt] for inline code
  • [link]link href|link anchor[/link] for links
  • [b][/b] for bold text
Email:
+ Ask a question
If you have a technical question related to programming and computers, ask it here. Other users will help you solve it!
Unanswered questions
Share your knowledge by helping others solve their problems