title: JPQL query: how to filter rows on a relationship?
body: <p>I'm new to JPA 2.0 and there are few things I don't understand.</p>
  
  <p>I have a couple of tables:</p>
  
  <pre><code>
  
      CUST table (for customers)
      --------------------------
      CUST_ID   (pk, integer)
      CUST_NAME (varchar)
  
  </code></pre>
  
  <p>and</p>
  
  <pre><code>
  
      ORD table (for orders)
      ----------------------
      ORD_ID     (pk, integer)
      ORD_STATUS (char) can be: 'N' for new, 'S' for shipped, 'D' for delivered
      CUST_ID    (fk, integer)
  
  </code></pre>
  
  <p>The relationship is a simple "one to many" (every customer can place many orders).</p>
  
  <p>Content of the tables:</p>
  
  <pre><code>
  
      CUST_ID | CUST_NAME
      -------------------
       1      | elcaro
       2      | tfosorcim
       3      | elppa
  
  </code></pre>
  
  <p>and</p>
  
  <pre><code>
  
      ORD_ID | ORD_STATUS | CUST_ID
      -----------------------------
       2     | N          | 1
       3     | N          | 1
       4     | N          | 1
       5     | S          | 1
       6     | S          | 1
       7     | D          | 1
       8     | D          | 1
       9     | D          | 1
       10    | D          | 2
       11    | N          | 2
       12    | S          | 3
       13    | S          | 3
  
  </code></pre>
  
  <p>Here's how I annotated my classes:</p>
  
  <pre><code>
  
      @Entity(name = "Customer")
      @Table(name = "CUST")
      public class Customer implements Serializable
      {
          private static final long serialVersionUID = 1L;
  
          @Id
          @Column(name = "CUST_ID")
          private Integer id;
  
          @Column(name = "CUST_NAME")
          private String name;
  
          @OneToMany(mappedBy = "customer")
          private List&lt;Order&gt; orders;
  
          // Default constructor, getters and setters (no annotations on these)
      }
  
  </code></pre>
  
  <p>and</p>
  
  <pre><code>
  
      @Entity(name = "Order")
      @Table(name = "ORD")
      public class Order implements Serializable
      {
          private static final long serialVersionUID = 1L;
  
          @Id
          @Column(name = "ORD_ID")
          private Integer id;
  
          @Column(name = "ORD_STATUS")
          private Character status;
  
          @ManyToOne
          @JoinColumns
          (
            {
              @JoinColumn(name = "CUST_ID", referencedColumnName = "CUST_ID")
            }
          )
          private Customer customer;
  
          // Default constructor, getters and setters (no annotations on these)
      }
  
  </code></pre>
  
  <p>Everything works just fine, the following JPQL query yields the results I expected:</p>
  
  <pre><code>`select c from Customer c`</code></pre>
  
  <p>it returns three objects of type Customer, each of which contains the orders that belong to that customer.</p>
  
  <p>But now, I want to extract the list of customers that have orders in status 'N', along with the associated orders (only the status 'N' orders, of course).
  Back in the good ol' days I would have written an SQL query like this:</p>
  
  <pre><code>
  
      select      c.cust_id,
                  c.cust_name,
                  o.ord_id,
                  o.ord_status
      from        cust c
      inner join  ord o on (o.cust_id = c.cust_id)
      where       o.ord_status = 'N'
  
  </code></pre>
  
  <p>and it would have returned the following result set:</p>
  
  <pre><code>
  
      CUST_ID | CUST_NAME | ORD_ID | ORD_STATUS
      -----------------------------------------
       1      | elcaro    | 2      | N
       1      | elcaro    | 3      | N
       1      | elcaro    | 4      | N
       2      | tfosorcim | 11     | N
  
  </code></pre>
  
  <p>The following JPQL query, however, doesn't yield the expected results:</p>
  
  <pre><code>`select distinct c from Customer c join c.orders o where o.status = 'N'`</code></pre>
  
  <p>it returns the correct set of customers (customer 'elppa' doesn't have any status 'N' order and is correctly excluded), but each customer contains the full set of orders, regardless of the status.
  It seems that the 'where' clause is only evaluated to determine which set of customers has to be extracted and then the persistence provider starts to navigate the relationship to extract the full set of orders.
  Thinking a little about it, I must admit that it makes sense.</p>
  
  <p>I then tried out another JPQL query:</p>
  
  <pre><code>`select c, o from Customer c join c.orders o where o.status = 'N'`</code></pre>
  
  <p>this JPQL query yields results that are similar to the ones produced by the previous SQL query: each result (4 results as expected) is a 2-object array, the first object is of type Customer and the second object is of type Order. But, again, the objects of type Customer contain the full set of related orders (as I expected, this time). Not to mention the fact that now the orders are not contained in the Customer objects, but are returned separately, just as in an SQL result set.</p>
  
  <p>Now the question is:
  Is it possible to write a JPQL query that filters out, not only the customers that don't have an order in status 'N', but the related orders (fetched during relationship navigation) that are not in status 'N' as well?
  What I'd like to be able to get is a 2-customer result where each customer contains only its status 'N' orders.</p>
  
  <p>I read the Java EE 6 Tutorial and one of the examples (the Order Application) has a schema that is similar to mine, but I couldn't find a query like this (in the downloaded source code).</p>
  
  <p>Although I think the above is standard behavior, I use an Oracle Weblogic 12c server (through its Eclipse adapter) and the persistence provider appears to be EclipseLink.</p>
  
  <p>Thanks in advance.</p>
  
  <p>Best regards,</p>
  
  <p>Stefano</p>