I am working on a forum like website where I have to query list of categories along with its latest post in the given category.
These are the table schemas
create table forum_posts (
forum_post_id text not null unique,
forum_post_name text not null,
user_id text not null references users(user_id),
forum_category_id text not null references forum_categories(forum_category_id),
forum_post_content text not null,
forum_post_is_active boolean not null default true,
forum_post_created_timestamp timestamptz not null default now(),
-- gets updated when user
--
-- have edited the post
-- new reply
-- user have un-deactivate (goes from deactivated to activated) the post.
forum_post_last_active_timestamp timestamptz not null default now(),
-- when forum_post_is_active gets switch to false, deactivated timestamp gets updated
forum_post_deactivated_timestamp timestamptz,
forum_post_is_category_based_announcement boolean not null default false,
forum_post_is_global_announcement boolean not null default false,
primary key (forum_post_id)
);
create table forum_categories (
forum_category_id text not null unique,
forum_category_name text not null unique,
forum_category_representative_id text not null unique,
user_id text not null,
forum_category_color_theme text not null default '#000000',
forum_category_created_timestamp timestamptz not null default now(),
primary key (forum_category_id),
foreign key (user_id) references users(user_id)
);
What I wanted to query is to query a list of categories with the latest post in forum_posts
table. I have tried to create the query below but I am not sure how to write the where
clause
select
forum_categories.forum_category_id,
count(forum_posts.forum_post_id) as post_count,
forum_posts.forum_post_id as latest_post_id,
forum_posts.forum_post_created_timestamp as latest_post_timestamp
from forum_categories
inner join forum_posts on forum_categories.forum_category_id = forum_posts.forum_category_id
where -- how do I write the where clause here.
Thank you for any help.
2
Answers
I have solved some part of the question by using
with
statementthank you to a guy on reddit.
The following will return the most recent post for each category with a post:
Adding
forum_post_id
to the sort criteria makes the query deterministic if multiple post in a category could have the same timestamp. If each post in a category is guaranteed to have a different timestamp, thenforum_post_id
is redundant. If post IDs within a category increase monotonically with time, then sorting byforum_post_created_timestamp
is unnecessary.