Hibernate: group by in join

Question

I have the following entities in my database:

• Ticket
• Ticket Entry

A Ticket Entry is linked to a Ticket and has a column that references the Ticket ID. I want to retrieve tickets that are linked to only 1 Ticket Entry. I figured out what the SQL script should look like but I don't know how I should convert it to Hibernate code.

select * from
tickets ticket
 join
ticket_entries entry on ticket.id=entry.ticket_id
group by ticket.id
having count(*) = 1

Thanks in advance

Answer 1

You may try the following HQL:

select t.id
from tickets t join t.ticket_entries entry
group by t.id
having count(*) = 1

The major difference from your raw SQL query is that in HQL the join does not require an ON clause. Rather, the definitions of the entities define what the join relation is.

Note that select * in combination with group by is generally not allowed or at the very least not advisable. Instead, you should only be selecting the ticket id here or maybe some other aggregate.