Sequelize: Fetch all elements from one side of a many-to-many, with aggregated count of the other side?

(I realize this is more of a SQL question than node, but this is the only sub suitable for Sequelize questions, as far as I know.)In a current database-driven project, I have a couple of different pairs of tables with many-to-many (M:N) relationships. I am using Sequelize as my ORM and DB-layer. What I need to be able to do, is select the items on one side of the relationship with a count of the linked items on the other side. I.e., one of my pairings is Authors and References (books, articles, papers, etc.). A Reference can have multiple authors, and an Author can be on multiple references. I want to be able to select in the Authors table and have a count of the linked References records (for argument's sake, I call this extra field refcount). Currently, I'm using include to get the linked references and then post-processing my results to replace the list of references with a field that is just the count:const fetchSingleAuthorWithRefCount = async (id) => { let author = await Author.findByPk(id, { include: [ { model: Reference, as: "References", attributes: ["id"] }, ], }).catch((error) => { throw new Error(error); }); if (author) { author = author.get(); author.refcount = author.References.length; delete author.References; } return author; }; But this isn't the right way to do it; besides the inefficiency of post-processing the results, I can't sort on refcount within the query itself. And I know that this is possible within SQL, but even if I figure out the proper SQL syntax, I'm not sure how to translate that into Sequelize.-Randy

Submitted September 19, 2020 at 10:33PM by rjray