Knex.Js. Subqueries in Examples
What if we need to fetch users that have age more that average age of all users? Or we need to get some data and after that find another data that is depended from previous result? Let’s find out how to handle such scenarios only with one query using knex.js
For visual explanation of “what will happen if…” cases I will be using express.js for server-side and PostgreSQL database from previous article: Knex.Js. Beginner’s Guide in Examples
Old-fashion way
What if we need to fetch users that have age more that average age of all users?
First, let’s see what we’ve got in our tests database (users table):
As you probably know the simplest way is to implement it in the old-fashion way — fetch data in two steps:
- Get average age (
avg-age) of all users in the database;
SELECT AVG(age)
FROM users;- Get users that have age more than previously fetched result;
SELECT *
FROM users
WHERE users.age > <avg-age>Step 1:
SQL Example:
Lets execute SQL query…
SELECT AVG(age)
FROM users;… and see what we will get:
Knex.js Example:
Step 2:
SQL Example:
Lets execute SQL query…
SELECT *
FROM users u
WHERE u.age > 36.75;… and see what we will get:
Knex.js Example:
Easy but not effective… Let’s see on the another, more elegant approach.
Subquery-fashion way
The simplest (I think) example of a subquery solution of a previous task will look like this:
SQL Example:
SELECT u.first_name,
last_name,
u.age
FROM users u
WHERE u.age >
(
SELECT AVG(age)
FROM users
);As you can see, we just combined two SQL queries into one. Our subquery will be evaluated first (average age) and only after that outer query data will be fetched:
But how to do that using knex.js library? Let’s find out
Knex.js Example:
Result will be exactly the same:
Simple, isn’t it? :)
