Postgres with Drizzle · Chapter 9 of 40
Select, sort and slice
By the end of this chapter you can sort members either way, serve them one numbered page at a time, count them in groups or all at once, and pick the top few. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Core CRUD & Querying
- 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 member directory has a "newest first / oldest first" switch and page numbers at the bottom. Rohan's dashboard shows how many members have each status, the total, and a leaderboard of the most active posters.
In chapter 4 you sorted and cut out one page when you were told how many rows to skip. The directory only knows the page number. And without grouping, Rohan's tiles would mean fetching every member and counting in JavaScript: half a million rows over the network to produce four numbers. Postgres can sort, slice and count before anything leaves the database.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
getUsersSortedByCreatedAt: oldest or newest first
Write getUsersSortedByCreatedAt(direction). direction is the string 'asc' or 'desc'. It returns every member, sorted by created_at: oldest first for 'asc', newest first for 'desc'.
Return an array of whole user rows, in that order. With the check's seven members, 'asc' starts with alice and ends with grace, and 'desc' the other way round.
- Task 2
getUsersPageinated: one page of members
Write getUsersPageinated(page, pageSize). Both are numbers; page counts from 1. It returns that page of members, pageSize per page, sorted by id from lowest to highest: page 1 is the first pageSize members, page 2 the next pageSize, and the last page may be shorter.
Return an array of whole user rows. With the check's seven members and pages of 3, page 1 and page 2 hold three each and do not overlap, and page 3 holds one.
- Task 3
countUsersByStatus: members per status
Write countUsersByStatus(). It counts the members of each status: one answer row per status that at least one member has.
Return an array of objects with exactly the keys status and count, for example the online row with count 3 and the offline row with count 2 in the check's data. Any order.
- Task 4
getTotalUserCount: how many members
Write getTotalUserCount(). It counts every member in the users table.
Return the count itself as a JavaScript number (the check expects the number 7), not the array of rows and not a string.
- Task 5
getTopUsersByPostCount: the top posters
Write getTopUsersByPostCount(limit). limit is a number. It returns the limit members with the highest post_count, highest first.
Return an array of whole user rows, in that order. With the check's seven members and a limit of 3: charlie, then eve, then alice.
What you will learn
- asc and desc: the sort direction, chosen while the code runs.
- Page numbers: turning page 3 into the right offset.
- groupBy with count(): one count per group, as a real number.
- Top N: sort from highest, then limit, and what happens with ties.
More in Core CRUD & Querying
Build "Select, sort and slice" 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.