title: JPA 2 criteria API with @ElementCollection and like
body: <p>I have the following entities</p>
  
  <pre><code>class A {
    @Id
    Integer id;
  
    String name;
  
    @ElementCollection
    @JoinTable(name = "othername", joinColumns = @JoinColumn(name = "id_a"))
    @Column(name = "name")
    List&lt;String&gt; othernames;
  }
  </code></pre>
  
  <p>I'm trying to use the criteria API to find instances of A using an infix search on the name or the other names. Basically something like this SQL-Query:</p>
  
  <pre><code>SELECT * FROM A
      WHERE name like '%something%'
      OR id IN (SELECT id_a FROM othername WHERE name LIKE '%something%');
  </code></pre>
  
  <p>The following code is meant to do the right thing:</p>
  
  <pre><code>String search = "%something%";
  Subquery&lt;Integer&gt; subquery = c.subquery(Integer.class);
  Root&lt;A&gt; subqueryFrom = subquery.from(A.class);
  subquery.select(subqueryFrom.get(A_.id));
  subquery.where(builder.like(subqueryFrom.get(A_.othernames), search));
  
  query.where(builder.or(
      builder.like(from.get(A_.name), search),
      from.get(A_.id).in(subquery)
  ));
  </code></pre>
  
  <p><em>I'm using a subquery here so I don't get duplicate results (e.g. someone searches for "a" and the name of an object contains "a" and the othernames contains an "a" as well).</em></p>
  
  <p>Now this code does not work, because subqueryFrom.get(A.othernames) returns an Expression&lt;List&lt;String>> while builder.like only accepts Expression&lt;String>.</p>
  
  <p>I actually think that my approach is completely wrong, but currently I can't think of any other solution.</p>