leaflet.pub
a tool for shared writing and social publishing
awarm.space
1CREATE OR REPLACE FUNCTION get_profile_posts(
2 p_did text,
3 p_cursor_sort_date timestamptz DEFAULT NULL,
4 p_cursor_uri text DEFAULT NULL,
5 p_limit int DEFAULT 20
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 LEFT JOIN LATERAL (
32 SELECT p.uri, p.record, p.name
33 FROM documents_in_publications dip
34 JOIN publications p ON p.uri = dip.publication
35 WHERE dip.document = d.uri
36 LIMIT 1
37 ) pub ON true
38 WHERE d.uri LIKE 'at://' || p_did || '/%'
39 AND (
40 p_cursor_sort_date IS NULL
41 OR d.sort_date < p_cursor_sort_date
42 OR (d.sort_date = p_cursor_sort_date AND d.uri < p_cursor_uri)
43 )
44 ORDER BY d.sort_date DESC, d.uri DESC
45 LIMIT p_limit;
46$$;