Understanding 'include' when resolving source association from target in sequelize and apollo-server

Question

I am writing a resolver (using sequelize) for my apollo-server and am struggling to understand how the resolve of this backwards association works... I am super confused with how the include in my sequelize query is working.

my model associations:

User.hasOne(models.Profile)  <- so the foreign key 'userId' is on the profile table

Profile.belongsTo(models.User) <- foreign key 'userId' still on profile table

my graphql schema:

type Profile {
  id: ID!
  user: User!
}

my resolver: (I can't query the User model using where: {profileId: profile.id} as the profileId foreign key doesn't exist on User) so... I use include..

Profile: {
  user: async (profile, _args, { models }) => {
    return await models.User.findOne({
      include: [{
        model: models.Profile,
        where: { id: profile.id } <- this makes zero sense to me.. id is the id of the Profile row? how does this work??
      }]
    })

Daniel Rearden Daniel Rearden · Accepted Answer · 2018-12-23T19:21:16

By using the include option, you are eagerly loading the specified associated model. From the docs:

When you are retrieving data from the database there is a fair chance that you also want to get associations with the same query - this is called eager loading.

When you include an associated model, under the hood Sequelize attaches a join statement to the query it generates. By default, this is a LEFT OUTER JOIN. That means if you write:

User.findAll({ include: [{ model: Profile }] })

The resulting query will find all Users. If a user happens to have a Profile, the relevant row in the result will also include the profile fields. On the other hand, we can force the join to be an INNER JOIN by adding the required option:

User.findAll({ include: [{ model: Profile, required: true }] })

Because it's an INNER JOIN, the resulting query returns only Users that have a Profile.

When you add a where inside the include, the JOIN is automatically converted to an INNER JOIN (unless you explicitly set required to false). The where clause itself actually becomes part of the ON statement of the INNER JOIN. So if we write:

User.findAll({ include: [{ model: Profile, where: { id: 'someId' } }] })

the results will include all Users that have a Profile and where that profile's id is equal to someId. The where is always specific to the model we're including, so there's no need to specify which model's id field we're interested in.

Lastly, if use findOne instead of findAll, Sequelize simply adds a LIMIT of 1 to the query and the method resolves to a single instance of the Model instead of an array.

A complete discussion of joins is outside the scope of this question. You can check out these other questions for more details:

Understanding 'include' when resolving source association from target in sequelize and apollo-server

1 Answers