alice.mosphere.at
this repo has no description
1DROP FUNCTION IF EXISTS get_main_thread;
2
3CREATE OR REPLACE FUNCTION public.get_main_thread(p_conversation_id TEXT)
4RETURNS TABLE (
5 tweet_id TEXT,
6 conversation_id TEXT,
7 reply_to_tweet_id TEXT,
8 account_id TEXT,
9 depth INT,
10 max_depth INT,
11 favorite_count INT,
12 retweet_count INT
13) AS $$
14BEGIN
15 RETURN QUERY
16 WITH RECURSIVE main_thread AS (
17 -- Base case: Select the initial tweet of the thread by the user
18 SELECT tweets.tweet_id, c.conversation_id, tweets.reply_to_tweet_id,
19 tweets.account_id,
20 0 AS depth, tweets.favorite_count, tweets.retweet_count
21 FROM tweets
22 LEFT JOIN conversations c ON tweets.tweet_id = c.tweet_id
23 WHERE c.conversation_id = p_conversation_id
24 AND tweets.reply_to_tweet_id IS NULL -- This ensures we start with the first tweet in the thread
25
26 UNION ALL
27
28 -- Recursive case: Select direct replies by the same user to their own tweets in the main thread
29 SELECT t.tweet_id, c.conversation_id, t.reply_to_tweet_id, t.account_id,
30 mt.depth + 1, t.favorite_count, t.retweet_count
31 FROM tweets t
32 LEFT JOIN conversations c ON t.tweet_id = c.tweet_id
33 JOIN main_thread mt ON t.reply_to_tweet_id = mt.tweet_id
34 WHERE t.account_id = mt.account_id
35 AND c.conversation_id = p_conversation_id
36 ),
37 thread_summary AS (
38 SELECT main_thread.conversation_id,
39 main_thread.account_id,
40 MAX(main_thread.depth) AS max_depth
41 FROM main_thread
42 GROUP BY main_thread.conversation_id, main_thread.account_id
43 )
44 SELECT mt.tweet_id, mt.conversation_id, mt.reply_to_tweet_id, mt.account_id,
45 mt.depth, ts.max_depth, mt.favorite_count, mt.retweet_count
46 FROM main_thread mt
47 JOIN thread_summary ts ON mt.conversation_id = ts.conversation_id AND mt.account_id = ts.account_id;
48END;
49$$ LANGUAGE plpgsql;
50COMMENT ON FUNCTION get_main_thread(text) IS 'Returns the main thread view for a given conversation_id';