git.squeep.com Git - websub-hub/blob - src/db/postgres/sql/topic-get-info-all.sql

   1 -- meta data with subscriber count
   2 SELECT
   3         t.id,
   4         created,
   5         url,
   6         extract(epoch FROM lease_seconds_preferred)::integer AS lease_seconds_preferred,
   7         extract(epoch FROM lease_seconds_min)::integer AS lease_seconds_min,
   8         extract(epoch FROM lease_seconds_max)::integer AS lease_seconds_max,
   9         t.publisher_validation_url,
  10         t.content_hash_algorithm,
  11         t.is_active,
  12         t.is_deleted,
  13         last_publish,
  14         content_fetch_next_attempt,
  15         t.content_fetch_attempts_since_success,
  16         content_updated,
  17         t.content_hash,
  18         t.content_type,
  19         COUNT (s.id) AS subscribers
  20 FROM topic t
  21 LEFT JOIN (SELECT id, topic_id FROM subscription WHERE expires > now()) s ON t.id = s.topic_id
  22 GROUP BY t.id
  23 ORDER BY subscribers DESC, t.created DESC