javascript - 如何在 Sequelize 中获取模型的所有用户名

Question

我正在使用 Express 和 Javascript 开发带有 Docker 的网络应用程序。我正在使用 三层架构 并使用 Sequelize 使用 Mysql 发送查询。我有 博客文章 作为与 帐户 表有关系的资源。这是关系的样子:

const Account = sequelize.define('accounts', {
    personId: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    username: {
        type: Sequelize.STRING(50),
        allowNull: false,
        unique: true
    },
    email: {
        type: Sequelize.STRING(50),
        allowNull: false,
        unique: true
    },
    userPassword: Sequelize.TEXT
}, {
    timestamps: false
})

const Blogpost = sequelize.define('blogposts', {
    blogId: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    title: Sequelize.TEXT,
    content: Sequelize.TEXT,
    posted: Sequelize.TEXT,
    imageFile: Sequelize.TEXT
}, {
    timestamps: false
})
Blogpost.belongsTo(Account, {foreignKey: "userId", foreignKeyConstraint: true})

这是我在 表示层 中检索所有博客文章的方式:

 router.get("/", function(request, response){

    blogManager.getAllBlogposts(function(errors, blogposts){
        if(errors.includes("databaseError")){
            response.send("<h1>Something went wrong!</h1>")
        }
        else if(errors.length > 0){
            const model = {
                errors
            }
            console.log("blogpostsModel:", { model })
            response.render("blogposts.hbs", { model })
        }else{
            const model = {
                blogposts
            }
            response.render("blogposts.hbs", { model })
        }
    })
})

这就是 模型 的样子:

blogposts {
    dataValues: {
        blogId: 2,
        title: 'fsdhkjfsdahflhs',
        content: 'fhjkdsfkbmngfhyuxgc',
        posted: '275760-07-09',
        imageFile: 'module-6.jpg',
        userId: 1
    },
 _previousDataValues: {
      blogId: 2,
      title: 'fsdhkjfsdahflhs',
      content: 'fhjkdsfkbmngfhyuxgc',
      posted: '275760-07-09',
      imageFile: 'module-6.jpg',
      userId: 1
  },

  blogposts {
      dataValues: {
          blogId: 3,
          title: 'fjhhkjfsa',
          content: 'gfhjdsakdasdsa',
          posted: '8989-08-09',
          imageFile: 'module-6.jpg',
          userId: 2
  },
 _previousDataValues: {
     blogId: 3,
     title: 'fjhhkjfsa',
     content: 'gfhjdsakdasdsa',
     posted: '8989-08-09',
     imageFile: 'module-6.jpg',
     userId: 2
  },

现在我想用用户 ID 检索用户名。我有一个可以通过 userid 检索用户名的工作函数，但我不知道如何使用它从 userid 中获取所有用户名。任何想法如何解决这个问题？

提前致谢!

Answer 1

您可以在查询 include 时使用 blogposts 选项，如下所示

Blogpost.findAll({
    include: [{
        model:Account,
    }]
});

它将使用连接查询来获取 account 但如果你想使用单独的查询，那么你也可以做这样的事情

router.get("/", function(request, response){

    blogManager.getAllBlogposts(function(errors, blogposts){
        if(errors.includes("databaseError")){
            response.send("<h1>Something went wrong!</h1>")
        }
        else if(errors.length > 0){
            const model = {
                errors
            }
            console.log("blogpostsModel:", { model })
            response.render("blogposts.hbs", { model })
        }else{
            const userIds = blogposts.map(blogpost => 
                                            blogpost.userId);
            // query the Account with the userIds;
            Account.find({ where : { userId : userIds}})
                   .then((accounts) => {
                      const blogpostsWithUserNames = 
                              blogposts.map(blogpost => {
                                 const findAccount = 
                                    accounts.filter(acc => 
                                     acc.userId === blogpost.userId);
                                 return { 
                                    ...blogpost, 
                                    userName : findAccount.username,
                                 }
                              })
                      const model = { 
                           blogposts : blogpostsWithUserNames
                      }
                      response.render("blogposts.hbs", 
                                   { model })
                    });
        }
    })
})