Reduce the number of roundtrips needed for archive page creation.

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;