Finding all entries where a collection field contains any of the given items

Question

I have a Spring Boot application that contains an entity like this (some fields stripped for compactness):

@Entity
public class Message extends AbstractPersistable<Long> {
    @ManyToMany(targetEntity = Tag.class, fetch = FetchType.EAGER)
    private Set<Tag> tags;
    // getter and setter for tags here
}

Tag is just a simple entity with a name field.

Now, I have another Set<Tag> in my code obtained from the user. I want to find all Messages that have any of the tags in this set. For example, if we have the following messages:

ID    TAGS
1     1, 2, 3
2     2, 5, 7
3     2, 4, 7

Then the query should, given the set [3, 4] return messages 1 and 3.

I tried writing this repository:

public interface MessageRepository extends JpaRepository<Message, Long> {
    List<Message> findByTags(Set<Tag> tag);
}

I enabled query logging and my code produced a query, which I've cleaned up a bit here. The query produces no results in the cases I tried, and I have no idea what the scalar = array comparison is doing.

SELECT message.id AS id FROM message
LEFT OUTER JOIN message_tags ON message.id = message_tags.message_id
LEFT OUTER JOIN tag ON message_tags.tags_id = tag.id
WHERE tag.id = (1,2,3,4,5) -- this is the input set

As suggested by @AliDehghani I tried writing the method as findByTagsIn(Set<Tag> tag). This replaced the last = with in in the query.

I got results, but there was another problem: the results were repeated for each matching tag as one might guess from the query. For example, searching for [2, 7] with the example messages above would return message 1, message 2 twice and message 3 twice.

As far as I know adding some kind of GROUP BY clause might help here.

The predefined query method keywords don't seem to have any features related to this either, so I think I have to write my own using @Query or something else.

I can't seem to figure out a query that would work either, and I'm not very experienced in H2 so I don't really want to guess how one might work either.

I don't want to write a method that find all messages with a single tag, call it for each tag and combine the results, since that would be ugly and, given a lot of tags as input, very slow. How should I write my query method?

@AliDehghani That gets me results, but they are repeated for each matching tag. — PurkkaKoodari, Jan 28 '17 at 14:19
@AliDehghani I updated the question. Basically if a message matches more than one tag in the parameter list it returns the message again for each tag. — PurkkaKoodari, Jan 28 '17 at 14:23
@AliDehghani That worked. Post that as an answer and I'll accept. — PurkkaKoodari, Jan 28 '17 at 14:47

score 18 · Accepted Answer · answered Jan 28 '17 at 16:09

List findByTags(Set tag);

As you can see from the query log, this query method will only find messages that are tagged with all tags in the given tag parameter, which is not what you want.

In order to find messages that are tagged with either of those tags, you can use the following query method:

List<Message> findByTagsIn(Set<Tag> tag);

I got results, but there was another problem: the results were repeated for each matching tag as one might guess from the query.

In order to get rid of those repeated messages, you can fetch only distinct values:

List<Message> findDistinctByTagsIn(Set<Tag> tag);

Finding all entries where a collection field contains any of the given items

1 Answers1

Linked