I am building a project using sequelize.js that includes a Tags table and a Stories table. They have a many to many relationship, which I created in sequelize with a through table of StoryTag. This all works perfectly so far, but I want to get a list of most popluar tags, as in how many stories they are associated with in the StoryTag table, and order them by the number of stories that use this tag.

This is the MySQL syntax of what I am trying to do. This works perfectly in 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;

This is what works in sequelize.js. It's a raw query, which is not ideal, but since this doesn't handle any sensitive information, it's not a huge worry, just very inelegant.

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

This outputs

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

As it should.
What doesn't quite work is:

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

Which outputs:

[
    {
        "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": "edy",
        "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": []
    }
]

This is not in order, and the count of stories is buried. This seems like the sort of thing that would be a mon and frequent request from a database, but I am at a loss of how to do this correctly with sequelize.js.

Resources that have failed me:
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
The official documentation for sequelize: /
Some less official and more readable documentation for sequelize: /

I am building a project using sequelize.js that includes a Tags table and a Stories table. They have a many to many relationship, which I created in sequelize with a through table of StoryTag. This all works perfectly so far, but I want to get a list of most popluar tags, as in how many stories they are associated with in the StoryTag table, and order them by the number of stories that use this tag.

This is the MySQL syntax of what I am trying to do. This works perfectly in 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;

This is what works in sequelize.js. It's a raw query, which is not ideal, but since this doesn't handle any sensitive information, it's not a huge worry, just very inelegant.

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

This outputs

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

As it should.
What doesn't quite work is:

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

Which outputs:

[
    {
        "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": "edy",
        "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": []
    }
]

This is not in order, and the count of stories is buried. This seems like the sort of thing that would be a mon and frequent request from a database, but I am at a loss of how to do this correctly with sequelize.js.

Resources that have failed me:
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
The official documentation for sequelize: http://docs.sequelizejs./manual/tutorial/
Some less official and more readable documentation for sequelize: https://sequelize.readthedocs.io/en/v3/docs/querying/

• javascript
• mysql
• node.js
• sequelize.js

3 Answers 3

Here's what finally worked, in case anyone else has this question. We also added a where to the include Story, but that's optional. This resource is easier to understand than the official sequelize docs: https://sequelize-guideslify./querying/
I also learned that being familiar with promises is really helpful when working with sequelize.

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

1. Please, use the same name if you mean the same thing (num_stories - Count_Of_Stories, etc.).
2. For ordering use order option.
3. Include count in top level attributes for get it on top level of instance.
4. I can't find include[].duplicating option in doc.

Your case:

db.Tag.findAll({
    attributes: [
        "id",
        "TagName",
        [db.sequelize.fn("COUNT", "stories.id"), "Count_Of_Stories"]
    ],
    include: [{
        model: db.Story,
        attributes: [],
        duplicating: false
    }],
    group: ["id"],
    order: [
        [db.sequelize.literal("`Count_Of_Stories`"), "DESC"]
    ]
});

Use through: {attributes: []} in options