Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 217
PurkkaKoodari Avatar asked Jan 28 '17 13:01

PurkkaKoodari


People also ask

How to check if an element is present in a collection?

The contains (Object element) of java.util.Collection interface is used to check whether the element ‘element’ exists in this collection. This method returns a boolean value depicting the presence of the element. If the element is present, it returns true, else it returns false. Attention reader! Don’t stop learning now.

What is collection contains () method in Java?

Collection contains () method in Java with Examples Last Updated : 29 Nov, 2018 The contains (Object element) of java.util.Collection interface is used to check whether the element ‘element’ exists in this collection. This method returns a boolean value depicting the presence of the element.

How do I check if a key/value pair exists in a collection?

Additionally, an optional $value can be specified that will be used to check if a given key/value pair exists within the collection. The contains method is incredibly versatile, and each use case will be highlighted below. The following demonstrates the contains method in its most basic usage:

What is the default value of the contains method?

The contains method defines two parameters: $key and $value. The $value parameter has a default value of null, however, the two method calls are not the same: Even though the two examples return true, the second example is functionally not correct. When explicitly passing null as the second argument, the contains method will always return true:


1 Answers

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);
like image 155
Ali Dehghani Avatar answered Oct 20 '22 07:10

Ali Dehghani