about summary refs log tree commit diff
path: root/migrations/2018-04-14-153202_add_stickies_improve_index/up.sql
diff options
context:
space:
mode:
authorVincent Ambo <tazjin@gmail.com>2018-04-14T15·47+0200
committerVincent Ambo <tazjin@gmail.com>2018-04-14T15·47+0200
commitd1c45159b9bb23553768b06ce7ad97ec38e8b845 (patch)
treef6dae457fddcb75bb6a3c65eec699868beb3b89f /migrations/2018-04-14-153202_add_stickies_improve_index/up.sql
parentc136d34e798f399c515b31d90fd1194b02005c96 (diff)
feat(db): Add support for stickies in database
Adds a 'sticky' column to threads and rewrites the thread index to
take sticky markings into account when ordering threads.

Stickies are not yet highlighted in any way in the forum overview.
Diffstat (limited to 'migrations/2018-04-14-153202_add_stickies_improve_index/up.sql')
-rw-r--r--migrations/2018-04-14-153202_add_stickies_improve_index/up.sql21
1 files changed, 21 insertions, 0 deletions
diff --git a/migrations/2018-04-14-153202_add_stickies_improve_index/up.sql b/migrations/2018-04-14-153202_add_stickies_improve_index/up.sql
new file mode 100644
index 000000000000..74a559e35d00
--- /dev/null
+++ b/migrations/2018-04-14-153202_add_stickies_improve_index/up.sql
@@ -0,0 +1,21 @@
+-- Add support for stickies in threads
+ALTER TABLE threads ADD COLUMN sticky BOOLEAN NOT NULL DEFAULT FALSE;
+
+-- 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 thread_author,
+         t.posted AS created,
+         t.sticky AS sticky,
+         p.id AS post_id,
+         p.author_name AS post_author,
+         p.posted AS posted
+    FROM threads t
+    JOIN (SELECT DISTINCT ON (thread_id)
+           id, thread_id, author_name, posted
+          FROM posts
+          ORDER BY thread_id, id DESC) AS p
+    ON t.id = p.thread_id
+    ORDER BY t.sticky DESC, p.id DESC;