Something went wrong. Try again.
(READ ONLY) Margin is an open annotation layer for the internet. Powered by the AT Protocol.
margin.at
extension web atproto comments
Something went wrong. Try again.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152-- name: GetAnnotationAuthorByURI :oneSELECT author_did FROM annotations WHERE uri = $1;
-- name: GetHighlightAuthorByURI :oneSELECT author_did FROM highlights WHERE uri = $1;
-- name: GetBookmarkAuthorByURI :oneSELECT author_did FROM bookmarks WHERE uri = $1;
-- name: GetTrendingTags :manySELECT tag, COUNT(*) as count FROM ( SELECT value as tag, author_did FROM annotations, json_array_elements_text(tags_json::json) as value WHERE tags_json IS NOT NULL AND tags_json != '' AND tags_json != '[]' AND created_at > NOW() - INTERVAL '14 days' UNION ALL SELECT value as tag, author_did FROM highlights, json_array_elements_text(tags_json::json) as value WHERE tags_json IS NOT NULL AND tags_json != '' AND tags_json != '[]' AND created_at > NOW() - INTERVAL '14 days' UNION ALL SELECT value as tag, author_did FROM bookmarks, json_array_elements_text(tags_json::json) as value WHERE tags_json IS NOT NULL AND tags_json != '' AND tags_json != '[]' AND created_at > NOW() - INTERVAL '14 days') combinedGROUP BY tagHAVING COUNT(DISTINCT author_did) >= 3ORDER BY count DESCLIMIT $1;
-- name: GetUserTags :manySELECT tag, SUM(cnt) as count FROM ( SELECT value as tag, COUNT(*) as cnt FROM annotations, json_array_elements_text(tags_json::json) as value WHERE annotations.author_did = $1 AND tags_json IS NOT NULL AND tags_json != '' AND tags_json != '[]' GROUP BY tag UNION ALL SELECT value as tag, COUNT(*) as cnt FROM highlights, json_array_elements_text(tags_json::json) as value WHERE highlights.author_did = $1 AND tags_json IS NOT NULL AND tags_json != '' AND tags_json != '[]' GROUP BY tag UNION ALL SELECT value as tag, COUNT(*) as cnt FROM bookmarks, json_array_elements_text(tags_json::json) as value WHERE bookmarks.author_did = $1 AND tags_json IS NOT NULL AND tags_json != '' AND tags_json != '[]' GROUP BY tag) combinedGROUP BY tagORDER BY count DESCLIMIT $2;