javascript - 计算 Sequelize.js 中联接表之间多对多表中的关系

Question

我正在使用 sequelize.js 构建一个项目，其中包括一个标签表和一个故事表。它们具有多对多的关系，这是我在 Sequelize 中使用 StoryTag 的直通表创建的。到目前为止，这一切都完美无缺，但我想获得最流行标签的列表，例如它们在 StoryTag 表中关联的故事数量，并按使用此标签的故事数量对它们进行排序。

这是我正在尝试做的 MySQL 语法。这在 MySQL Workbench 中完美运行:

SELECT tagName, COUNT(StoryTag.TagId) 
FROM Tags 
LEFT JOIN StoryTag on Tags.id = StoryTag.TagId 
GROUP BY Tags.tagName ORDER BY COUNT(StoryTag.TagId) DESC;

这是在 sequelize.js 中起作用的。这是一个原始查询，并不理想，但由于它不处理任何敏感信息，所以不用担心，只是非常不优雅。

//DIRECT QUERY METHOD (TEST)
app.get("/api/directags", function (req, res) {
    db.sequelize.query("select tags.id, tags.TagName, COUNT(stories.id) as num_stories 
    from tags left join storytag on storytag.TagId = tags.id 
    left join stories on storytag.StoryId = stories.id 
    group by tags.id order by num_stories desc;", { 
        type: db.Sequelize.QueryTypes.SELECT
    }).then(function(result) {
        res.send(result);
    });
}); 

这输出

[
  {
    "id": 3,
    "TagName": "fiction",
    "num_stories": 3
  },
  {
    "id": 5,
    "TagName": "Nursery Rhyme",
    "num_stories": 2
  },
  ...
  {
    "id": 4,
    "TagName": "nonfiction",
    "num_stories": 0
  }
]

应该的。
不太有效的是:

//Sequelize count tags 
//Known issues: will not order by the count
//Includes a random 'storytag' many-to-many table row for some reason
app.get("/api/sequelizetags", function (req, res) {
    db.Tag.findAll({
        attributes: ["id","TagName"],
        include: [{
            model: db.Story, 
            attributes: [[db.sequelize.fn("COUNT", "stories.id"), "Count_Of_Stories"]],
            duplicating: false
        }],
        group: ["id"]
    }).then(function (dbExamples) {
        res.send(dbExamples);
    });
}); 

哪些输出:

[
    {
        "id": 1,
        "TagName": "horror",
        "Stories": [
            {
                "Count_Of_Stories": 1,
                "StoryTag": {
                    "createdAt": "2018-11-29T21:09:46.000Z",
                    "updatedAt": "2018-11-29T21:09:46.000Z",
                    "StoryId": 1,
                    "TagId": 1
                }
            }
        ]
    },
    {
        "id": 2,
        "TagName": "comedy",
        "Stories": []
    },
    {
        "id": 3,
        "TagName": "fiction",
        "Stories": [
            {
                "Count_Of_Stories": 3,
                "StoryTag": {
                    "createdAt": "2018-11-29T21:10:04.000Z",
                    "updatedAt": "2018-11-29T21:10:04.000Z",
                    "StoryId": 1,
                    "TagId": 3
                }
            }
        ]
    },
    {
        "id": 4,
        "TagName": "nonfiction",
        "Stories": []
    },
   ...
    {
        "id": 8,
        "TagName": "Drama",
        "Stories": [
            {
                "Count_Of_Stories": 1,
                "StoryTag": {
                    "createdAt": "2018-11-30T01:13:56.000Z",
                    "updatedAt": "2018-11-30T01:13:56.000Z",
                    "StoryId": 3,
                    "TagId": 8
                }
            }
        ]
    },
    {
        "id": 9,
        "TagName": "Tragedy",
        "Stories": []
    }
]

此文不分先后，埋藏故事数。这似乎是来自数据库的常见且频繁的请求，但我不知道如何使用 sequelize.js 正确执行此操作。

让我失望的资源:
Sequelize where on many-to-many join
Sequelize Many to Many Query Issue
How to query many-to-many relationship data in Sequelize
Select from many-to-many relationship sequelize
sequelize 的官方文档:http://docs.sequelizejs.com/manual/tutorial/
sequelize 的一些不太官方但更易读的文档:https://sequelize.readthedocs.io/en/v3/docs/querying/

Answer 1

以下是最终起作用的方法，以防其他人有此问题。我们还在包含故事中添加了一个位置，但这是可选的。此资源比官方 Sequelize 文档更容易理解:https://sequelize-guides.netlify.com/querying/
我还了解到，在使用 sequelize 时，熟悉 promises 真的很有帮助。

db.Tag.findAll({
        group: ["Tag.id"],
        includeIgnoreAttributes:false,
        include: [{
            model: db.Story,
            where: {
                isPublic: true
            }
        }],
        attributes: [
            "id",
            "TagName",
            [db.sequelize.fn("COUNT", db.sequelize.col("stories.id")), "num_stories"],
        ],
        order: [[db.sequelize.fn("COUNT", db.sequelize.col("stories.id")), "DESC"]]
    }).then(function(result){
        return result;
    });