a tool for shared writing and social publishing
Something went wrong. Try again.
1.2 kB · 46 lines
SQL
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647CREATE OR REPLACE FUNCTION get_profile_posts( p_did text, p_cursor_sort_date timestamptz DEFAULT NULL, p_cursor_uri text DEFAULT NULL, p_limit int DEFAULT 20)RETURNS TABLE ( uri text, data jsonb, sort_date timestamptz, comments_count bigint, mentions_count bigint, recommends_count bigint, publication_uri text, publication_record jsonb, publication_name text)LANGUAGE sql STABLEAS $$ SELECT d.uri, d.data, d.sort_date, (SELECT count(*) FROM comments_on_documents c WHERE c.document = d.uri), (SELECT count(*) FROM document_mentions_in_bsky m WHERE m.document = d.uri), (SELECT count(*) FROM recommends_on_documents r WHERE r.document = d.uri), pub.uri, pub.record, pub.name FROM documents d LEFT JOIN LATERAL ( SELECT p.uri, p.record, p.name FROM documents_in_publications dip JOIN publications p ON p.uri = dip.publication WHERE dip.document = d.uri LIMIT 1 ) pub ON true WHERE d.uri LIKE 'at://' || p_did || '/%' AND ( p_cursor_sort_date IS NULL OR d.sort_date < p_cursor_sort_date OR (d.sort_date = p_cursor_sort_date AND d.uri < p_cursor_uri) ) ORDER BY d.sort_date DESC, d.uri DESC LIMIT p_limit;$$;