Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
using Include in sequelize

'Include' in Sequelize: The One Confusing Query That You Should Memorize

When querying your database in Sequelize, you'll often want data associated with a particular model which isn't in the model's table directly. This data is usually typically associated through join tables (e.g. a 'hasMany' or 'belongsToMany' association), or a foreign key (e.g. a 'hasOne' or 'belongsTo' association).

When you query, you'll receive just the rows you've looked for. With eager loading, you'll also get any associated data. For some reason, I can never remember the proper way to do eager loading when writing my Sequelize queries. I've seen others struggle with the same thing.

Eager loading is confusing because the 'include' that is uses has unfamiliar fields is set in an array rather than just an object.

So let's go through the one query that's worth memorizing to handle your eager loading.

The Basic Query

Here's how you would find all the rows of a particular model without eager loading.

Albums.findAll()
.then(albums => console.log(albums))
.catch(console.error)

Vanilla Include

Here's how you would find all the Artists associated with your albums.

Albums.findAll({
  include: [{// Notice `include` takes an ARRAY
    model: Artists
  }]
})
.then(albums => console.log(albums))
.catch(console.error)

This will take you a long way. Sequelize is smart enough to pull in any rows from your Artists model that are associated with your Albums.

Include takes an array of objects. These objects have properties like model, as, and where which tell Sequelize how to look for associated rows.

Customized Include with an Alias

Now, let's customize how we receive our eagerly-loaded rows.

Albums.findAll({
  include: [{
    model: Artists,
    as: 'Singer' // specifies how we want to be able to access our joined rows on the returned data
  }]
})
.then(albums => console.log(albums))
.catch(console.error)

In this query, we have specified that the instances we receive back from Sequelize should have a property called 'Singer'. We'll be able to access any rows from our Artists table associated with our Albums through this .Singer property.

Customized Include with Alias and Where

Finally, let's layer a where onto our include, so we can narrow down the rows that we'll receive.

Albums.findAll({
  include: [{
    model: Artists,
    as: 'Singer',
    where: { name: 'Al Green' } //
  }]
})
.then(albums => console.log(albums))
.catch(console.error)

Our where query should look familiar if you've used Sequelize before: it has the same format as any typical Sequelize query. Our query will now only return joined rows where 'Al Green' is the name of the associated artist. We can access Al Green's artist data for relevant Album instances on our aliased .Singer property.

To wrap up, include takes an array of objects. These objects are queries of their own, essentially just Sequelize queries within our main query. Inside each include query we specify the associated model, narrow our results with where, and alias our returned rows with as. Memorizing model, where, as, and that include takes an array will make your next experience with eager loading much more pleasant.

@sidoshi

This comment has been minimized.

Copy link

commented Aug 29, 2017

Really helpful. Thanks!

@bprivateer

This comment has been minimized.

Copy link

commented Sep 1, 2017

how can i include more than one includes model?

@bprivateer

This comment has been minimized.

Copy link

commented Sep 1, 2017

very helpful btw

@holmberd

This comment has been minimized.

Copy link

commented Sep 2, 2017

@bprivateer to include more than one model with include, you would add them to the include array.
Let's say you had another association called Genre that you wanted to include with your Album query, it would look something like this:

