How to retrieve the ordered list of best articles having a minimum number of votes by using HQL ?

Question

I have a Vote domain class from my grails application containing properties like article_id and note

I want to HQL query the Vote domain class in order to retrieve the 5 best rated articles having at least 10 votes.

I tried :

SELECT v.article_id, avg(v.note), count(*) FROM vote v where count(*) >= 10  group by v.article_id order by avg(v.note) desc limit 5;

But unfortunately the insertion of where count(*) >= 10 throws an error.

How can I do that in a simple way?

Thank you for your help.

Answer 1

doesn't the following work ?

SELECT v.article_id, avg(v.note), count(*) as count 
FROM vote v 
where count >= 10  
group by v.article_id 
order by avg(v.note) desc limit 5;

Later edit: Try this

SELECT v.article_id, avg(v.note) 
from vote v 
group by v.article_id 
having count(*) >= 10
order by avg(v.note) desc limit 5;

Answer 2

Not being able to test it, I'm reluctant to try to guess how the final query would look like, but HAVING COUNT(*) >= 10 might be something to look at.

http://www.sqlcommands.net/sql+having/

Answer 3

Try that:

SELECT v.article_id, avg(v.note),
FROM vote v 
GROUP BY v.article_id
HAVING count(*) >= 10
OREDER BY avg(v.note) desc limit 5;