diff options
author | Vincent Ambo <mail@tazj.in> | 2018-05-25T16·44+0200 |
---|---|---|
committer | Vincent Ambo <github@tazj.in> | 2018-05-27T13·00+0200 |
commit | 871093a973d265026581c53b760f9f021b192f54 (patch) | |
tree | 4e43455a5831eebf8cb031f5c5249db9bb8108ca /migrations/2018-05-25-161939_add_closed_to_index/up.sql | |
parent | 468af132e89a8cb80392b7d6e12f1e53b849c0c1 (diff) |
feat(db): Update views with thread-closed field
Diffstat (limited to 'migrations/2018-05-25-161939_add_closed_to_index/up.sql')
-rw-r--r-- | migrations/2018-05-25-161939_add_closed_to_index/up.sql | 35 |
1 files changed, 35 insertions, 0 deletions
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 000000000000..87580a2f3f9e --- /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; |