Postgres with Drizzle · Chapter 20 of 40
The common path
By the end of this chapter you can name a step of a query and build on it, walk a thread of replies to any depth, and ask whether a matching row exists. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Advanced Queries
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.
Why you need this
Rohan's queries keep growing: active members with their post counts, popular posts with their comment counts, a dashboard tile with four totals. Each is a query inside a query inside a query, and nobody can read them a month later. Meera wants a whole comment thread under a comment, replies to replies to any depth, and a list of members who have never posted.
A thread is the hard part. A join goes one level down; for three levels you would write three joins, and the fourth reply would be missing. In JavaScript you would query a level, collect the ids, query the next level, and loop until nothing comes back: one round trip per level. Postgres can follow the replies itself, in one query.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
getActiveUsersWithPosts: active members and their posts
Write getActiveUsersWithPosts(). It returns every active member (deleted_at is NULL; a soft-deleted member is left out) with how many posts they have written, 0 for an active member with none. Pick the active members in a CTE first, then count their posts.
Send raw SQL with getPool().query and return result.rows: objects with id, username and post_count, where post_count is a number, not a string. Any order: the check finds each member by id.
- Task 2
getTopPostsWithComments: popular posts and their comments
Write getTopPostsWithComments(minLikes). minLikes is a number. It returns the posts whose likes_count is at least minLikes (exactly minLikes counts), each with how many comments it has (0 for none). Pick the popular posts in a CTE first, using minLikes as a query parameter.
Return result.rows: objects with id, content, likes_count and comment_count, where comment_count is a number. Any order. With the check's data and minLikes 10, two posts come back, and the one with 50 likes has 3 comments.
- Task 3
getCommentTreeRecursive: a comment and every reply below it
Write getCommentTreeRecursive(rootCommentId). rootCommentId is a number, the id of a comment. It returns that comment, its replies, their replies and so on to any depth, using a recursive CTE. A reply's parent_id holds the id of the comment it answers.
Return result.rows: objects with id, content, parent_id and depth, a number: 1 for the root comment, 2 for its replies, 3 for replies to those. Order by depth, then id. With the check's data: the root, two replies and one reply to a reply, four rows.
- Task 4
existsUsersWithoutPosts: members who never posted
Write existsUsersWithoutPosts(). It returns the members who have not written any post, using NOT EXISTS.
Return result.rows: objects with id and username. Any order. With the check's data only user3 comes back.
- Task 5
getMultipleCTEStats: four totals in one query
Write getMultipleCTEStats(). It returns the platform's totals from one query with several CTEs: the number of members, the number of posts, the total likes_count of all posts, and the number of comments.
Return one object (the first row), not an array, with total_users, total_posts and total_comments (numbers, not strings) and total_likes. With the check's data: 3 members, 3 posts and 6 comments.
What you will learn
- WITH name AS (...): a common table expression (CTE), a named step you then use like a table.
- Several CTEs in one WITH, separated by commas.
- WITH RECURSIVE: a starting row, then a step that runs again on its own results until it finds nothing.
- NOT EXISTS: keep a row when a subquery finds no match.
More in Advanced Queries
Build The common path yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.