From efce515bf8d5e44b23cb047165912ce817500533 Mon Sep 17 00:00:00 2001
From: Matthias Andreas Benkard <code@mail.matthias.benkard.de>
Date: Sun, 13 Mar 2011 12:57:36 +0100
Subject: Reduce the number of roundtrips needed for archive page creation.

---
 schema.sql | 22 ++++++++++++++++++++++
 1 file changed, 22 insertions(+)

(limited to 'schema.sql')

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;
-- 
cgit v1.2.3

