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 Message
s 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?
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.
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.
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:
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:
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With