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

问题描述

我正在使用 sequelize.js 构建一个项目，其中包括一个标签表和一个故事表。他们有一个多对多的关系，我在续集中创建了一个 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/

标签： javascriptmysqlnode.jssequelize.js

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

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

问题描述

解决方案

推荐阅读