Let's Learn AdonisJS 7: Working with Model Relationships

Working with Model Relationships

In This Lesson

Learn the difference between eager and lazy loading relationships in AdonisJS. We'll also learn about querying nested relationships, filtering by relationships, and more.

Created by: @tomgobich
Published: 4 months ago

In the last lesson, we saw how we can join relationships together using the query builder, but in most cases, we can lean on model relationships instead to simplify our query logic. Model relationships can be used for any relationship defined within our models, and we can nest relationships as much as needed.

Eagerly Loading Relationships

To start, on our challenges.show page, we may want to show the details of the person who created the challenge. To do this using our Challenge model's relationship to the creator, we can eagerly load the relationship onto the queried results using the preload() method from our query builder.

export default class ChallengesController {
  // ...

  /**
   * Show individual record
   */
  async show({ view, params }: HttpContext) {
    const challenge = await Challenge.query()
      .preload('createdBy')
      .where('id', params.id)
      .firstOrFail()
      
    const editUrl = urlFor('challenges.edit', { id: params.id })

    return view.render('pages/challenges/show', { challenge, editUrl })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

With model relationships, the entire relationship we've loaded is populated into its own model via the relationship property. For example, by preloading the createdBy relationship, we can now access the creator's User model instance via challenge.createdBy directly from our query's result.

Furthermore, the loaded relationship gets its own query builder as well, which we can access via the second argument. So, if we wanted to just select the firstName and id, we can do that!

export default class ChallengesController {
  // ...

  /**
   * Show individual record
   */
  async show({ view, params }: HttpContext) {
    const challenge = await Challenge.query()
      .preload('createdBy', (query) => query.select('id', 'fullName'))
      .where('id', params.id)
      .firstOrFail()
      
    const editUrl = urlFor('challenges.edit', { id: params.id })

    return view.render('pages/challenges/show', { challenge, editUrl })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

This type of relationship loading, using preload() is called eager-loading because the relationships are immediately requested. They're also requested in a manner that minimizes the number of queries needed and accounts for the N+1 problem.

For example, if you have 50 challenges and want to load the creator for each, the N+1 problem would be fetching the challenges, then looping over them to query their creators, one by one, using a separate query for each, for a total of 51 queries. An easy bottleneck in any application. Eager-loading solves this by instead using just 2 queries by:

Querying for the challenges
Grabbing all the challenge's creatorIds
Fetching all the creators in a single query
Populating the related creator into each challenge

Lazily Loading Relationships

Though preload() can be more efficient with lists of data, there's no real benefit when working with singular records, like we are in our show and edit handlers. In these situations, we may choose to lazily-load the relationship by loading it at a point dissociated with the original query. This comes in particularly handy with our static model query methods, like findOrFail(), because it gives us a way to populate a relationship for these queries using the load() method.

export default class ChallengesController {
  // ...

  /**
   * Edit individual record
   */
  async edit({ params, view }: HttpContext) {
    // find the challenge being edited by its id
    const challenge = await Challenge.findOrFail(params.id)

    await challenge.load('createdBy')

    // pass the challenge to the view
    return view.render('pages/challenges/edit', { challenge })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

The load() method works just like preload(), the only difference being how the relationship is loaded; preload() is for eager-loading and load() is for lazy-loading.

Counts & Aggregates

We can also use dedicated methods to load relationship counts and aggregates as well. When lazy-loading, the methods to do so are loadCount() and loadAggregate(). For example, if we want to include the number of participants for a challenge, we can use loadCount('participants').

export default class ChallengesController {
  // ...

  /**
   * Edit individual record
   */
  async edit({ params, view }: HttpContext) {
    // find the challenge being edited by its id
    const challenge = await Challenge.findOrFail(params.id)

    await challenge.load('createdBy')
    await challenge.loadCount('participants')

    // pass the challenge to the view
    return view.render('pages/challenges/edit', { challenge })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

There aren't dedicated columns for counts nor aggregates, so again, these will be populated onto the $extras object of our returned model instance. They're also given a default name from a snake case concatenation of the relationship property name and count, for example, our participant count will be participants_count and can be accessed via challenge.$extras.participants_count.

We can also give it a name of our own, for example, let's say we ultimately want three counts:

Total number of participants
Total number of participants who have not completed the challenge
Total number of participants who have completed the challenge

To start, we'll add two additional counts and filter them by the pivot table's completed_at column. To do this, there are several special purposed wherePivot() methods to filter a many-to-many relationship by their pivot table data. For this particular check, we'll use whereNullPivot() and whereNotNullPivot().

export default class ChallengesController {
  // ...

  /**
   * Edit individual record
   */
  async edit({ params, view }: HttpContext) {
    // find the challenge being edited by its id
    const challenge = await Challenge.findOrFail(params.id)

    await challenge.load('createdBy')
    await challenge.loadCount('participants')
    await challenge.loadCount('participants', (query) => query.whereNullPivot('completed_at'))
    await challenge.loadCount('participants', (query) => query.whereNotNullPivot('completed_at'))

    // pass the challenge to the view
    return view.render('pages/challenges/edit', { challenge })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

Next, we can give them applicable names via their callback query builders by adding an as() method to manually give them a name applicable to their purpose.

export default class ChallengesController {
  // ...

  /**
   * Edit individual record
   */
  async edit({ params, view }: HttpContext) {
    // find the challenge being edited by its id
    const challenge = await Challenge.findOrFail(params.id)

    await challenge.load('createdBy')
    await challenge.loadCount('participants')

    await challenge.loadCount('participants', (query) => 
      query.whereNullPivot('completed_at').as('not_completed_count')
    )

    await challenge.loadCount('participants', (query) => 
      query.whereNotNullPivot('completed_at').as('completed_count')
    )

    // pass the challenge to the view
    return view.render('pages/challenges/edit', { challenge })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

With this, we'll now get three separate counts for our challenges!

participants_count - the total number
not_completed_count - the total number who haven't completed the challenge
completed_count - the total number who have completed the challenge

We probably don't need these counts on our edit page, though, so let's instead do this same thing using our query builder on our show handler! Instead of loadCount() all we need to do is use withCount() instead, everything else remains the same!

export default class ChallengesController {
  // ...

  /**
   * Show individual record
   */
  async show({ view, params }: HttpContext) {
    const challenge = await Challenge.query()
      .preload('createdBy', (query) => query.select('id', 'fullName'))
      .withCount('participants')
      .withCount('participants', (query) =>
        query.whereNullPivot('completed_at').as('not_completed_count')
      )
      .withCount('participants', (query) =>
        query.whereNotNullPivot('completed_at').as('completed_count')
      )
      .where('id', params.id)
      .firstOrFail()
      
    const editUrl = urlFor('challenges.edit', { id: params.id })

    return view.render('pages/challenges/show', { challenge, editUrl })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

Now, when using the aggregate version of these methods, like withAggregate() it's on us to perform the type of aggregation we're after within the callback query builder. This gives us a fluid way to build counts, averages, min, max, and other aggregates as needed. For example, let's replace our withCount('participants') with an aggregate usage.

export default class ChallengesController {
  // ...

  /**
   * Show individual record
   */
  async show({ view, params }: HttpContext) {
    const challenge = await Challenge.query()
      .preload('createdBy', (query) => query.select('id', 'fullName'))
      .withAggregate('participants', (query) => query.count('*').as('participants_count'))
      .withCount('participants', (query) =>
        query.whereNullPivot('completed_at').as('not_completed_count')
      )
      .withCount('participants', (query) =>
        query.whereNotNullPivot('completed_at').as('completed_count')
      )
      .where('id', params.id)
      .firstOrFail()
      
    const editUrl = urlFor('challenges.edit', { id: params.id })

    return view.render('pages/challenges/show', { challenge, editUrl })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

Here we're not filtering at all, though we could. Then, we're counting the result of all columns using *. Finally, aggregates must be given a name, so we're manually setting it to participants_count.

Any of these can be freely used on our show page, so let's add the challenge's creator and participants.

@layout()

  <div class="hero">
    <h1>{{ challenge.text }}</h1>
    <p>Points: {{ challenge.points }}</p>
    <p>Created by: {{ challenge.createdBy.fullName }}</p>
    <p>Participants: {{ challenge.$extras.participants_count }} ({{ challenge.$extras.completed_count }} completed)</p>

    <a href="{{ editUrl }}" class="button">Edit Challenge</a>
    <button type="submit" form="destroy" class="button destructive">Delete</button>
  </div>

  @!form({ id: 'destroy', route: 'challenges.destroy', routeParams: { id: challenge.id }, method: 'DELETE' })

@end
  
    
      Copied!
      
        
      
    
  
  
resources
views
pages
challenges
show.edge

We didn't add any completed participants in our seeder, so it'll be all 0's across the board there for the moment.

Eager Loading & Joining

Let's return to our index handler where we have our join. We now know that we can alternatively eager-load the relationship instead of joining. Meaning, if we do that, we can remove our users.full_name from the select list.

export default class ChallengesController {
  /**
   * Display a list of resource
   */
  async index({ view }: HttpContext) {
    const challenges = await Challenge.query()
      .where('points', '>', 50)
      .where((q) => q.where('text', 'LIKE', 'input%').orWhere('text', 'LIKE', 'index%'))
      .preload('createdBy')
      .select('challenges.id', 'text', 'points')
      .join('users', 'users.id', 'challenges.creator_id')
      .orderBy('users.full_name')

    return view.render('pages/challenges/index', { challenges })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

However, since model relationships are queried for and populated post-query, they require the relationship's foreign key to be included within the select list. Otherwise, Lucid won't be able to populate the relationship. Without it, we'll see an exception like:

Cannot preload "createdBy", value of "Challenge.creatorId" is undefined. Make sure to set "null" as the default value for foreign keys

This can be easily solved by adding the creatorId into our select list of our query builder!

export default class ChallengesController {
  /**
   * Display a list of resource
   */
  async index({ view }: HttpContext) {
    const challenges = await Challenge.query()
      .where('points', '>', 50)
      .where((q) => q.where('text', 'LIKE', 'input%').orWhere('text', 'LIKE', 'index%'))
      .preload('createdBy')
      .select('challenges.id', 'text', 'points', 'creatorId')
      .join('users', 'users.id', 'challenges.creator_id')
      .orderBy('users.full_name')

    return view.render('pages/challenges/index', { challenges })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

Also note, we're still joining here, which allows us to order by the joined relationship. This is one thing we can't do directly with model relationships, and are where using joins comes in handy. Since we are still joining, we still have the ambiguous id column and still need to reference challenges.id specifically in our select list. However, do note, if we omit our select list, Lucid will be able to handle the ambiguity on its own without issue!

export default class ChallengesController {
  /**
   * Display a list of resource
   */
  async index({ view }: HttpContext) {
    const challenges = await Challenge.query()
      .where('points', '>', 50)
      .where((q) => q.where('text', 'LIKE', 'input%').orWhere('text', 'LIKE', 'index%'))
      .preload('createdBy')
      .join('users', 'users.id', 'challenges.creator_id')
      .orderBy('users.full_name')

    return view.render('pages/challenges/index', { challenges })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

Filtering By Relationships

Finally, if we need to filter our results by the relationship at all with the query builder, we can do that with ease using specialized where methods for relationships. For example, if we only want to include challenges from creators with a public profile, we could do something like:

export default class ChallengesController {
  /**
   * Display a list of resource
   */
  async index({ view }: HttpContext) {
    const challenges = await Challenge.query()
      .where('points', '>', 50)
      .where((q) => q.where('text', 'LIKE', 'input%').orWhere('text', 'LIKE', 'index%'))
      .whereHas('createdBy', (creator) =>
        creator.whereHas('profile', (profile) => profile.where('isPublic', true))
      )
      .preload('createdBy')
      .join('users', 'users.id', 'challenges.creator_id')
      .orderBy('users.full_name')

    return view.render('pages/challenges/index', { challenges })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

This will limit our challenges to those where challenge.createdBy.profile.isPublic is true! Let's simplify this query so we can see exactly what the whereHas() and preload() methods are executed via the SQL that's executed! By the way, for any of these, you can find the executed SQL printed out in your terminal for convenience.

export default class ChallengesController {
  /**
   * Display a list of resource
   */
  async index({ view }: HttpContext) {
    const challenges = await Challenge.query()
      .whereHas('createdBy', (creator) =>
        creator.whereHas('profile', (profile) => profile.where('isPublic', true))
      )
      .preload('createdBy')

    return view.render('pages/challenges/index', { challenges })
  }

  // ...
}
  
    
      Copied!
      
        
      
    
  
  
app
controllers
challenges_controller.ts

First, we have the main query from our query builder with the relationship filtering via whereHas(). The SQL generated for this is:

SELECT
  *
FROM
  `challenges`
WHERE
  EXISTS (
    SELECT
      *
    FROM
      `users`
    WHERE
      (
        EXISTS (
          SELECT
            *
          FROM
            `profiles`
          WHERE
            (`is_public` = ?)
            AND (`users`.`id` = `profiles`.`user_id`)
        )
      )
      AND (`users`.`id` = `challenges`.`creator_id`)
  ) [ true ]
  
    
      Copied!

As you can see whereHas() directly filters the query using an EXISTS predicate! We can also see our where clause uses SQL parameterization to help protect us against injection attacks.

Then, we have the query run for our preload(). The SQL generated for this is:

SELECT * FROM `users` WHERE `id` IN (?, ?, ?, ?, ?, ?, ?) [
  8, 1, 7, 3,
  6, 10, 9
]
  
    
      Copied!

So, amongst our challenges being queried, we have seven distinct creators. Lucid queries these separately, then fills them into the challenge.createdBy property post-query to keep things efficient and again to solve the N+1 problem.

Join the Discussion 0 comments

Be the first to comment!

Playing Next Lesson In

Let's Learn AdonisJS 7 #3.5

Working with Model Relationships

Eagerly Loading Relationships

Lazily Loading Relationships

Counts & Aggregates

Eager Loading & Joining

Filtering By Relationships

Join the Discussion 0 comments

Project Foundations

Fundamentals

Database

Authentication