Support ?q=category queries

Warning, untested. But hopefully works!

1 files changed, 15 insertions, 0 deletions

diff --git a/src/database/postgres/mod.rs b/src/database/postgres/mod.rs
index ee7dd1c..71c4d58 100644
--- a/src/database/postgres/mod.rs
+++ b/src/database/postgres/mod.rs
@@ -75,6 +75,21 @@ impl PostgresStorage {
 #[async_trait::async_trait]
 impl Storage for PostgresStorage {
     #[tracing::instrument(skip(self))]
+    async fn categories(&self, url: &str) -> Result<Vec<String>> {
+        sqlx::query_scalar::<_, String>("
+SELECT jsonb_array_elements(mf2['properties']['category']) AS category
+FROM kittybox.mf2_json
+WHERE
+    jsonb_typeof(mf2['properties']['category']) = 'array'
+    AND uid LIKE ($1 + '%')
+    GROUP BY category ORDER BY count(*) DESC
+")
+            .bind(url)
+            .fetch_all(&self.db)
+            .await
+            .map_err(|err| err.into())
+    }
+    #[tracing::instrument(skip(self))]
     async fn post_exists(&self, url: &str) -> Result<bool> {
         sqlx::query_as::<_, (bool,)>("SELECT exists(SELECT 1 FROM kittybox.mf2_json WHERE uid = $1 OR mf2['properties']['url'] ? $1)")
             .bind(url)