a tool for shared writing and social publishing
Something went wrong. Try again.
1.4 kB · 49 lines
SQL
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950CREATE OR REPLACE FUNCTION get_reader_feed( p_identity text, p_cursor_timestamp timestamptz DEFAULT NULL, p_cursor_uri text DEFAULT NULL, p_limit int DEFAULT 25)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 JOIN documents_in_publications dip ON dip.document = d.uri JOIN publication_subscriptions ps ON ps.publication = dip.publication LEFT JOIN LATERAL ( SELECT p.uri, p.record, p.name FROM documents_in_publications dip2 JOIN publications p ON p.uri = dip2.publication WHERE dip2.document = d.uri LIMIT 1 ) pub ON true WHERE ps.identity = p_identity AND ( p_cursor_timestamp IS NULL OR d.sort_date < p_cursor_timestamp OR (d.sort_date = p_cursor_timestamp AND d.uri < p_cursor_uri) ) GROUP BY d.uri, d.data, d.sort_date, pub.uri, pub.record, pub.name ORDER BY d.sort_date DESC, d.uri DESC LIMIT p_limit;$$;