Q:

Spring data jpa repository retrieve multiple counts

I have the following domain entities:
Conversation, which has a list of Messages, each of them has a set newFor Participants. For whom the message in a list has not been read yet.
I'm struggling with writing a repository interface method (or query) to retrieve a map of conversations having a number of unread messages for this participant.
Atm I use the following to retrieve just a list of conversations:
@Query("SELECT c from Conversation c JOIN c.participants p WHERE :participant IN p")
List<Conversation> findByParticipantsIn(Participant participant, Pageable pageable);

But I need to somehow update the query to be able to include the count of nested unread messages. Something like that:
@Query("SELECT c, count(..my problem is here..) from Conversation c LEFT JOIN c.participants p WHERE :participant IN p ")
List<Object[]> findByParticipantsIn(Participant participant, Pageable pageable);

Does anyone have any idea of how to put joins to count() or what else could I use here?
UPD
Entity fragments exposing the relationships:
public class Conversation {
...
    @OneToMany(cascade = CascadeType.ALL)
    private List<Message> messages = new ArrayList<>();
...
    @ManyToMany(fetch = FetchType.EAGER)
    private Set<Participant> participants = new LinkedHashSet<>();
...
}

public class Message {
...
    @ManyToMany(fetch = FetchType.EAGER)
    private Set<Participant> newFor = new HashSet<>();
...
}

Participant is not aware of Message and Conversation existence.

A:

Finally I could accomplish the goal of retrieving the data invoking a single repository method this way:
@Query("SELECT c, (SELECT COUNT(m) FROM Message m WHERE m.conversation = c AND :participant MEMBER OF m.newFor) " +
        "FROM Conversation c " +
        "WHERE :participant MEMBER OF c.participants " +
        "GROUP BY c, c.lastMessage.createdDate " +
        "ORDER BY c.lastMessage.createdDate DESC")
List<Object[]> findConversationsPerParticipant(Participant participant, Pageable pageable);

This query retrieves conversations for the given participant plus a count of unread messages in accordance with the described above data model.
Not sure if this approach is efficient enough but I believe that it might work faster than 2 separate calls to a remote DB.

