Postgres with Drizzle · Chapter 8 of 40
The query masters
By the end of this chapter you can ask Drizzle for exactly the members you need: two conditions at once, everything except one value, any value from a list, a date range, and fields that were never filled in. 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 is building the moderators' page. It has five filter buttons: online and verified, not offline, joined between two dates, no bio yet, and a status picker where a moderator ticks several statuses at once.
In chapter 4 you filtered with eq and or: one condition, or either of two. With only those, you would fetch every member and filter the array in JavaScript. With half a million members that sends every row over the network to throw most of them away. Postgres can do the filtering itself, if you can say what you want.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
findOnlineVerifiedUsers: online and verified
Write findOnlineVerifiedUsers(). It returns the members whose status is online and who are verified (is_verified is true): both must hold.
Return an array of whole user rows, as a plain select gives them. Any order.
- Task 2
findUsersNotOffline: everyone who is not offline
Write findUsersNotOffline(). It returns every member whose status is anything except offline (online, away or busy).
Return an array of whole user rows. Any order.
- Task 3
findUsersCreatedBetween: joined between two dates
Write findUsersCreatedBetween(startDate, endDate). Both are JavaScript Date objects. It returns the members whose created_at is from startDate to endDate, both ends included: a member who joined at exactly either moment counts.
Return an array of whole user rows. Any order.
- Task 4
findUsersWithNullBio: members with no bio
Write findUsersWithNullBio(). It returns the members who never wrote a bio: their bio is NULL.
Return an array of whole user rows. Any order.
- Task 5
findUsersByMultipleStatuses: any of the picked statuses
Write findUsersByMultipleStatuses(statuses). statuses is an array of status names, like away and busy. It returns the members whose status is any one of them.
Return an array of whole user rows. Any order.
What you will learn
- and: every condition must hold.
- ne and inArray: everything except one value; any value from a list.
- between: a range, both ends included.
- isNull: finding empty fields, and why eq with null finds nobody.
More in Core CRUD & Querying
Build The query masters 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.