feat(db): Add view for ordering thread index by most recent post

This implements the classic thread ordering expected by most forum
users (i.e. the thread with the most recent post is at the top).

2 files changed, 16 insertions, 0 deletions

diff --git a/migrations/2018-04-14-145711_create_index_view/down.sql b/migrations/2018-04-14-145711_create_index_view/down.sql
new file mode 100644
index 000000000000..87d26820f7e2
--- /dev/null
+++ b/migrations/2018-04-14-145711_create_index_view/down.sql
@@ -0,0 +1 @@
+DROP VIEW thread_index;
diff --git a/migrations/2018-04-14-145711_create_index_view/up.sql b/migrations/2018-04-14-145711_create_index_view/up.sql
new file mode 100644
index 000000000000..3b7ddc1d647a
--- /dev/null
+++ b/migrations/2018-04-14-145711_create_index_view/up.sql
@@ -0,0 +1,15 @@
+-- Create a simple view that returns the list of threads ordered by
+-- the last post that occured in the thread.
+
+CREATE VIEW thread_index AS
+  SELECT t.id AS thread_id,
+         t.title AS title,
+         t.author_name AS author_name,
+         t.posted AS posted,
+         p.id AS post_id
+    FROM threads t
+    JOIN (SELECT DISTINCT ON (thread_id) id, thread_id
+          FROM posts
+          ORDER BY thread_id, id DESC) AS p
+    ON t.id = p.thread_id
+    ORDER BY p.id DESC;