HQL 按表达式排序

Question

我有两个持久类，Comment 和 Vote 。一个Comment可以与许多Vote相关联，并且它们之间存在正常工作的@OneToMany - @ManyToOne关系。我想做的是按大多数赞成票对评论进行排序。如果1列Vote.up则Vote投赞成票，如果0列Vote.up则投反对票。我正在尝试找出它们的区别

到目前为止，这是我的总部，但它不起作用

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

有没有办法做到这一点？

Answer 1

HQL 不支持这样的语法，因此您必须为此使用本机查询：

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();

Answer 2

看看这个 sql 示例：

FROM Employee E WHERE E.id > 10 " +
         "ORDER BY E.firstName DESC, E.salary DESC 

它是 hql 句子的一个例子。
你确定你的句子有效吗？