How to make join queries using Sequelize on Node.js

Question

I am using sequelize ORM; everything is great and clean, but I had a problem when I use it with join queries. I have two models: users and posts.

var User = db.seq.define('User',{
    username: { type: db.Sequelize.STRING},
    email: { type: db.Sequelize.STRING},
    password: { type: db.Sequelize.STRING},
    sex : { type: db.Sequelize.INTEGER},
    day_birth: { type: db.Sequelize.INTEGER},
    month_birth: { type: db.Sequelize.INTEGER},
    year_birth: { type: db.Sequelize.INTEGER}

});

User.sync().success(function(){
    console.log("table created")
}).error(function(error){
    console.log(err);
})


var Post = db.seq.define("Post",{
    body: { type: db.Sequelize.TEXT },
    user_id: { type: db.Sequelize.INTEGER},
    likes: { type: db.Sequelize.INTEGER, defaultValue: 0 },

});

Post.sync().success(function(){
    console.log("table created")
}).error(function(error){
    console.log(err);
})

I want a query that respond with a post with the info of user that made it. In the raw query, I get this:

db.seq.query('SELECT * FROM posts, users WHERE posts.user_id = users.id ').success(function(rows){
            res.json(rows);
        });

My question is how can I change the code to use the ORM style instead of the SQL query?

Solution 1

User.hasMany(Post, {foreignKey: 'user_id'})
Post.belongsTo(User, {foreignKey: 'user_id'})

Post.find({ where: { ...}, include: [User]})

Which will give you

SELECT
  `posts`.*,
  `users`.`username` AS `users.username`, `users`.`email` AS `users.email`,
  `users`.`password` AS `users.password`, `users`.`sex` AS `users.sex`,
  `users`.`day_birth` AS `users.day_birth`,
  `users`.`month_birth` AS `users.month_birth`,
  `users`.`year_birth` AS `users.year_birth`, `users`.`id` AS `users.id`,
  `users`.`createdAt` AS `users.createdAt`,
  `users`.`updatedAt` AS `users.updatedAt`
FROM `posts`
  LEFT OUTER JOIN `users` AS `users` ON `users`.`id` = `posts`.`user_id`;

The query above might look a bit complicated compared to what you posted, but what it does is basically just aliasing all columns of the users table to make sure they are placed into the correct model when returned and not mixed up with the posts model

Other than that you'll notice that it does a JOIN instead of selecting from two tables, but the result should be the same

Further reading:

• http://docs.sequelizejs.com/en/latest/docs/associations/#one-to-one-associations
• http://docs.sequelizejs.com/en/latest/docs/associations/#one-to-many-associations
• http://docs.sequelizejs.com/en/latest/docs/models-usage/#eager-loading

Solution 2

While the accepted answer isn't technically wrong, it doesn't answer the original question nor the follow up question in the comments, which was what I came here looking for. But I figured it out, so here goes.

If you want to find all Posts that have Users (and only the ones that have users) where the SQL would look like this:

SELECT * FROM posts INNER JOIN users ON posts.user_id = users.id

Which is semantically the same thing as the OP's original SQL:

SELECT * FROM posts, users WHERE posts.user_id = users.id

then this is what you want:

Posts.findAll({
  include: [{
    model: User,
    required: true
   }]
}).then(posts => {
  /* ... */
});

Setting required to true is the key to producing an inner join. If you want a left outer join (where you get all Posts, regardless of whether there's a user linked) then change required to false, or leave it off since that's the default:

Posts.findAll({
  include: [{
    model: User,
//  required: false
   }]
}).then(posts => {
  /* ... */
});

If you want to find all Posts belonging to users whose birth year is in 1984, you'd want:

Posts.findAll({
  include: [{
    model: User,
    where: {year_birth: 1984}
   }]
}).then(posts => {
  /* ... */
});

Note that required is true by default as soon as you add a where clause in.

If you want all Posts, regardless of whether there's a user attached but if there is a user then only the ones born in 1984, then add the required field back in:

Posts.findAll({
  include: [{
    model: User,
    where: {year_birth: 1984}
    required: false,
   }]
}).then(posts => {
  /* ... */
});

If you want all Posts where the name is "Sunshine" and only if it belongs to a user that was born in 1984, you'd do this:

Posts.findAll({
  where: {name: "Sunshine"},
  include: [{
    model: User,
    where: {year_birth: 1984}
   }]
}).then(posts => {
  /* ... */
});

If you want all Posts where the name is "Sunshine" and only if it belongs to a user that was born in the same year that matches the post_year attribute on the post, you'd do this:

Posts.findAll({
  where: {name: "Sunshine"},
  include: [{
    model: User,
    where: ["year_birth = post_year"]
   }]
}).then(posts => {
  /* ... */
});

I know, it doesn't make sense that somebody would make a post the year they were born, but it's just an example - go with it. :)

