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).

6 files changed, 45 insertions, 7 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;
diff --git a/src/db.rs b/src/db.rs
index c26597293706..5a66fbb0fc74 100644
--- a/src/db.rs
+++ b/src/db.rs
@@ -36,19 +36,18 @@ impl Actor for DbExecutor {
 pub struct ListThreads;
 
 impl Message for ListThreads {
-    type Result = Result<Vec<Thread>>;
+    type Result = Result<Vec<ThreadIndex>>;
 }
 
 impl Handler<ListThreads> for DbExecutor {
     type Result = <ListThreads as Message>::Result;
 
     fn handle(&mut self, _: ListThreads, _: &mut Self::Context) -> Self::Result {
-        use schema::threads::dsl::*;
+        use schema::thread_index::dsl::*;
 
         let conn = self.0.get()?;
-        let results = threads
-            .order(posted.desc())
-            .load::<Thread>(&conn)?;
+        let results = thread_index
+            .load::<ThreadIndex>(&conn)?;
         Ok(results)
     }
 }
diff --git a/src/models.rs b/src/models.rs
index 29d196b9a998..159a85469aeb 100644
--- a/src/models.rs
+++ b/src/models.rs
@@ -26,6 +26,18 @@ pub struct Thread {
     pub author_email: String,
 }
 
+/// This struct is used as the query type for the thread index view,
+/// which lists the index of threads ordered by the last post in each
+/// thread.
+#[derive(Queryable, Serialize)]
+pub struct ThreadIndex {
+    pub thread_id: i32,
+    pub title: String,
+    pub author_name: String,
+    pub posted: DateTime<Utc>,
+    pub post_id: i32,
+}
+
 #[derive(Identifiable, Queryable, Serialize, Associations)]
 #[belongs_to(Thread)]
 pub struct Post {
diff --git a/src/render.rs b/src/render.rs
index ca1fd8ac3bc9..66b3fe19c867 100644
--- a/src/render.rs
+++ b/src/render.rs
@@ -47,7 +47,7 @@ impl From<DateTime<Utc>> for FormattedDate {
 
 /// Message used to render the index page.
 pub struct IndexPage {
-    pub threads: Vec<Thread>,
+    pub threads: Vec<ThreadIndex>,
 }
 
 impl Message for IndexPage {
@@ -69,7 +69,7 @@ impl Handler<IndexPage> for Renderer {
         let threads: Vec<IndexThread> = msg.threads
             .into_iter()
             .map(|thread| IndexThread {
-                id: thread.id,
+                id: thread.thread_id,
                 title: escape_html(&thread.title),
                 posted: thread.posted.into(),
                 author_name: thread.author_name,
diff --git a/src/schema.rs b/src/schema.rs
index d14c89c4c7f8..02392c8fc60c 100644
--- a/src/schema.rs
+++ b/src/schema.rs
@@ -19,6 +19,17 @@ table! {
     }
 }
 
+// Note: Manually inserted as print-schema does not add views.
+table! {
+    thread_index (thread_id){
+        thread_id -> Integer,
+        title -> Text,
+        author_name -> Text,
+        posted -> Timestamptz,
+        post_id -> Integer,
+    }
+}
+
 joinable!(posts -> threads (thread_id));
 
 allow_tables_to_appear_in_same_query!(