@cloud_manul @amin @rl_dane @tripplehelix @alatartheblue @kudzu @dmoonfire @thedoctor @krafter @clayton @roguefoam @fbievan @orbitalmartian @joel @dm @sotolf @btp @adamsdesk @jlw_the_jobber @gorkmo note this also works for multiple starting points, for which you can use WHERE id IN (…) or WHERE thread_id IN (…) or even WHERE id IN (…) OR thread_id IN (…) (the duplication is necessary; while one can combine the search in PostgreSQL, the combined search cannot use the indicēs and is dead slow) if you have either a post ID (like https://semaphore.social/statuses/xxx) or a GtS 0.19+ DB thread ID, thanks to use of UNION ipv UNION ALL in the “findtop” query (which just goes from an arbitrary point (or more than one) in the thread to its starting point a.k.a. the OP), and it’ll show you the information for multiple threads separately, for example…
gotosocial=> WITH RECURSIVE
gotosocial-> findtop AS (
gotosocial(> SELECT id, in_reply_to_id FROM statuses
gotosocial(> -- can use thread_id and/or multiple values here:
gotosocial(> WHERE id IN ('01JHC4Q1RXD9XBR3ESVQ2XT6QS', '01JW4BKHEGG216D56XXNC50MJW') OR thread_id IN ('01JHC4Q1RXD9XBR3ESVQ2XT6QS', '01JW4BKHEGG216D56XXNC50MJW')
gotosocial(> UNION
gotosocial(> SELECT s.id, s.in_reply_to_id
gotosocial(> FROM statuses s INNER JOIN findtop f ON s.id = f.in_reply_to_id
gotosocial(> ),
gotosocial-> threads AS (
gotosocial(> SELECT id, in_reply_to_id, 1 AS depth, id AS op FROM findtop
gotosocial(> WHERE in_reply_to_id IS NULL
gotosocial(> UNION ALL
gotosocial(> SELECT s.id, s.in_reply_to_id, t.depth + 1, t.op
gotosocial(> FROM statuses s INNER JOIN threads t ON s.in_reply_to_id = t.id
gotosocial(> ),
gotosocial-> counts AS (
gotosocial(> SELECT op, COUNT(*) AS count FROM threads GROUP BY op ORDER BY op
gotosocial(> ),
gotosocial-> leaves AS (
gotosocial(> SELECT id, depth, op FROM threads WHERE NOT EXISTS (
gotosocial(> SELECT * FROM statuses s WHERE s.in_reply_to_id = threads.id)
gotosocial(> ORDER BY depth DESC, id ASC
gotosocial(> ),
gotosocial-> jleaves AS (
gotosocial(> SELECT op, to_jsonb(array_agg(id || ': ' || depth)) AS leaves FROM leaves GROUP BY op
gotosocial(> ),
gotosocial-> jthreads AS (
gotosocial(> SELECT jsonb_build_object('OP', j.op, 'TOOTS', c.count, 'leaves', jsonb_array_length(j.leaves), 'depth', j.leaves) AS t
gotosocial(> FROM jleaves j INNER JOIN counts c USING (op) ORDER BY j.op
gotosocial(> )
gotosocial-> SELECT jsonb_pretty(to_jsonb(array_agg(t))) FROM jthreads;
jsonb_pretty
[
{
"OP": "01JHBSJABRE18TSGTNPT0RYBWD",
"TOOTS": 258,
"depth": [
"01JHCC64RR1T09R50TS5REB4CV: 43",
"01JHCCJWZ8VPCKKKBKMCQ8ESAB: 43",
"01JHCCXBY0403MN91HH8NZGZJJ: 43",
"01JHCCPVXRHB9CK422DNWV83J4: 42",
"01JHCC9C98126988JZ8SC251EB: 41",
"01JHCCM418V2Q9VBQ8EP0G4SK2: 41",
"01JHCCND1R4NS0EKPNS8BRSMHV: 41",
"01JHCBWSY8H3TYKAZVJ7B99XYB: 39",
"01JHCBXP8G43JDAJYACWKYYMQ5: 39",
"01JHCC40D849JW3EQDY1AMR8VZ: 39",
"01JHCEK8NWPJZ1TMKF4F95WB3Y: 39",
"01JHCCPP282JXHMAV7PQ643EF8: 38",
"01JHCEEP8CDRWQXKYTCFANCWCY: 38",
"01JHG4WBK038180C5Z606CRKB3: 38",
"01JHCBHVBGD4GC3XND3YTFHDX4: 37",
"01JHCBJHT8F8M8VKPGC9G8S7F3: 37",
"01JHCB44W8NEEFC1GQDB2N0RQQ: 36",
"01JHCANJ33DQKK9V0V9NE3283Q: 34",
"01JHCB3N8809R3EJF47R3QBS6T: 34",
"01JHCBDFPRR5RBCNMVJ0VC1YHA: 34",
"01JHEA5PJ53DKFKMJ9KA44A6H0: 34",
"01JHEFA34RF7JS21WKD440JZHH: 34",
"01JHES5XW6DW8EKZC29F286F1W: 34",
"01JHHEQB80BKJ43FGDJ2H4CPGW: 34",
"01JHCA9PQN6ZH715MQZ5J36RN2: 33",
"01JHCAF2TXTW7BQRE8C7YQYAGE: 33",
"01JHCAMB0RJRQF3KRJTKQNDZ02: 33",
"01JHCARZERY23P10GPFGTKH5AQ: 33",
"01JHHRN64RFYQZC8HHVJ3NWVFS: 33",
"01JHCAEEHGW8JKE1PECVVW3YC2: 32",
"01JHCAGNTRKHZY0BBK7SRX17JG: 32",
"01JHCAJ5P09CFVJGW66FNCDX6T: 31",
"01JHCG5EA502J9P5T0VHPTFMCN: 31",
"01JHE8EETGM75RCQDJ0RK6618J: 31",
"01JHC9X4VHSQQA4YT1HMA6NY8T: 30",
"01JHCA4C90T4PS7NP86XG5MGCJ: 29",
"01JHCAEG40AQF4YD89ZYGY4G88: 29",
"01JHCESMNRYD8HJEVAD5HYHM20: 29",
"01JHE5SF5YEVN2QEA23611C96Y: 29",
"01JHC9W7H8FBP3V0CASXE6Z12A: 28",
"01JHC9YMP06CXH0W7XBRE3S51W: 28",
"01JHCA5W485ZM7A9CG9NE9151M: 28",
"01JHC9JQTG5Q9VPAC6TERTBAMQ: 27",
"01JHC9W9FR50R0XRQ19PEDW0AW: 27",
"01JHC9YJQGKZEHGNFT3FVG3176: 27",
"01JHC9JTR8P6HJ15S56N5E1GD5: 26",
"01JHC9D260DWZPQA8YVQGAZJP7: 25",
"01JHC9KS103SNCN1ERF9T1TH0D: 25",
"01JHC9NJMRHXA9G8A2FT3QTJWG: 25",
"01JHC9P10Z7G2SB76KHMQ5Q93J: 24",
"01JHC68M8CWY5YQRZDK1S77GD0: 18",
"01JHC8Z1Y8PA3Q9WYZDAT5ZCND: 18",
"01JHC5WZR9WEEPGYXAJGFDWEJH: 17",
"01JHC5H2QXQ6Y5G35WF7VSBPQ2: 16",
"01JHC524W061BVBS0VP3M7EB7Y: 15",
"01JHC5BHER0VYKR1G2APZES1JA: 15",
"01JHC82A0R1XFBC97FFCYF08BD: 15",
"01JHC8KT963NED0WTCEWA03PB3: 15",
"01JHG8SFG0RW6W9FXGEGETN9HN: 15",
"01JHC7ZRZ0ZFQM4YJS56X1FPER: 13",
"01JHC4PRKRRF7VGX1X62DDQQBV: 12",
"01JHG8DBSRQPJN48PG6SCE9CK9: 12",
"01JHC7W0V8JPE1ZXXN2RDJD1XV: 11",
"01JHC7XPJ08YEMTD6JMTR4K10T: 11",
"01JHC43EEG0YJ3KGYNR1RW6BZ2: 10",
"01JHC2F6JGXZPNM1SQ5W0FX70S: 7",
"01JHC2EYRGDHPXED5W2RHSAPQE: 6",
"01JHG018PGTCD65Z16MBMTSA2G: 5"
],
"leaves": 68
},
{
"OP": "01JW4BKHEGG216D56XXNC50MJW",
"TOOTS": 10,
"depth": [
"01JW4D30080K16JM4XWMJ1BG4W: 3",
"01JW4DD478G4HX9JA6NMPPETK8: 3",
"01JW4BQ4P0GMD9KBX51XDF4YVC: 2",
"01JW4BSTM0FTB630JF9F1SMMHW: 2",
"01JW4C266GDV3DJDD6M79MZ2DQ: 2",
"01JW4C682RZCJBFKDCXHZPT7ZD: 2",
"01JW4C9JH0KW0GTTABWP6VD33B: 2"
],
"leaves": 7
}
]
(this example uses a post ID and a thread ID it turns out)
R.L. Dane 
🍵 
Amin Hollon, B.A.
mirabilos
Cloud Manul