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?
This question is related to
mysql
node.js
orm
sequelize.js
Model1.belongsTo(Model2, { as: 'alias' })
Model1.findAll({include: [{model: Model2 , as: 'alias' }]},{raw: true}).success(onSuccess).error(onError);
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:
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]});
Source: Stackoverflow.com