Something went wrong. Try again.
(READ ONLY) Margin is an open annotation layer for the internet. Powered by the AT Protocol.
margin.at
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;