From 871093a973d265026581c53b760f9f021b192f54 Mon Sep 17 00:00:00 2001 From: Vincent Ambo Date: Fri, 25 May 2018 18:44:05 +0200 Subject: feat(db): Update views with thread-closed field --- .../2018-05-25-161939_add_closed_to_index/up.sql | 35 ++++++++++++++++++++++ 1 file changed, 35 insertions(+) create mode 100644 migrations/2018-05-25-161939_add_closed_to_index/up.sql (limited to 'migrations/2018-05-25-161939_add_closed_to_index/up.sql') diff --git a/migrations/2018-05-25-161939_add_closed_to_index/up.sql b/migrations/2018-05-25-161939_add_closed_to_index/up.sql new file mode 100644 index 0000000000..87580a2f3f --- /dev/null +++ b/migrations/2018-05-25-161939_add_closed_to_index/up.sql @@ -0,0 +1,35 @@ +-- Update the index view: +DROP VIEW thread_index; +CREATE VIEW thread_index AS + SELECT t.id AS thread_id, + t.title AS title, + ta.name AS thread_author, + t.posted AS created, + t.sticky AS sticky, + t.closed AS closed, + p.id AS post_id, + pa.name AS post_author, + p.posted AS posted + FROM threads t + JOIN (SELECT DISTINCT ON (thread_id) + id, thread_id, user_id, posted + FROM posts + ORDER BY thread_id, id DESC) AS p + ON t.id = p.thread_id + JOIN users ta ON ta.id = t.user_id + JOIN users pa ON pa.id = p.user_id + ORDER BY t.sticky DESC, p.id DESC; + +-- Update post view: +DROP VIEW simple_posts; +CREATE VIEW simple_posts AS + SELECT p.id AS id, + thread_id, body, + p.posted AS posted, + p.user_id AS user_id, + threads.closed AS closed, + users.name AS author_name, + users.email AS author_email + FROM posts p + JOIN users ON users.id = p.user_id + JOIN threads ON threads.id = p.thread_id; -- cgit 1.4.1