MySQL 如何在 Node.js 上使用 Sequelize 进行连接查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20460270/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make join queries using Sequelize on Node.js
提问by Jose Sosa
I am using sequelize ORM; everything is great and clean, but I had a problem when I use it with joinqueries.
I have two models: users and posts.
我正在使用 sequelize ORM；一切都很棒而且很干净，但是当我将它用于join查询时遇到了问题。我有两个模型：用户和帖子。
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?
我的问题是如何更改代码以使用 ORM 样式而不是 SQL 查询？
回答by Ryan Shillington
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:
如果您想查找所有拥有用户的帖子（并且只有拥有用户的帖子），SQL 如下所示：
SELECT * FROM posts INNER JOIN users ON posts.user_id = users.id
Which is semantically the same thing as the OP's original SQL:
这在语义上与 OP 的原始 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:
将 required 设置为 true 是产生内部联接的关键。如果您想要一个左外连接（您可以在其中获取所有帖子，无论是否有用户链接）然后将 required 更改为 false，或者将其关闭，因为这是默认设置：
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:
如果您想查找属于出生年份为 1984 年的用户的所有帖子，您需要：
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.
请注意，只要在其中添加 where 子句，默认情况下 required 就为 true。
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:
如果您想要所有帖子，无论是否附加了用户，但如果有用户，则只有 1984 年出生的用户，然后将必填字段添加回：
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:
如果您想要名称为“Sunshine”的所有帖子，并且仅当它属于 1984 年出生的用户时，您可以这样做：
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:
如果您想要名称为“Sunshine”的所有帖子，并且仅当它属于与帖子上的 post_year 属性匹配的同一年出生的用户时，您可以这样做：
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:
我从这个文档中（大部分）发现了这一点：
回答by Jan Aagaard Meier
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
除此之外，您会注意到它执行 JOIN 而不是从两个表中进行选择，但结果应该是相同的
Further reading:
进一步阅读：
回答by Raja JLIDI
Model1.belongsTo(Model2, { as: 'alias' })
Model1.findAll({include: [{model: Model2 , as: 'alias' }]},{raw: true}).success(onSuccess).error(onError);
回答by Renish Gotecha
In my case i did following thing. In the UserMaster userIdis PK and in UserAccess userIdis FK of UserMaster
就我而言，我做了以下事情。在 UserMaster 中，userId是 PK，在 UserAccess 中，userId是 UserMaster 的 FK
UserAccess.belongsTo(UserMaster,{foreignKey: 'userId'});
UserMaster.hasMany(UserAccess,{foreignKey : 'userId'});
var userData = await UserMaster.findAll({include: [UserAccess]});