I figured this out (mostly) from this doc:

• http://docs.sequelizejs.com/en/latest/docs/models-usage/#eager-loading

Solution 3

Model1.belongsTo(Model2, { as: 'alias' })

Model1.findAll({include: [{model: Model2  , as: 'alias'  }]},{raw: true}).success(onSuccess).error(onError);

Solution 4

In my case i did following thing. In the UserMaster userId is PK and in UserAccess userId is FK of UserMaster

UserAccess.belongsTo(UserMaster,{foreignKey: 'userId'});
UserMaster.hasMany(UserAccess,{foreignKey : 'userId'});
var userData = await UserMaster.findAll({include: [UserAccess]});

View more solutions

Comments

[Ciro Santilli OurBigBook.com]

Related: stackoverflow.com/questions/22958683/…

[clemlatz]

What if I want to join only Users who are born in 1984 ? In SQL I would do : SELECT * FROM posts JOIN users ON users.id = posts.user_id WHERE users.year_birth = 1984

[Jan Aagaard Meier]

@Iwazaru The answer would be too long to fit in a comment, please open a new question

[Manwal]

All links are dead not :-(

[theptrk]

Did that question ever get posted in an answered question?

[antew]

Do we need both of them or one is enough : User.hasMany(Post, {foreignKey: 'user_id'}) Post.belongsTo(User, {foreignKey: 'user_id'})

[Ryan Shillington]

@theptrk I know this is an old post, but I answered both the original question and this extra question in my answer below.

[Ryan Shillington]

@antew When you call User.hasMany(Post) you add methods / attributes to the User object, and when you call Post.belongsTo(User) you add methods to the Post class. If you're always calling from one direction (ex. User.getPosts()) then you don't need to add anything to the Post object. But it's nice to have the methods on both sides.

[theptrk]

@antew thanks for the response, i was never able to find the link for the question regarding SELECT * FROM posts JOIN users ON users.id = posts.user_id WHERE users.year_birth = 1984 but I think i figured it out by using through and a where object

[antew]

@Ryan, thanks for the clarification. However it's not clear for me, if you meant foreign keys in the database, or methods / attributes in the Sequelize object. Thanks.

[Ryan Shillington]

@theptrk I gave the answer for that exact query in my answer below (I don't see any reason to add a new question).

[Ryan Shillington]

@antew I meant methods / attributes on the Sequelize object.

[ThisGuyCantEven]

To join using include on a where just do include[model:db.User,where:{birthyear:1984}}]. you can filter attributes, tooinclude[model:db.User,where:{birthyear:1984},attributes:[‌​"username"]}]. This wuld be equivalent to FROM posts JOIN users.usernname on user.id=posts.user_id WHERE users.birthyear=1984

[Neo]

Thank you very much, this was useful

[Abdul Ahmad]

yea, great answer

[hanzichi]

so what represents posts.user_id = users.id in your sequelize? thanks

[Ryan Shillington]

@hanzichi That the posts.user_id column matches the users.id column is set up in your sequelize model definition.

[Giorgio Andretti]

This answer is so exhaustive it should be linked in Sequelize's doc

[Andrew Rayan]

Great piece of tutorial which i have been looking for.Thanks a lot

[Ali SadeghipourKorabaslo]

Hi, Is below code block need to define on every query User.hasMany(Post, {foreignKey: 'user_id'}) Post.belongsTo(User, {foreignKey: 'user_id'}) I hope to find a way to generate models by sequelize-auto with foreign keys definition.

[Prathamesh More]

Where should I put this code? User.hasMany(Post, {foreignKey: 'user_id'}) Post.belongsTo(User, {foreignKey: 'user_id'});

[Solid_Metal]

required false still gave me inner join for some reason

[Ryan Shillington]

Maybe you spelled required wrong or put it in the wrong place?

[Mahir Altınkaya]

Thank you. Best answer still.

[Jason G]

what if you only want 1 column from users? example, username but not, id, password, email and so on

[Ryan Shillington]

@JasonG You want the "attributes" key. Search for "attributes" in this doc: sequelize.org/master/manual/model-querying-basics.html

[Rajesh Kumar]

@Iwazaru Just add { where: { '$users.birthyear$': 1984 } in your model query to get expected result.

[Mohammad Zaid Pathan]

This helped me SomeTable.hasOne(User, { sourceKey: "UserId-In-SomeTable", foreignKey: "UserId-In-User-Table" });

[Dinuka Salwathura]

This works, Thanks!