SQL conversion with subquery in HQL selection

Question

I have the following SQL, I am having problems converting to HQL. NPE rushes - which, I think, has something to do with the SUM function. Also, I would like to sort by subselect alias - is this possible?

SQL (subtitle):

SELECT q.title, q.author_id, (SELECT IFNULL(SUM(IF(vote_up=true,1,-1)), 0) FROM vote WHERE question_id = q.id) AS votecount FROM question q ORDER BY votecount DESC 

HQL (does not work)

 SELECT q, (SELECT COALESCE(SUM(IF(v.voteUp=true,1,-1)), 0) FROM Vote v WHERE v.question = q) AS votecount FROM Question AS q LEFT JOIN q.author u LEFT JOIN u.blockedUsers ub WHERE q.dateCreated BETWEEN :week AND :now AND u.id NOT IN ( SELECT ub.blocked FROM UserBlock AS ub WHERE ub.blocker = :loggedInUser ) AND (u.blockedUsers IS EMPTY OR ub.blocked != :loggedInUser) ORDER BY votecount DESC 

Answer 1

Here is a working HQL if anyone is interested:

 SELECT q, (SELECT COALESCE(SUM(CASE v.voteUp WHEN true THEN 1 ELSE -1 END), 0) FROM Vote v WHERE v.question = q) AS votecount FROM Question AS q LEFT JOIN q.author u LEFT JOIN u.blockedUsers ub WHERE q.dateCreated BETWEEN :week AND :now AND u.id NOT IN ( SELECT ub.blocked FROM UserBlock AS ub WHERE ub.blocker =:loggedInUser ) AND (u.blockedUsers IS EMPTY OR ub.blocked !=:loggedInUser) ORDER BY col_1_0_ DESC 

Pay attention to ORDER BY col_1_0 _

There is an open problem with Hibernate - it parses aliases incorrectly and, since aliases are renamed to the request, an error will be generated. So col_1_0_ is basically a workaround - the name Hibernate is generated. See Question: http://opensource.atlassian.com/projects/hibernate/browse/HHH-892