leaflet.pub
a tool for shared writing and social publishing
awarm.space
1CREATE OR REPLACE FUNCTION get_reader_feed(
2 p_identity text,
3 p_cursor_timestamp timestamptz DEFAULT NULL,
4 p_cursor_uri text DEFAULT NULL,
5 p_limit int DEFAULT 25
6)
7RETURNS TABLE (
8 uri text,
9 data jsonb,
10 sort_date timestamptz,
11 comments_count bigint,
12 mentions_count bigint,
13 recommends_count bigint,
14 publication_uri text,
15 publication_record jsonb,
16 publication_name text
17)
18LANGUAGE sql STABLE
19AS $$
20 SELECT
21 d.uri,
22 d.data,
23 d.sort_date,
24 (SELECT count(*) FROM comments_on_documents c WHERE c.document = d.uri),
25 (SELECT count(*) FROM document_mentions_in_bsky m WHERE m.document = d.uri),
26 (SELECT count(*) FROM recommends_on_documents r WHERE r.document = d.uri),
27 pub.uri,
28 pub.record,
29 pub.name
30 FROM documents d
31 JOIN documents_in_publications dip ON dip.document = d.uri
32 JOIN publication_subscriptions ps ON ps.publication = dip.publication
33 LEFT JOIN LATERAL (
34 SELECT p.uri, p.record, p.name
35 FROM documents_in_publications dip2
36 JOIN publications p ON p.uri = dip2.publication
37 WHERE dip2.document = d.uri
38 LIMIT 1
39 ) pub ON true
40 WHERE ps.identity = p_identity
41 AND (
42 p_cursor_timestamp IS NULL
43 OR d.sort_date < p_cursor_timestamp
44 OR (d.sort_date = p_cursor_timestamp AND d.uri < p_cursor_uri)
45 )
46 GROUP BY d.uri, d.data, d.sort_date, pub.uri, pub.record, pub.name
47 ORDER BY d.sort_date DESC, d.uri DESC
48 LIMIT p_limit;
49$$;