Using multiple recursive CTE's to go backwards and forwards through a list (PostgreSQL)

Question

PostgreSQL 14

Assume I have the following table:

CREATE TABLE links
(
    linkid serial primary key,
    patientid integer NOT NULL,
    linkto integer NOT NULL
)

INSERT INTO links (patientid, linkto)
  VALUES  (1,2), (1,3), (1,4), (1,5), (1,6);

INSERT INTO links (patientid, linkto)
  VALUES  (2,7), (2,8), (2,9), (2,10), (2,11);
 
INSERT INTO links (patientid, linkto)
  VALUES  (3,12), (3,13), (3,14), (3,15), (3,16);
  
INSERT INTO links (patientid, linkto)
  VALUES  (4,17), (4,18), (4,19), (4,20), (4,21);

Each patientid has multiple links. If I start with patientId of 1, the following will correctly enumerate all the id's 1 is directly related to AND all the id's that are linked to 1.

WITH RECURSIVE _IN (patientid) AS (
        VALUES (1)
), 
linked (linkid, patientid, linkto) AS (
    SELECT k.linkid, k.patientid, k.linkto 
       FROM _IN n 
       JOIN links k ON k.patientid = n.patientid
    UNION ALL
    SELECT LL.linkid, LL.patientid, LL.linkto  FROM linked L
    INNER JOIN links LL ON L.linkto = LL.patientid  
) SELECT * FROM linked

However, it will be quite possible that an id other than 1 will be given, e.g. "2".

In the case of "2", I want to enumerate all the id's 2 is related to (in a forward direction) as above, but ALSO enumerate all the id's linked to 2 in a backward direction. That is, as above, as 1 is a patient id, it is linked to 2. So if given 2, I need it to read backward to hit #1.

Additionally, each backlink will have its own set of links that need to be enumerated.

The following fails--and never completes. How can this be done?

WITH RECURSIVE _IN (patientid) AS (
        VALUES (2)
), 
linked (linkid, patientid, linkto) AS ( 
    SELECT k.linkid, k.patientid, k.linkto 
       FROM _IN n 
       JOIN links k ON k.patientid = n.patientid
    UNION ALL
    SELECT LL.linkid, LL.patientid, LL.linkto  FROM linked L
    INNER JOIN links LL ON L.linkto = LL.patientid  
), 
backlink (linkid, patientid, linkto) AS ( 
SELECT k.linkid, k.patientid, k.linkto 
   FROM linked lll
   JOIN links k ON k.linkto = lll.linkto
UNION ALL
SELECT LL.linkid, LL.linkto, LL.patientid FROM backlink L
INNER JOIN links LL ON L.linkto = L.linkto  


)
SELECT * FROM backlink

Any help with this would be most appreciated.

TIA

You'll want to start your recursive query for `backlink`s with `_IN`, not with `linked` I guess? Then `SELECT * FROM linked UNION SELECT * FROM backlinked` — Bergi, Jul 27 '22 at 18:48

score 0 · Answer 1 · answered Jul 28 '22 at 02:34

The following works as expected:

WITH RECURSIVE _IN (patientid) AS (
        VALUES (7)
), 
backlink (linkid, patientid, linkto) AS ( 
    SELECT k.linkid, k.patientid, k.linkto 
       FROM _IN n
       JOIN links k ON k.linkto = n.patientid
  UNION 
        SELECT L.linkid, L.patientid, L.linkto 
        FROM backlink b
       INNER JOIN links L ON L.linkto = b.patientid 
), 
_t4 (linkid, patientid, linkto) AS ( 
    SELECT null, n.patientid, null FROM _IN n
    UNION
    SELECT b.linkid, b.patientid, b.linkto FROM backlink b
),
_t3 (linkid, patientid, linkto) AS ( 
    SELECT k.linkid, k.patientid, k.linkto 
    FROM _t4 b 
    JOIN links k ON k.patientid = b.patientid
        UNION 
    SELECT LL.linkid, LL.patientid, LL.linkto  FROM _t3 tt
    INNER JOIN links LL ON LL.patientid = tt.linkto 
) 
SELECT * FROM _t3

Is there a better way?

Using multiple recursive CTE's to go backwards and forwards through a list (PostgreSQL)

1 Answers1