node.js - 如何在 Sequelize 中正确编写包含多个包含的复杂查询？

Question

我是 Sequelize ORM 的新手。我有表用户、角色和投票。

我想获得一个用户，他的角色(可能很少)和投票表中的平均评分作为评分字段。我遇到了 group by 问题。

const id = req.params.id;
user
.findOne({
  where: { id },
  attributes: [
    "id",
    "username",
    "email",
    "avatar",
    "createdAt",
  ],
  include: [
    { model: role, attributes: ["name"] },
    { model: vote, attributes: ["rate"] },
  ],
})
.then(user => {
  res.status(200).json({ user });
})
.catch(err => {
  console.log(err);
});

上面的代码得到下一个json

{
  "id": 2,
  "username": "John Connor",
  "email": "johny@gmail.com",
  "avatar": "/public/static/img/uploads/avatar.jpg",
  "createdAt": "2018-03-05T20:25:56.693Z",
  "role": {
    "name": "USER_ROLE"
  },
  "votes": [
    {
      "rate": 4
    },
    {
      "rate": 5
    },
    {
      "rate": 2
    }
  ]
}

我不知道如何正确执行复杂的查询并获得平均值
而不是获得所有字段率。你如何做这样的查询？
也许我应该单独做这件事还是有点不同？

Answer 1

干得好 :

user.findOne({
    where: { id },
    attributes: [
        "id",
        "username",
        "email",
        "avatar",
        "createdAt",
        [sequelize.fn('AVG', sequelize.col('votes.rate')) ,'average_vote'] // perform average function like this
    ],
    include: [
        { model: role, attributes: ["id","name"] },
        { model: vote, attributes: [] }, // remove all the attributes from vote model
    ],
    // group by by the rest 2 table's (change user. and role. as per table name or query)
    group : ['user.id','role.id']

})

Note :

1. Please read the comments in code

2. There might be table name issue so you need to check that , rest of the part will work 100% fine.