summaryrefslogtreecommitdiff
path: root/schema.sql
diff options
context:
space:
mode:
Diffstat (limited to 'schema.sql')
-rw-r--r--schema.sql22
1 files changed, 22 insertions, 0 deletions
diff --git a/schema.sql b/schema.sql
index 87adc26..5f636fe 100644
--- a/schema.sql
+++ b/schema.sql
@@ -269,4 +269,26 @@ CREATE AGGREGATE most_recent_revision (article_revisions) (
STYPE = article_revisions
);
+
+CREATE VIEW article_comment_counts AS
+ SELECT a.id AS article,
+ count(c.*) AS comment_count
+ FROM articles a
+ LEFT JOIN comments c
+ ON c.article = a.id
+ AND EXISTS (SELECT *
+ FROM comment_revisions
+ WHERE comment = c.id
+ AND status IN ('approved', 'trusted'))
+ GROUP BY a.id;
+
+
+CREATE VIEW article_publishing_dates AS
+ SELECT article AS article,
+ min(date) AS publishing_date
+ FROM article_revisions
+ WHERE status IN ('published', 'syndicated')
+ GROUP BY article;
+
+
COMMIT;