In the last lesson we spent some time going through various ways you can query data using the query builder. In this lesson, we're going to be talking specifically about querying relationships.

Querying relationships is one area where the difference between querying with our Models and using the Database module is significant. Since the Database module doesn't make use of our relationship definitions in our models it has no contextual information about our relationships. So, for this lesson, we'll be specifically utilizing our Models to query our relationships.

Preloading / Eager Loading

Lucid provides a method on our Model's query builder and instances called preload. This method will cover the vast majority of relationship querying you're going to need to do.

So, say we want to query all projects with all their tasks. We can this by calling our preload method. Then, we provide it the name of the relationship we want to preload, as it's defined in the Model.

import Project from 'App/Models/Project'

const projects = await Project.query().preload('tasks')

Here we're getting all our projects, and nested within each project will be that project's tasks. The resulting data would end up looking something like this.

[{
  name: "My Example Project",
  tasks: [{
    name: "Introductory Task",
    // ... other task data 
  }]
  // ... other project data,
}]

Filtering Relationship Data

What if we need to filter down the data on the relationship we're preloading? For this, the preload method accepts a second argument. This second argument is a callback function that provides us our relationship's query. So, we can use this callback argument to filter, sort, or limit down our related data.

const projects = await Project.query()
  .preload('tasks', (query) => query
    .where('status_id', 1)
    .orderBy('due_at', 'desc')
  )

Here we're again querying all our projects, however, this time we're only going to get back tasks for each project that have a status_id of 1. Additionally, our project's tasks will be ordered descending by their due date.

Preloading Multiple Relationships

To preload multiple relationships, all we need to do is chain another preload off our initial preload. We can do this as many times as needed.

const projects = await Project.query()
  .preload('tasks', (query) => query.orderBy('due_at', 'desc'))
  .preload('creator')
  .preload('assignee')

So here each project we get back will contain the project's tasks, the project creator, and the assignee if one is defined. Our tasks will also, again, be ordered descending by their due date.

Preloading From Instance

Let's say we've already performed a query, maybe we used the findOrFail method off our Model and we need to then preload a relationship off that returned instance. Lucid, thankfully, also provides the preload method on record instances. So, all we need to do is call preload off the instance and it'll work just the same as if we'd done it within the query builder.

const user = await User.findOrFail(1)

await user.preload('projects')

Here we're querying for the user with an id of 1. Then, with the returned record instance, we're preloading all of that user's projects onto the user object. So, our underlying user object now looks like the below.

{
  username: "testuser1",
  email: "[email protected]",
  projects: [{
    name: "My Example Project",
    // ... other project fields
  }],
  // ... other user fields
}

Preloading Nested Relationships

What if we need to query all projects a user belongs to and include the latest 3 tasks of each project? To do this we would need to not only preload our projects off our user, but we'd also need to preload the tasks off the preloaded projects. Thankfully, the preload can be called inside a preload's callback query.

const user = await User.findOrFail(1)

await user.preload('projects', (query) => query
  .preload('tasks')
  .orderBy('created_at', 'desc')
  .limit(3)
)

Here we're querying for the user with an id of 1. Then, with the returned record instance, we're preloading all project's the user belongs to. Lastly, we're querying all tasks for each project we get back, ordered newest to oldest, by their created_at value and grabbing only the first 3 results. The data here would look something like the below.

{
  username: "testuser1",
  email: "[email protected]",
  projects: [{
    name: "My Example Project",
    tasks: [{
      name: "Introductory Task",
      // ... other task fields
    }],
    // ... other project fields
  }],
  // ... other user fields
}

Query Through

So, we've covered how we can append related data to our existing query results, but what if we just need the related data? Say, maybe we want our user and their projects stored in two separate variables because it'd be more convenient.

To do this, we can make use of a method Lucid provides us called related. In essence, this will return back a barebones version of the Model for the relationship we specify, allowing us to query and mutate data while keeping the relationship in-tact. This allows us to query the underlying related data itself instead of loading it onto our results. So, to query a user's projects without getting the user data itself, we'd do the below.