Album.findAll({
  include: [
    {
      model: Artist,
      as: 'Singer',
    },
    {
      model: Genre
    }
]

To include all associations use include: [{all: true}]

Extra:

If your Genre model has a many-to-many association with model Album by a join table called albumgenre (i.e. the join table contains all the Album records associated with that specific Genre) and you didn't want to fetch all attributes from the join table albumgenre with your query. You can then pass through: { attributes: [] } with your include.

Album.findAll({
  include: [
    {
      model: Artist,
      as: 'Singer',
    },
    {
      model: Genre
      through: { attributes: [] }
    }
]
@EpicWiz

This comment has been minimized.

Copy link

commented Sep 5, 2017

Very nice explanation. Thank you!
Also, thanks to @holmberd for expanding on it.

@kannan007

This comment has been minimized.

Copy link

commented Sep 26, 2017

Hi this is nice but am getting an error tasks is not associated to user. I have two tables user and task.
User Model file

	id : {
		type: Sequelize.INTEGER,
		primaryKey: true
	},
    username: {
        type: Sequelize.STRING,
    },
    password: {
    	type: Sequelize.STRING
    }
});
module.exports = Users;```

Task Model file

```var Tasks = sequelize.define('tasks', {
	user_id: {
		type: Sequelize.INTEGER,
        allowNull: false,
		references: {
        // This is a reference to another model
         model: users,
         // This is the column name of the referenced model
         key: 'id'
       }
	},
    taskname: {
        type: Sequelize.STRING
    }
});
module.exports = Tasks;```

My user route file

```var users = require('../models/user');
var tasks = require('../models/task');
Userrouter.route('/')
.get(function(req,res,next) {
	users.findAll({
		include: [{// Notice `include` takes an ARRAY
	    	model: tasks
	  	}]
	}).then((data) => {
		res.send(data);
	}).catch((err) => {
		console.log(err);
	});
})```

Where am making a mistake any help would be appreciated thanks
@gbyonivo

This comment has been minimized.

Copy link

commented Nov 17, 2017

How do I select albums with no artists? or artists with no albums?

@andela-kakpobome

This comment has been minimized.

Copy link

commented Feb 11, 2018

@gbyonivo For the case of How do I select albums with no artists? I believe it'll simply be a case of checking that a known field in the Artist model is null. Something like:

Album.findAll({
  include: [
    {
      model: Artist,
      as: 'Singer',
      where: { artist.name: null }
    }
]
@vitaliyKorzhenko

This comment has been minimized.

Copy link

commented Feb 13, 2018

thanks man

@mooniker

This comment has been minimized.

Copy link

commented Mar 6, 2018

@adela-kakpobome: what is "artist.name" in that snippet, and why can't the where clause be { id: null }?

@BronwynHarris

This comment has been minimized.

Copy link

commented May 23, 2018

This is great, thank you!

@Songkeys

This comment has been minimized.

Copy link

commented Jun 16, 2018

Thank you!
Another question: can I get the instance of what I include? Like to give a column in the included table an increment? Cuz I don't want to query the included one again then to manipulate it.

@cld-santos

This comment has been minimized.

Copy link

commented Jun 20, 2018

@holmberd thanks a lot for your extra tip.

@sharmanitin

This comment has been minimized.

Copy link

commented Jun 24, 2018

@holmberd how about fetch parent model only if submodel exist, i.e something like :
Album.findAll({ include: [ { model: Artist, require: true } ]

@ketavchotaliya

This comment has been minimized.

Copy link

commented Jun 28, 2018

I have Ticket model and TicketComment model, Ticket has a hasMany relation with TicketComment; Everything works perfectly in eager loading with where inside include But when I haven't any data in child table at that time I'm getting a not found error. When I remove where clause then it will return blank (empty) key in response.

I'm attaching code as well for reference

  1. Relation Defination
    Ticket.hasMany(TicketComment, {as: 'ticketComments', foreignKey: 'ticketId', targetKey: 'ticketId'});

  2. Find method with eager loading

     var ticketDetails = await Ticket.findOne({
     include: [
         {
             model: TicketComment,
             as: 'ticketComments',
             attributes: ['commentId', 'ticketId', 'title', 'description', 'createdBy'],
             where: {isDelete: false} // ticket object will be blank if comment not found; 
             //wherever it will return blank ticketComments object if remove where condition
         }
     ],
     where: {
         ticketId: req.params.ticketId,
     },
     attributes: ['ticketId', 'ticketNumber', 'title', 'description', 'type', 'severity', 'status', 'userId'],
     });
    
@azaeng04

This comment has been minimized.

Copy link

commented Aug 4, 2018

What about the added methods on models with associations such as addPerson(), addPersons(), setPerson(), setPersons() ? Do they work similar to the include?

@jp26jp

This comment has been minimized.

Copy link

commented Aug 19, 2018

@andela-kakpobome I'm almost positive that this is not valid syntax:

where: { artist.name: null }
@BenjaminDish

This comment has been minimized.

Copy link

commented Sep 19, 2018

Hello, thanks this is very useful.

I'm trying to deal with a similar case, with a Model associated to instances of itself. It is exactly like this discussion on GitHub : One main comment and X children comments.

Comment.findAll({
  include: [
    {
      model: Comment,
      as: 'children'
    }
]

This run into an infinite loop. It seems like it tries to include infinitely the Comment model. Actually, the data in base is well structured and there is no infinite bindings between Comments, just master comments, and children comments (answers to the first one).

Is there something to add / remove in the scope to make it work properly ?

@ajbraus

This comment has been minimized.

Copy link

commented Dec 7, 2018

Isn't the problem that this does not output what you'd expect:

{ 
  name: 'dude', 
  age: 43,
  posts: [ 
    { title: "awesome", body: "Great" }
  ]
}

Instead it outputs a bunch of garbage.

@ajbraus

This comment has been minimized.

Copy link

commented Dec 7, 2018

Found a fix to this ^^^. just add nested: true to the query options.

@luisfrocha

This comment has been minimized.

Copy link

commented Feb 14, 2019

I have an extension of this problem. I'm trying to associate two tables on different, disparate databases altogether. Below is info:
DB1:

// PostgreSQL
CREATE TABLE "public"."CourseCatalog" (
    "courseid" varchar(255) NOT NULL,
    "title" varchar(255) NOT NULL,
    PRIMARY KEY ("courseid")
);

DB2:

// MySQL DB
CREATE TABLE `completions` (
  `member_id` varchar(10) NOT NULL,
  `module_id` varchar(10) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

where DB2.module_id = DB1.courseid. So, what I'm trying to do is something like the below:

db1.completions.findAll({
  include: [
    {
      model: db2.CourseCatalog,
      where: { db1.completions.module_id = db2.CourseCatalog.courseid }
    }
  ],
  group: 'module_id',
  where: {...}
});

I know this line above doesn't work, but it's simply to show my intent. Is it even possible to achieve this?

@manojbharti

This comment has been minimized.

Copy link

commented Feb 24, 2019

I have one product table and another is stock of products table but i am getting this result by joining these both tables using "include", I want both table's specific attributes in one object how can I do that please help. Thanks in advance
"data": [
{
"id": 1,
"product_id": 1,
"product_code": "sun1200",
"stock_quantity": 1200,
"is_del": 0,
"created_by": 1,
"modified_by": null,
"createdAt": "2019-02-24T16:40:17.000Z",
"updatedAt": null,
"product": {
"id": 1,
"product_name": "Sun-Glass",
"product_code": "sun1200",
"mrp_price": 120000,
"product_img": "",
"discount": 2000,
"category": "eye-glass",
"subcategory": "eye-glass",
"model_no": "sunglass1222",
"tax": 20,
"created_by": 1,
"modified_by": null,
"createdAt": "2019-01-19T13:30:25.000Z",
"updatedAt": "2019-01-19T13:30:25.000Z"
}
}
]

@bobber205

This comment has been minimized.

Copy link

commented Mar 5, 2019

Does include support ordering the results like the find() queries do via

order: [['updatedAt', 'DESC']]
@Jadex1

This comment has been minimized.

Copy link

commented Apr 5, 2019

This has been very helpful. does include work on findByPk or findOne? I have a business and I always want to retrieve the business address, address is a separate table.

@patryk-wlaz

This comment has been minimized.

Copy link

commented May 15, 2019

@Jadex1 yeah, according to docs you can pass option object to findByPk, which I just did and it worked ;)

const ownerWithDoggo = await this.ownerEntity.findByPk<OwnerEntity>(ownerId, {
  include: [
    {
      model: DoggoEntity,
    },
  ],
});
@waldyrious

This comment has been minimized.

Copy link

commented Jun 17, 2019

@zcaceres I think this sentence needs a little reworking:

Eager loading is confusing because the 'include' that is uses has unfamiliar fields is set in an array rather than just an object.

It seems that it has two possible terminations:

  • has unfamiliar fields

  • is set in an array rather than just an object.

Did you mean to use only one of these? Or maybe combine them with an "and"?

Eager loading is confusing because the 'include' that is uses has unfamiliar fields, and it is set in an array rather than just an object.

@avtarnanrey

This comment has been minimized.

Copy link

commented Jun 27, 2019

@bprivateer to include more than one model with include, you would add them to the include array.
Let's say you had another association called Genre that you wanted to include with your Album query, it would look something like this:

Album.findAll({
  include: [
    {
      model: Artist,
      as: 'Singer',
    },
    {
      model: Genre
    }
]

To include all associations use include: [{all: true}]

Extra:

If your Genre model has a many-to-many association with model Album by a join table called albumgenre (i.e. the join table contains all the Album records associated with that specific Genre) and you didn't want to fetch all attributes from the join table albumgenre with your query. You can then pass through: { attributes: [] } with your include.

Album.findAll({
  include: [
    {
      model: Artist,
      as: 'Singer',
    },
    {
      model: Genre
      through: { attributes: [] }
    }
]

Thank you for your help. You should write your own blog post for most common associations queries.

@mnezmah

This comment has been minimized.

Copy link

commented Jul 17, 2019

If you have nested relations for example:

Group.belongsTo(Invite)
Invite.belongsTo(Issue)

then your query looks like this:

Issue.find({
    include: [
        {
            model: Invite,
            include: [Group]
        }
    ]
});
@bprivateer

This comment has been minimized.

Copy link

commented Jul 17, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.