Initial release

[websub-hub] / src / db / postgres / sql / topic-get-info-all.sql

diff --git a/src/db/postgres/sql/topic-get-info-all.sql b/src/db/postgres/sql/topic-get-info-all.sql
new file mode 100644 (file)
index 0000000..e428903
--- /dev/null
+++ b/src/db/postgres/sql/topic-get-info-all.sql
@@ -0,0 +1,23 @@
+-- meta data with subscriber count
+SELECT
+       t.id,
+       created,
+       url,
+       extract(epoch FROM lease_seconds_preferred) AS lease_seconds_preferred,
+       extract(epoch FROM lease_seconds_min) AS lease_seconds_min,
+       extract(epoch FROM lease_seconds_max) AS lease_seconds_max,
+       t.publisher_validation_url,
+       t.content_hash_algorithm,
+       t.is_active,
+       t.is_deleted,
+       last_publish,
+       content_fetch_next_attempt,
+       t.content_fetch_attempts_since_success,
+       content_updated,
+       t.content_hash,
+       t.content_type,
+       COUNT (s.id) AS subscribers
+FROM topic t
+LEFT JOIN (SELECT id, topic_id FROM subscription WHERE expires > now()) s ON t.id = s.topic_id
+GROUP BY t.id
+ORDER BY subscribers DESC, t.created DESC