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.

Well you are using "IN" which is basically a "subset" operator. Does equals ("=") work? I can't test this right now — Benjamin Maurer, Jun 20 '20 at 15:58
@BenjaminMaurer I tried it today and it gives me error on application startup "Operator CONTAINING on usersIds requires a scalar argument" — Denis Stephanov, Jun 21 '20 at 17:11

Eklavya · Accepted Answer · 2020-06-30T19:43:58.427

3

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

edited Jun 30 '20 at 19:43

answered Jun 30 '20 at 19:16

Eklavya

17,618
4
28
57

Thank you it works, but I was hoping that spring has better support for this use case. – Denis Stephanov Jun 30 '20 at 23:27
@DenisStephanov That is the way you can do using SQL, so for JPQL and I think spring-data-jpa try to support most common uses cases. Exact child match is not the common case in sql maybe that's why they don't facilitate any keyword for this. – Eklavya Jul 01 '20 at 05:29
the else statement should return 0 instead of -1 ? I think something is wrong with the solution – bLaXjack Jun 14 '23 at 11:09
@bLaXjack It doesn't matter actually, original task is to match the count or not. – Eklavya Jun 14 '23 at 11:53

score 0 · Answer 2 · answered Jun 30 '20 at 14:54

Ensure that the user ids in the user_ids column are kept alphabetically ordered. So for example when user b enters the conversation with id 3, the user_ids column that was 'user-a, user-c' becomes 'user-a, user-b, user-c'.

Next make sure that when you want to retrieve a converation based on an exact match of participants, the user ids in the argument to your query is alphabetically ordered as well. The select can then be

select c from Converation c WHERE c.userIds = :userIds

Now only exact matches will be found.

Same answer as I give to Lavish .. it gives me error – Denis Stephanov Jun 30 '20 at 23:17 — Denis Stephanov, Jun 30 '20 at 23:17

Lavish · Answer 3 · 2020-07-01T04:39:44.283

0

You can write a custom query in your Repository class as mentioned below:

@Repository
public interface YourRepository extends JpaRepository<ConversationModel, Integer> {

 @Query(nativeQuery = true, value = "select c from Converation c WHERE c.userId = :userId ORDER BY userId DESC LIMIT 1")
    Optional<ConversationModel> findByUserId(@Param("userId") String userId);

}

Now exact matching userId will be getting returned from the database layer. Make it optional and check if the returned value is not null while using in the service layer.

For fetching multiple records you'll have to use IN in the Query as mentioned below

 @Query("select c from Converation c WHERE c.userIds IN :userIds")
    List<ConversationModel> findByUserIds(@Param("userIds") List<String> userIds);

Hopefully, this will resolve your issue.

edited Jul 01 '20 at 04:39

answered Jun 30 '20 at 17:59

Lavish

642
7
12

1

Did you tried it? Because it gives me following error: org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [cccccccc-000000000001] did not match expected type [java.util.Collection (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value [cccccccc-000000000001] did not match expected type [java.util.Collection (n/a)] where cccccccc-000000000001 is one of items in passed collection into repository – Denis Stephanov Jun 30 '20 at 23:15
Yes I tried similar query. Oh got it, I think since there are multiple entries that your db is returning the method 1 that I mentioned might not be working because it's return type is Optional. You might need to limit the result from DB layer then for that you can use something like ` @Query(nativeQuery = true, value = "select c from Converation c WHERE c.userId = :userId ORDER BY userId DESC LIMIT 1") ` – Lavish Jul 01 '20 at 04:36
If you check my question there is collection of user ids, not one on conversation entity – Denis Stephanov Jul 01 '20 at 09:36
In that case the IN query should have worked. I'm using similar query in my project. – Lavish Jul 01 '20 at 10:40

Find entity by exact matching in collection

3 Answers3

Linked