const user = await User.findOrFail(1)

const userProjects = await user.related('projects').query().orderBy('name')

Here we first query for and get our user instance. Then, off our user instance, we call the related method, passing it the relationship name we want the Model for. From there, we can call query and we're returned back a full query builder. So here, userProjects is just an array of projects, no user data included.

Since we get a full query builder for our related records returned back to us, we can then filter, preload, and do whatever we need.

const user = await User.findOrFail(1)

const userProjects = await user.related('projects').query()
  .preload('tasks', (query) => query
    .orderBy('due_at', 'desc')
    .preload('creator')
    .preload('assignee')
  )
  .preload('users')

Here we're first querying our user instance. Then, we're grabbing all the user's projects. Then, for each project the user belongs to, we're preloading its tasks and assigned users. Lastly, for each task the project has we're preloading each task's creator and assignee plus ordering the tasks descending by their due date.

If we needed to, we could also utilize this approach to create, update, or delete data as well. Remember, this returns back the full query builder to us for our related Model. So, we could delete all tasks belonging to a project using this same approach.

const project = await Project.findOrFail(1)

await project.related('tasks').query().delete();

Intermediary Table Data

Intermediary table data is additional data that's on intermediary (or pivot) tables. These would be our project_task and project_user tables. On our project_task table we have an additional intermediary column sort_order we may want to query with our records. Our project_user table then has a role_id we may want to query with our records. By default, Lucid will include only the foreign keys from our intermediary tables when we query. Let's discuss how our intermediary data is included, and some ways we can define we want these additional columns to be included as well. Note that this section is specific to many-to-many relationships.

Intermediary Data

When we query our many-to-many relationships, the foreign keys of our intermediary table will be automatically included. However, since they don't belong to anyone Model object, Lucid will store them away in an $extras object.

So, when we query our project users, we can access the foreign keys (our ids), like so.

const user = await User.findOrFail(1)

await user.preload('projects')

const projectIds = user.projects.map(project => project.$extras.pivot_project_id)

Note that pivot_ is prefixed to our foreign key's name within the $extras object. That's done for all by default as well.

Automatic Inclusion

We can tell Lucid to automatically include additional intermediary table columns when we query relationships by defining these columns on our Model's relationship decorator pivotColumns option. In fact, we set up this up in our Model Relationship lesson earlier.

export default class Project extends BaseModel {
  // ...

  @manyToMany(User, {
    pivotColumns: ['role_id']
  })
  public users: ManyToMany<typeof User>
}

Now Lucid will automatically include our role_id anytime we query our users from our project(s).

const user = await User.findOrFail(1)

await user.preload('projects')

const roleIds = user.projects.map(project => project.$extras.pivot_role_id)

Additionally, we'll also automatically have this column available to us within our queries to filter and sort by.

const user = await User.findOrFail(1)

await user.preload('projects', query => query.where('role_id', 1))

Per Query Inclusion

It may be that you only need additional intermediary data on one or two queries. In this case, it wouldn't make sense to automatically include it for every query of this relationship. Therefore, we can define we need these columns within the query itself, by defining the pivotColumns within the preload.

const user = await User.findOrFail(1)

await user.preload('projects', query => query.pivotColumns(['role_id']))

const roleIds = user.projects.map(project => project.$extras.pivot_role_id)

Filtering By Intermediary Data

What if we don't need the additional intermediary data returned back, but we do need to filter our related data down using it? Thankfully, Lucid provides us an additional where method called wherePivot which allows us to do just that.

const project = await Project.findOrFail(1)

await project.preload('users', query => query.wherePivot('role_id', 1))

const admins = project.users;

Here we're querying all of the project's admins (assuming role_id 1 is admin) using the role_id defined in the project_user intermediary table.

Querying Relationship Existence

Another thing Lucid empowers us to easily do is to check whether a relationship exists. For example, we could grab only the user's projects that have tasks.

const user = await User.findOrFail(1)

