Hibernate error: unexpected AST node

Question

I need to sort topics by latest post. Could someone please help me with this hibernate query:

unexpected AST node: query 
[SELECT t 
FROM Topic t 
ORDER BY 
(SELECT MAX(p.createdOn) FROM Post p WHERE p.topic.id = t.id) 
DESC]

What is the issue here?

Answer 1

I think that this JPQL query should work

select t.id, t.description, max(p.createdOn) as maxCreationTime 
from Topic t inner join t.posts p
group by t.id, t.description
order by maxCreationTime

If you want to include topics that have not related posts.

select t.id, t.description, max(p.createdOn) as maxCreationTime
from Topic t left join t.posts p
group by t.id, t.description
order by maxCreationTime

You have to specify the selected fields from Topic in order to make the aggregation function max work. Hope this helps.

Answer 2

SELECT topic FROM Post ORDER BY createdOn