Skip to content

Instantly share code, notes, and snippets.

@leklund
Last active August 29, 2015 14:21
Show Gist options
  • Save leklund/f7171eede07a1ed41a59 to your computer and use it in GitHub Desktop.
Save leklund/f7171eede07a1ed41a59 to your computer and use it in GitHub Desktop.
Recursive query for member network
-- 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment