From efce515bf8d5e44b23cb047165912ce817500533 Mon Sep 17 00:00:00 2001 From: Matthias Andreas Benkard 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