Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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 │
├─────┼─────┤
11
12
13
14
15
16
17
18
19
110
210
211
212
213
214
215
216
217
218
219
220
320
321
322
323
324
325
425
426
427
428
429
430
531
61
650
750
760
850
875
└─────┴─────┘
(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 │
├───────────┼───────┤
21
31
41
51
61
71
81
91
101
501
202
192
182
172
162
152
142
132
122
112
752
602
253
243
233
223
213
304
294
284
274
264
└───────────┴───────┘
(32 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.