如何在 Node.js 上使用 Sequelize 进行连接查询 [英] How to make join queries using Sequelize on Node.js

问题描述

我正在使用 sequelize ORM；一切都很棒而且很干净，但是当我将它与 join 查询一起使用时遇到了问题.我有两个模型:用户和帖子.

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);
        });

我的问题是如何更改代码以使用 ORM 样式而不是 SQL 查询?

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

推荐答案

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

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

哪个会给你

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

除此之外，您会注意到它执行 JOIN 而不是从两个表中进行选择，但结果应该是相同的

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

进一步阅读:

• 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

这篇关于如何在 Node.js 上使用 Sequelize 进行连接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！