Translate this MySQL query to JPQL

Question

I am not sure how would this MySQL query look in JPQL. DETAILS:

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

EDIT: Post table look:

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    |                |
+---------------+----------------+------+-----+---------+----------------+

Comment table look:

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    |                |
+-----------------+--------------+------+-----+---------+----------------+

This is command in mysql, terminal.

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

I tried this, but its not working:

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

I have used native SQL for getting result I need. So, its not really an answer to my question, but I will post what I did, and what works (with plain SQL). Because, its not realized with JPQL, there isn't named query in an entity class, but native query has been used (inside the method of DAO object).

So, this is method content:

public List<Post> getMostCommentedPosts(){
    Query q = em.createNativeQuery("select * from post order by "
            + "(select count(post_id) from comment where comment.post_id=post.post_id) desc", Post.class);
    List<Post> resultList = (List<Post>) q.getResultList();

    if (resultList.isEmpty())
        return null;
    else
        return resultList;
}