JPQL query: how to filter rows on a relationship?

Question

I'm new to JPA 2.0 and there are few things I don't understand.

I have a couple of tables:

    CUST table (for customers)
    --------------------------
    CUST_ID   (pk, integer)
    CUST_NAME (varchar)

and

    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)

The relationship is a simple "one to many" (every customer can place many orders).

Content of the tables:

    CUST_ID | CUST_NAME
    -------------------
     1      | elcaro
     2      | tfosorcim
     3      | elppa

and

    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

Here's how I annotated my classes:

    @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<Order> orders;

        // Default constructor, getters and setters (no annotations on these)
    }

and

    @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)
    }

Everything works just fine, the following JPQL query yields the results I expected:

`select c from Customer c`

it returns three objects of type Customer, each of which contains the orders that belong to that customer.

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:

    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'

and it would have returned the following result set:

    CUST_ID | CUST_NAME | ORD_ID | ORD_STATUS
    -----------------------------------------
     1      | elcaro    | 2      | N
     1      | elcaro    | 3      | N
     1      | elcaro    | 4      | N
     2      | tfosorcim | 11     | N

The following JPQL query, however, doesn't yield the expected results:

`select distinct c from Customer c join c.orders o where o.status = 'N'`

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.

I then tried out another JPQL query:

`select c, o from Customer c join c.orders o where o.status = 'N'`

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.

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.

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).

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.

Thanks in advance.

Best regards,

Stefano

Solution

JPA deals with objects, and an object has an identity and is the same no matter how it is queried. No matter what your where clause is, the Customer objects returned are still the same Customer objects and should have the same relationships. This is important for caching, object identity and consistency.

Your second query is probably the correct way to do what you want. It is possible to do what your are trying to do using an alias on a JOIN FETCH (as of EclipseLink 2.4), but not recommended.

See, http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#JOIN_FETCH