HQL order by expression

Question

I have two persistent classes, Comment and Vote. A Comment can be associated with many Votes and there is a @OneToMany - @ManyToOne relationship between them that's working correctly. What I'm trying to do is to sort the comments by most upvotes. A Vote is an upvote if Vote.up column is 1 and downvote if Vote.up is 0. I'm trying to work out their difference

So far this is my HQL but it's not working

select c from Comment c
order by (
    (select count(*) from c.votes v where v.up = 1) 
    - (select count(*) from c.votes v where v.up = 0)
) desc

Is there a way to do this?

Answer 1

HQL doesn't support such a syntax, so you'll have to use a native query for this:

List<Comment> comments = (List<Comment>) session.createSQLQuery(
        "select * " +
        "from Comment " +
        "where id in (   " +
        "    select comment_id " +
        "    from (     " +
        "        select        " +
        "            c.id as comment_id,        " +
        "            SUM(CASE WHEN v.up=1 THEN 1 ELSE -1 END) AS vote_count     " +
        "        from Comment c     " +
        "        left join Votes v on c.id = v.comment_id     " +
        "        group by comment_id     " +
        "        order by vote_count desc   " +
        "    ) c_v " +
        ") c_id"
).addEntity(Comment.class).list();