通过集合中的精确匹配查找实体 [英] Find entity by exact matching in collection

问题描述

我有这样的实体:

@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;
}

是否可以通过完全匹配用户ID在Spring的存储库中找到对话实体?例如，我有以下实体:

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

因此，当我希望通过用户ID user-a和user-c常规IN子句找到对话时，如下所示:

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

将找到ID为1和3的对话，但我想找到完全匹配的内容，因此我的预期结果仅为对话3.

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

可能的解决方案是在存储库中使用常规IN子句，并在服务层中使用下一个过滤器集合，但我更喜欢直接从数据库返回所需实体的解决方案.至少可以在JPQL或本机sql中使用吗?谢谢.

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.

推荐答案

使用HAVING和CASE来计数匹配的userId并检查与搜索到的userIds计数是否相等.

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);

这篇关于通过集合中的精确匹配查找实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！