Find entity by exact matching in collection

Question

I have entity like this:

@Getter
@Setter
@Entity
public class Conversation extends AbstractEntity{

    @ElementCollection
    @Column(name = "user_id", nullable = false)
    @CollectionTable(name = "conversation_user", joinColumns = @JoinColumn(name = "conversation_id", nullable = false))
    private List<String> usersIds;
}

Is possible to find conversation entity by spring's repository by exact matching of user ids? For instance I have these entities:

 id | user_ids
------------------------------------------
 1  | user-a, user-b, user-c
 2  | user-a, user-b
 3  | user-a, user-c

So when I will be want found conversation by user ids user-a and user-c regular IN clause like this:

SELECT c FROM Conversation c WHERE c.userIds IN :userIds

will found conversations with id 1 and 3, but I want find exact match, so my expected result is only conversation 3.

Possible solution is use regular IN clause in repository, and next filter collection in service layer but I prefer solution which returns required entity directly from database. Is it possible in JPQL or native sql at least? Thank you.

Answer 1

Use HAVING with CASE to count matched userId and check equal with searched userIds count.

@Query(value = "SELECT c FROM Conversation c LEFT JOIN c.usersIds cu GROUP BY c "
           + "HAVING SUM(CASE WHEN cu IN (:userIds) THEN 1 ELSE -1 END) = :userIdsCount")
List<Conversation> findByUserIds(@Param("userIds") List<String> userIds,
                                 @Param("userIdsCount") Integer userIdsCount);