title: Spring boot JPA: search entities by field that represent list of embedded objects
body: <p>I have an entity <code>Order</code> with List of options inside, like this: </p>
  
  <pre><code>@Entity
  @Table(name = "orders")
  public class OrderEntity extends AuditableEntity {
      private Long passengerId;
      private OrderType type;
      private OrderStatus status;
      @ElementCollection()
      @CollectionTable(name = "options", joinColumns = @JoinColumn(name = "order_id"))
      private List&lt;OrderOptionEntity&gt; options = new ArrayList&lt;&gt;(0);
  ...
  </code></pre>
  
  <p>And I want to find all orders, matches specified list of options.  I'm using <code>JpaRepository&lt;OrderEntity, Long&gt;</code> for CRUD operations. Unfortunately, when I add method <code>findByOptions</code>, like this: </p>
  
  <pre><code>public interface OrderRepository extends JpaRepository&lt;OrderEntity, Long&gt; {
      List&lt;OrderEntity&gt; findAllByOptions(List&lt;OrderOptionEntity&gt; options);
  }
  </code></pre>
  
  <p>In tests it throws </p>
  
  <blockquote>
    <p>SqlNode's text did not reference expected number of columns;</p>
  </blockquote>
  
  <p>So now I just do <code>findAll()</code> and filter all orders manually. Is there any more elegant way for obtain entities, matching by all elements of list inside it? </p>
  
  <p><strong>UPDATE</strong>: 
  When I run </p>
  
  <pre><code>@Query("SELECT ord FROM OrderEntity ord WHERE :options MEMBER OF ord.options")
      List&lt;OrderEntity&gt; findAllByOptions(@Param(value = "options") List&lt;OrderOptionEntity&gt; options);
  </code></pre>
  
  <p>It works fine, but only if  ord.options and options in query has size 1, if more - it throws </p>
  
  <blockquote>
    <p>o.h.engine.jdbc.spi.SqlExceptionHelper   : Invalid argument in JDBC
    call: parameter index out of range: 3</p>
  </blockquote>
  
  <p>Generated SQL is </p>
  
  <pre><code> /* SELECT
          ord 
      FROM
          OrderEntity ord 
      WHERE
          :options MEMBER OF ord.options */ select
              orderentit0_.id as id1_3_,
              orderentit0_.version as version2_3_,
              orderentit0_.create_time as create_t3_3_,
              orderentit0_.update_time as update_t4_3_,
              orderentit0_.comment as comment5_3_,
              orderentit0_.distance_to_order as distance6_3_,
              orderentit0_.passenger_id as passenge7_3_,
              orderentit0_.price as price8_3_,
              orderentit0_.route_distance as route_di9_3_,
              orderentit0_.status as status10_3_,
              orderentit0_.type as type11_3_ 
          from
              orders orderentit0_ 
          where
              (
                  ? , ?
              ) in (
                  select
                      options1_.key,
                      options1_.value 
                  from
                      options options1_ 
                  where
                      orderentit0_.id=options1_.order_id
              )
  </code></pre>
  
  <p>All what I want - get all Orders, containing some subset of options.  </p>