Postgres with Drizzle · Chapter 17 of 40
The computed fields
By the end of this chapter you can ask Postgres to compute values while it reads: joined text, counts from another table, ages in days, scores from a formula, and labels chosen by rules. 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
Meera's profile cards show a label made of the display name and the username. Her post previews show how many comments each post has, and Rohan's dashboard wants each member's age in days, an engagement score for every post and an activity level for every member.
None of these is a column. Stored copies would need updating on every new comment, like or post, and the day someone forgets, they are wrong. Computing in JavaScript means, for the comment counts, one more query per post. Postgres can compute each value from the columns you have, in the query that reads the row.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
getUsersWithFullInfo: a label from two columns
Write getUsersWithFullInfo(). It returns every member with a computed fullLabel: the display name, a space, an opening bracket, an at sign, the username and a closing bracket. A member with display name Meera K and username meera gets "Meera K (@meera)".
Return an array of objects with id, username and fullLabel, one per member. Any order: the check finds user1 by username.
- Task 2
getPostsWithCommentCount: comments per post
Write getPostsWithCommentCount(). It returns every post with commentCount, the number of comments whose post_id is that post's id, computed by a subquery inside the select.
Return an array of objects with id, content and commentCount, one per post. commentCount may come back as a string such as '4': the check reads it with Number. Any order: the check finds each post by id.
- Task 3
getUsersWithAccountAge: days since joining
Write getUsersWithAccountAge(). It returns every member with accountAgeDays: the whole days in the interval from created_at to the current moment. A member who joined today gets 0.
Return an array of objects with id, username and accountAgeDays, a whole number. Any order.
- Task 4
getPostsWithEngagementScore: a score from likes and comments
Write getPostsWithEngagementScore(). It returns every post with engagementScore: its likes_count times 2, plus the number of comments on that post.
Return an array of objects with id, content and engagementScore. engagementScore may come back as a string: the check reads it with Number. With the check's data, a post with 10 likes and 3 comments scores 23.
- Task 5
getUserStatusSummary: an activity level per member
Write getUserStatusSummary(). It returns every member with activityLevel, from the stored post_count column: 'power_user' when post_count is more than 10, 'active' when it is more than 0 (1 to 10), and 'lurker' otherwise.
Return an array of objects with username and activityLevel. Any order: the check finds each member by username. With the check's data, post_count 15 gives power_user, 5 gives active and 0 gives lurker.
What you will learn
- The sql template: write an SQL expression inside select, with schema columns placed in it by ${...}.
- Text and arithmetic: || joins text, and * and + work on numbers, as in any SQL.
- A subquery per row: a SELECT COUNT(*) that looks at the outer row, and why it must name the outer table itself.
- Dates and CASE: NOW() minus a timestamp, the days in it, and rules checked in order.
More in Advanced Queries
Build The computed fields 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.