In our creating, reading, updating, and deleting (CRUD) overview lesson, we briefly touched on a few different ways we can query data from our database. In this lesson, we're going to be specifically taking a look at the query builder.
In the overview lesson, we learned that the query builder is available to us in both our Models and the Database module.
// initiating the query builder in Database module
await Database.from('table_name')
// initiating the query builder on a Model
await Project.query()
Once the query builder is initiated the syntax and capabilities are very similar. For that reason, we'll be explicitly using the Model query builder throughout today's lesson.
Where Statements
We've seen where statements a few times now, both in our overview and in our creating data lesson. However, there's more to the where statements that meets the eye. For this, we'll be running through several quick examples. In essence, all where statements and their variations provide different ways to filter down data being queried from our database.
The Three Types of Where
The where statement is a versatile one. It can accept a plethora of various argument types. We can provide it a key-value pair, an object of key-value pairs, or even a callback function.
// key value pair
await Project.query().where('name', 'My Example Project')
// object of key value pairs
await Project.query().where({ name: 'My Example Project' })
// callback function
await Project.query().where(query => query.where('name', 'My Example Project')
The first two are nice and readable while the callback function allows us to group together statements, which we'll talk about a tad later.
Where Statement Operators
In prior examples of where statements we used the equality operator, meaning the value in the database must exactly match our where statement value. But, what if we need to search for greater than, less than, or even whether a string contains a value? For this, where has a method option accepting three arguments, where the middle is the equality operator.
import { DateTime } from 'luxon'
import Project from 'App/Models/Project'
// less than
await Project.query().where('createdAt', '<', DateTime.local().toSQL())
// less than or equal
await Project.query().where('createdAt', '<=', DateTime.local().toSQL())
// greater than
await Project.query().where('createdAt', '>', DateTime.local().toSQL())
// greater than or equal
await Project.query().where('createdAt', '>=', DateTime.local().toSQL())
// like (contains)
await Project.query().where('name', 'LIKE', 'My Example%')
Here we have where statements checking whether the project's createdAt
date-time value in the database is less than, less than or equal to, greater than, then greater than or equal to the current date-time. DateTime
here is a package called Luxon. You might recall this from our Models where we're using this DateTime
as our createdAt
and updatedAt
types.
The last example here is using the LIKE
operator. In SQL, this allows us to search for partial matches in our database. Then, within our value, we're ending our string with a %
. This is the LIKE
operators wildcard value. We can utilize LIKE
several different ways to search for matches at the start, end, or in the middle of a string value.
where('name', 'LIKE', 'My Example%')
Where name starts with "My Example"where('name', 'LIKE', '%Example Project')
Where name ends with "Example Project"where('name', 'LIKE', '%Example%')
Where name contains "Example"
Multiple Where Statements
If we need to query based on multiple fields or values, we're going to need multiple where statements. Thankfully, to do this all we need to do is chain additional where methods off one another, as many times as we need.
const projects = await Project.query().where('name', 'LIKE', 'My Example%').where('statusId', 1)
Here we're querying for projects with a name
starting with "My Example" and also have a statusId
of 1. Alternatively, we could also use the method andWhere
if you feel that makes things a little easier to read.
const projects = await Project.query().where('name', 'LIKE', 'My Example%').andWhere('statusId', 1)
Cool, but what if we need to query with an or? Like, if we need projects with a statusId
of 1 or 2. For that, we can make use of the orWhere
method to signify we need one value or the other.
const projects = await Project.query().where('statusId', 1).orWhere('statusId', 2)
Okay, but what if we need both and and or? Like, if we need projects with a name
matching a search term and also have a statusId
of 1 or 2. For this, we can provide a callback method to the where
method. This will provide us with a scoped query, essentially wrapping the containing statements in parenthesis within the actual SQL that'll be generated.
const projects = await Project.query()
.where('name', 'LIKE', 'My Example%')
.where(query => query.where('statusId', 1).orWhere('statusId', 2))
Where Variations
So, we've already learned we have where
, andWhere
, and orWhere
. You'll be delighted to learn these aren't the only "where" based options! Let's quickly run through some of the other frequently used variations.
whereNot
We can use this to query where things are NOT equal to a value. Maybe, we want all projects that don't have astatusId
of 3 (imagine astatusId
of 3 here notes a deleted project).const projectsNot3 = await Project.query() .whereNot('statusId', 3) const projectNot3Or2 = await Project.query() .whereNot('statusId', 3) .orWhereNot('statusId', 2) const projectNot3And2 = await Project.query() .whereNot('statusId', 3) .andWhereNot('statusId', 2)
whereIn
&whereNotIn
We can use this to query where a value is or is not within an array. An example of this would be querying for a list of idsconst ids = [1, 2, 3] // whereIn const projectsIn123 = await Project.query() .whereIn('id', ids) const projectsStatus1OrIn123 = await Project.query() .where('statusId', 1) .orWhereIn('id', ids) const projectsStatus2AndIn123 = await Project.query() .where('statusId', 2) .andWhereIn('id', ids) // whereNotIn const projectsNotIn123 = await Project.query() .whereNotIn('id', ids) const projectsStatus1NotIn123 = await Project.query() .where('statusId', 1) .orWhereNotIn('id', ids) const projectsStatus2AndIn123 = await Project.query() .where('statusId', 2) .andWhereNotIn('id', ids)
whereNull
&whereNotNull
We can use these to query specifically for a NULL value or where a value is not NULL. Since NULL is the value in question we're querying for or against, these methods just require a column name.// whereNull - returns projects with a NULL description const projectsWithoutDesc = await Project.query() .whereNull('description') // whereNotNull - returns projects with a description value (not NULL) const projectsWithDesc = await Project.query() .whereNotNull('description')
Note that we're skipping over
whereExists
,whereNotExists
,whereBetween
,whereNotBetween
, andwhereRaw
. These all do exist, and their documentation is available on KnexJS' documentation.
Common Use-Cases
Now that we have where statements and their common variations, let's move onto other common query builder usages. Then, in the next lesson, we'll dive into querying relationships.
Now, I also want to note that all these methods being mentioned can be chained together to build very powerful queries. So, don't think you're limited to simple where statements.
Ordering
We can order our results using the orderBy
method in the query builder. By default, this method will sort in ascending order, however, we can provide a second argument to note whether to sort ascending or descending.
// ascending
const projectsAlphabeticallyByName = await Project.query()
.where('statusId', 1).orderBy('name')
const projectsAlphabeticallyByName2 = await Project.query()
.where('statusId', 1)
.orderBy('name', 'asc')
// descending
const projectsReverseAlphabeticallyByName = await Project.query()
.where('statusId', 1)
.orderBy('name', 'desc')
In this example, we're using orderBy
to sort our projects by their name. Ascending will sort alphabetically, and descending will sort reverse alphabetically. We could also do this with numbers, dates, and other types as well.
Limiting Results
To limit our results to a particular number of records, we can make use of the limit
method.
const projects = await Project.query().where('statusId', 1).limit(10)
Here we're specifying we only want back 10 projects with our query. Alternatively, if you remember back in the overview lesson, we can grab just the first record by calling first
. In addition, if we need to have a record, we could call firstOrFail
, which will throw a 404 if a record cannot be found.
const project = await Project.query().where('statusId', 1).first()
const projectOrFail = await Project.query().where('statusId', 1).firstOrFail()
Aggregate Results
We can utilize aggregate methods, count, min, max, sum, and avg to aggregate data together.
const projectTotal = await Project.query().where('statusId', 1).count('* as total')
// returns [{ total: 3 }]
const minSortOrder = await Database.from('project_task').min('sort_order as min')
// returns [{ min: 0 }]
const maxSortOrder = await Database.from('project_task').max('sort_order as max')
// returns [{ max: 8 }]
First, we're getting the number of projects with a
statusId
of 1.as total
is specifying we want our count returned back as "total". This is optional.Next, we're using the Database module to query from our intermediary table to get the minimum
sort_order
value within ourproject_task
table. Again, usingas min
to specify we want our min value stored as "min". This too is optional.Last, we're doing the same as min, but with max to get the maximum
sort_order
.
Limiting Columns
So far, we've been grabbing our data with all the columns that are on the table, but what if we want only need a subset of the data for each of our columns? Say, we're grabbing data for a select and only need the id and name. For this, we can use the select
method.
const projects = await Project.query().select(['id', 'name'])
Distinct Data
What if we have data with repetitive column values and we need to only query distinct occurrences? Maybe, we want to grab all admin users attached to projects. However, since users can belong to multiple projects, users could be admins on multiple projects. We don't want to query the user multiple times, we only want the user once. For this, we could make use of distinct
.
const adminUserIds = await Database.from('project_user')
.where('role_id', Role.ADMIN)
.select(['user_id'])
.distinct(['user_id'])
Grouping Data
What if we need to group multiple rows of the same value together? We don't really have a good use-case for this in our schema, but we can stretch to reach an example. Let's say we want to query to determine all the roles a user has for any project, but we don't need any role more than once.
const userRoles = await Database.from('project_user')
.where('user_id', 1)
.select(['user_id', 'role_id'])
.groupBy(['role_id'])
Conditional Statements
Let's say we want to conditionally query by a particular field. Maybe we have an optional filter. If we don't have a filter applied, we don't want to include it in our query, otherwise, we do. For this, we can use the if
method Adonis provides.
const statusFilter = 1;
await projects = await Project.query()
.if(statusFilter, query => query.where('statusId', statusFilter))
.orderBy('name')
Next Up
So, this only cracks the surface of what we can do with the query builder. For sake of brevity, we only touched on each implementation covered today. If you need more in-depth options or would like to review all the options, check out Adonis' and KnexJS' documentations.
In the next lesson, we're going to continue talks on querying by covering how we can query relationships, aggregate relationship data, and determine whether a relationship exists.
Join The Discussion! (4 Comments)
Please sign in or sign up for free to join in on the dicussion.
Anonymous (AquaFiona786)
Do you have belongTo use case ?
Please sign in or sign up for free to reply
tomgobich
Yes,
belongsTo
is typically applied when the model contains the id. For example:A full working example can be found here:
https://github.com/jagr-co/jagr.co/blob/main/app/Models/Comment.ts#L52
Please sign in or sign up for free to reply
Anonymous (StorkRonna987)
I want to use andWhere statement with null or empty value. I want to exclude whole instruction when value is null.
.andWhere(‘column’, null)
Tell me how to do it.
Please sign in or sign up for free to reply
tomgobich
The issue there is
.where('column', null)
can't be written to valid SQL asWHERE column = null
in itself results in an error. Because of this.whereNull('column')
and.whereNotNull('column')
exist.If you have a dynamic value in a variable, you can use the if query builder helper to determine which where clause to use.
Please sign in or sign up for free to reply