Sequelize with join, group by, having, order by

Question

Sequelize with join, group by, having, order by

How this query can be written using ORM?

SELECT p.id, 
    p.name, 
    COUNT(c.id) counter 
FROM Posts p 
LEFT JOIN Comments c 
    ON c.post_id = p.id 
WHERE p.rating > 100 
GROUP BY p.id
HAVING counter > 10
ORDER BY counter DESC 
LIMIT 5

node.js
sequelize.js

Answer 1

Since there is aggregated column counter in your query I think there is no standard ORM method to fetch entities by this query. But Sequelize provide the way to run raw SQL expression and parse results in any model you need. Or even deeper you can parse response yourself creating as complex result as you need.

Here is related docs for this case: http://sequelizejs.com/docs/latest/usage#raw-queries

Answer 2

Try this:

Post.findAll({
  where: {rating: {gt: 100}},
  include: [Comments],
  having: ['COUNT(?) >= ?', '`comment`.`id`', 10]
})

Read my research by this question.

i use sequelize@2.0.0-dev9