CODEWITHSUNDEEP
spring-data-jpahqljpqlspring-repositories
Denis Stephanov
Denis Stephanov

Reputation: 5241

Find entity by exact matching in collection

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.

Upvotes: 7

Views: 2717

Answers (3)

Lavish
Lavish

Reputation: 720

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.

Upvotes: 0

Eklavya
Eklavya

Reputation: 18430

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

Upvotes: 3

Jan Peter
Jan Peter

Reputation: 390

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.

Upvotes: 0

Related Questions