mysql - 将此 MySQL 查询转换为 JPQL

Question

我不确定这个 MySQL 查询在 JPQL 中会是什么样子。详情:

select title
from post
order by (
  select count(postId)
  from comment
  where comment.postId=post.id
) desc;

编辑:帖子表外观:

mysql> desc post;
+---------------+----------------+------+-----+---------+----------------+
| Field         | Type           | Null | Key | Default | Extra          |
+---------------+----------------+------+-----+---------+----------------+
| post_id       | int(11)        | NO   | PRI | NULL    | auto_increment |
| post_content  | varchar(50000) | NO   |     | NULL    |                |
| post_date     | datetime       | NO   |     | NULL    |                |
| post_summary  | varchar(1000)  | YES  |     | NULL    |                |
| post_title    | varchar(300)   | NO   |     | NULL    |                |
| post_visitors | int(11)        | NO   |     | NULL    |                |
| user_id       | int(11)        | NO   | MUL | NULL    |                |
| category_id   | int(11)        | NO   | MUL | NULL    |                |
+---------------+----------------+------+-----+---------+----------------+

评论表查看:

mysql> desc comment;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| comment_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| comment_content | varchar(600) | NO   |     | NULL    |                |
| comment_date    | datetime     | NO   |     | NULL    |                |
| comment_title   | varchar(300) | NO   |     | NULL    |                |
| user_id         | int(11)      | NO   | MUL | NULL    |                |
| post_id         | int(11)      | NO   | MUL | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

这是 mysql 终端中的命令。

mysql> select post_title from post order by (select count(post_id) from comment where comment.post_id=post.post_id) desc;

我尝试过这个，但它不起作用:

SELECT p FROM Post p ORDER BY 
(SELECT c COUNT(c.getPost().getId()) 
from Comment c 
where c.getPost().getId()=p.getId()) 
desc

Answer 1

非工作方法的主要问题是从 JPQL 查询调用 Java 类的方法 - 这预计不会工作。

假设实体的重要属性大致如下:

@Entity
public class Post {
    @Id int id;
    @OneToMany (mappedBy = "post") List<Comment> comments;
    //...
}

@Entity
public class Comment {
    @Id int id;
    @ManyToOne Post post;
    //...
}

那么下面的 Hibernate+MySQL 组合就足够了:

SELECT p 
FROM Post p 
ORDER BY SIZE(p.comments) DESC

JPA 2.0 规范需要更复杂的查询，因为 ORDER BY 子句中可以使用的内容相当有限:

1. A state_field_path_expression that evaluates to an orderable state field of an entity or embeddable class abstract schema type designated in the SELECT clause by one of the follow- ing: • a general_identification_variable • a single_valued_object_path_expression
2. A state_field_path_expression that evaluates to the same state field of the same entity or embeddable abstract schema type as a state_field_path_expression in the SELECT clause
3. A result_variable that refers to an orderable item in the SELECT clause for which the same result_variable has been specified. This may be the result of an aggregate_expression, a scalar_expression, or a state_field_path_expression in the SELECT clause.

以下查询也应该适用于其他实现。当然，附加变量是结果的一部分有点乏味:

SELECT p, SIZE(p.comments) as ord  
FROM Post p 
ORDER BY ord DESC