title: Finding all entries where a collection field contains any of the given items
body: <p>I have a Spring Boot application that contains an entity like this (some fields stripped for compactness):</p>
  
  <pre class="lang-java prettyprint-override"><code>@Entity
  public class Message extends AbstractPersistable&lt;Long&gt; {
      @ManyToMany(targetEntity = Tag.class, fetch = FetchType.EAGER)
      private Set&lt;Tag&gt; tags;
      // getter and setter for tags here
  }
  </code></pre>
  
  <p><code>Tag</code> is just a simple entity with a name field.</p>
  
  <hr>
  
  <p>Now, I have another <code>Set&lt;Tag&gt;</code> in my code obtained from the user. I want to find all <code>Message</code>s that have any of the tags in this set. For example, if we have the following messages:</p>
  
  <pre class="lang-none prettyprint-override"><code>ID    TAGS
  1     1, 2, 3
  2     2, 5, 7
  3     2, 4, 7
  </code></pre>
  
  <p>Then the query should, given the set <code>[3, 4]</code> return messages 1 and 3.</p>
  
  <hr>
  
  <p>I tried writing this repository:</p>
  
  <pre class="lang-java prettyprint-override"><code>public interface MessageRepository extends JpaRepository&lt;Message, Long&gt; {
      List&lt;Message&gt; findByTags(Set&lt;Tag&gt; tag);
  }
  </code></pre>
  
  <p>I enabled <a href="https://stackoverflow.com/questions/39535232/how-to-log-query-in-pagingandsortingrepository">query logging</a> 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 <code>scalar = array</code> comparison is doing.</p>
  
  <pre class="lang-sql prettyprint-override"><code>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
  </code></pre>
  
  <hr>
  
  <p>As suggested by @AliDehghani I tried writing the method as <code>findByTagsIn(Set&lt;Tag&gt; tag)</code>. This replaced the last <code>=</code> with <code>in</code> in the query.</p>
  
  <p>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 <code>[2, 7]</code> with the example messages above would return message 1, message 2 twice and message 3 twice.</p>
  
  <p>As far as I know adding some kind of <code>GROUP BY</code> clause might help here.</p>
  
  <hr>
  
  <p>The <a href="http://docs.spring.io/spring-data/jpa/docs/1.6.0.RELEASE/reference/html/jpa.repositories.html#d0e1599" rel="noreferrer">predefined query method keywords</a> don't seem to have any features related to this either, so I think I have to write my own using <code>@Query</code> or something else.</p>
  
  <p>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.</p>
  
  <p>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?</p>