await user.preload('projects', query => query.has('tasks'))

The has method here will query the relationship's existence. So, it will only return projects where the project has at least one task. The has method can also accept an operator and the number of records we require the relationship to have as well.

const user = await User.findOrFail(1)

await user.preload('projects', query => query.has('tasks', '>=', 3))

Here's we're querying for all the user's projects where the project has at least 3 tasks. So far we aren't applying any additional querying on our tasks, but we could do that as well by using whereHas. So, we could take this a step further and query only the projects that have uncompleted tasks. The below assumes a status_id of 3 notes completed tasks.

const user = await User.findOrFail(1)

await user.preload('projects', query => query
  .whereHas('tasks', query => query.whereNot('status_id', 3))
)

These two methods also come available in andHas, orHas, andWhereHas, and orWhereHas as well if you need to query the existence between two or more relationships.

Querying Relationship Absence

Maybe we want to query all projects that don't have any tasks. To do this, we can use the inverse of has and whereHas, doesntHave and whereDoesntHave.

const user = await User.findOrFail(1)

await user.preload('projects', query => query.doesntHave('tasks'))

// -- or --

await user.preload('projects', query => query
  .whereDoesntHave('tasks', query => query.whereNot('status_id', 3))
)

First, we're querying all the user's projects that don't have any tasks. Second, we're querying all the user's projects that don't have any uncompleted tasks. Again, assuming a status_id of 3 notes a completed task.

These also come in andDoesntHave, andWhereDoesntHave, orDoesntHave, and orWhereDoesntHave as well if you need to query the absence of a relationship between two or more relationships.

Aggregating Relationships

Lastly, we have the ability to aggregate our relationship data. We can quickly and easily count all records of a relationship by using the withCount method.

const user = await User.query()
  .where('id', 1)
  .withCount('projects')
  .firstOrFail()

Here we're grabbing our user with an id of 1. We're also using withCount to note we want the count of all the user's projects. Like our pivot columns, this to will be appended onto an $extras object on our returned record(s).

So, we can access our user's project count via user.$extras.projects_count. Note that the default naming here is the relation name and count snake cased together. If you'd like to manually define a name for your count you can do that as well.

const user = await User.query()
  .where('id', 1)
  .withCount('projects', query => query.as('user_project_count'))
  .firstOrFail()

Here we're explicitly naming our count variable as userprojectcount and we'd be able to access this via user.$extras.userprojectcount. And as you may have noticed, we have our query context within our withCount callback, so we can absolutely filter down the data being counted as well or even reach through a relation, then count.

const user = await User.findOrFail(1)

await user.preload('projects', projectsQuery => projectsQuery
  .withCount('tasks', tasksQuery => tasksQuery.where('status_id', 3))
)

Here we're querying all our user's projects, however, with all our user's projects we're also grabbing the count of the completed tasks each project has. Assuming a status_id of 3 notes a completed task.

Other Aggregations

So far we've just been counting the totals of our records or filtered records. However, the callback of the withCount method can be used to aggregate our counts in any way we need. So, in addition to counting, we could also use withCount to get the min, max, sum, and average as well. Remember, we have our sort_order defined as a pivot column in our Model relationship definitions, so we can directly access this column in our queries.

const project = await Project.query()
  .where('id', 1)
  .withCount('tasks', query => query.sum('sort_order').as('sort_sum'))

Now, I don't have a valid reason why you'd want or need to sum a sort order, but it's one of the only numbers we have in our schema to work with so bear with me. Here we're grabbing our project with an id of 1. Then, including with this project the sum or our project's task sort orders within our project_task table.

Next Up

So, we've covered how we can query our relationships, determine whether relationships exist or don't exist, and how we can aggregate our relationships. I feel like our creating, reading, updating, and deleting (CRUD) overview lesson covered updating and deleting pretty well, so we won't be digging into specific lessons on these. However, we still need to cover query scopes. So, in the next lesson, we'll be learning all about query scopes and how we can use them to easily create reusable queries.