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 a statusId of 3 (imagine a statusId 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 ids

  const 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, and whereRaw. 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 }]

1. 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.

2. Next, we're using the Database module to query from our intermediary table to get the minimum sort_order value within our project_task table. Again, using as min to specify we want our min value stored as "min". This too is optional.

3. 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.