Dworza Dworza - 2 years ago 283
SQL Question

JPA query with condition for OneToMany hibernate relationship

I have a

spring boot
application, where I use
to access the data in a database. Now I'd need to perform a more complex query, but I can't figure out, how to make it.

In my Repository interface, I have a working query, that selects all chatThreads of a user, by his username:

@Query("SELECT chatThread FROM ChatThread chatThread WHERE (chatThread.userId) = (:id)")
public List<ChatThread> find(@Param("id") String id);

But how to adjust it now, to select only messages, which were not read yet?
There exists a
hibernate OneToMany relationship
and the scheme looks like that:
enter image description here

Answer Source

Based on your comment and assuming you have the OneToMany relationship on ChatThread as List<Message> messages, your JPQL query should look like this:

SELECT chatThread FROM ChatThread chatThread WHERE (chatThread.userId) = (:id) AND NOT EXISTS (SELECT message FROM chatThread.messages message WHERE message.read = TRUE)

You might have to change the condition for message.read if you don't use boolean to integer conversion, e.g. message.read = 1.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download