Recursive query for member network

gistfile1.sql

-- Get a network of members for a given member with a seperation distance.
-- In this example I've named the degree of seperation depth.

-- pid: petition_id
-- mid: member_id
-- in the real world there there woudl be a unique constraint on (pid,mid)
CREATE TABLE signatures (pid integer, mid integer);


-- populate some signatures
INSERT INTO signatures (pid, mid) VALUES (1, generate_series(1,10));
INSERT INTO signatures (pid, mid) VALUES (2, generate_series(10,20));
INSERT INTO signatures (pid, mid) VALUES (3, generate_series(20,25));
INSERT INTO signatures (pid, mid) VALUES (4, generate_series(25,30));
INSERT INTO signatures (pid, mid) VALUES (5,31),(6,1),(6,50),(7,50),(7,60),(8,50),(8,75);

-- this is what it looks like:
SELECT * FROM signatures;

┌─────┬─────┐
│ pid │ mid │
├─────┼─────┤
│   1 │   1 │
│   1 │   2 │
│   1 │   3 │
│   1 │   4 │
│   1 │   5 │
│   1 │   6 │
│   1 │   7 │
│   1 │   8 │
│   1 │   9 │
│   1 │  10 │
│   2 │  10 │
│   2 │  11 │
│   2 │  12 │
│   2 │  13 │
│   2 │  14 │
│   2 │  15 │
│   2 │  16 │
│   2 │  17 │
│   2 │  18 │
│   2 │  19 │
│   2 │  20 │
│   3 │  20 │
│   3 │  21 │
│   3 │  22 │
│   3 │  23 │
│   3 │  24 │
│   3 │  25 │
│   4 │  25 │
│   4 │  26 │
│   4 │  27 │
│   4 │  28 │
│   4 │  29 │
│   4 │  30 │
│   5 │  31 │
│   6 │   1 │
│   6 │  50 │
│   7 │  50 │
│   7 │  60 │
│   8 │  50 │
│   8 │  75 │
└─────┴─────┘
(40 rows)


-- get the network for a member
WITH RECURSIVE q as (
  SELECT s.mid, s.pid, 1 as depth
  FROM signatures s0
  JOIN signatures s ON (s.pid = s0.pid)
  WHERE s0.mid = 1 AND s.mid <> 1
UNION ALL
  SELECT ss.mid, ss.pid, depth + 1
  FROM q
  JOIN signatures s ON q.mid = s.mid
  JOIN signatures ss ON ss.pid = s.pid
  WHERE ss.mid <> q.mid and ss.pid <> q.pid
) 
SELECT mid as member_id, depth FROM q;

┌───────────┬───────┐
│ member_id │ depth │
├───────────┼───────┤
│         2 │     1 │
│         3 │     1 │
│         4 │     1 │
│         5 │     1 │
│         6 │     1 │
│         7 │     1 │
│         8 │     1 │
│         9 │     1 │
│        10 │     1 │
│        50 │     1 │
│        20 │     2 │
│        19 │     2 │
│        18 │     2 │
│        17 │     2 │
│        16 │     2 │
│        15 │     2 │
│        14 │     2 │
│        13 │     2 │
│        12 │     2 │
│        11 │     2 │
│        75 │     2 │
│        60 │     2 │
│        25 │     3 │
│        24 │     3 │
│        23 │     3 │
│        22 │     3 │
│        21 │     3 │
│        30 │     4 │
│        29 │     4 │
│        28 │     4 │
│        27 │     4 │
│        26 │     4 │
└───────────┴───────┘
(32 rows)