JPA / EclipseLink: Joined WHERE query does not give expected result

Question

Now that's very confusing... I have a JPA entity Order that references an entity User. The User can be either buyer or seller of the Order.

Because both buyer and seller can enter additional information for an order, I moved that to an extra entity OrderUserData. There might or might not be a corresponding OrderUserData object, but IF one exists, the user should only be able to see the entry they created (based on USER_ID) and not the one of the other party.

The entities look like this:

@Entity
@Table(name = "T_ORDER")
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "SELLER_ID")
    private User seller;

    @ManyToOne
    @JoinColumn(name = "BUYER_ID")
    private User buyer;

    @OneToMany(mappedBy = "order", fetch = FetchType.EAGER)
    private List<OrderUserData> userData = new ArrayList<>();

   //..
}

--

@Entity
@Table(name = "T_ORDER_USERDATA")
public class OrderUserData {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "ORDER_ID")
    private Order order;

    @ManyToOne
    @JoinColumn(name = "USER_ID")
    private User user;

    private String comment;
  //...
}

( User is not very exciting, just ID and basic name fields )

Now when I'm trying to select the appropriate data to display in the website, I have a problem:

String qry = "SELECT o FROM Order o LEFT JOIN o.userData ud "
            + " WHERE (o.seller.id = :userId OR o.buyer.id = :userId)"
            + " AND ( ud.user IS NULL OR ud.user.id = :userId )";
    TypedQuery<Order> query = em.createQuery(qry, Order.class);

    query.setParameter("userId", userId);

Let's say I execute this, setting userId to 2:

My Database looks like this:

ORDER
=====
ID    SELLER_ID    BUYER_ID
1     1            2
2     2            3
3     3            1

ORDER_USERDATA
===============
ID     ORDER_ID    USER_ID    COMMENT
1      1           1          Comment that only user 1 should see
2      1           2          Comment that only user 2 should see

But unlike you would expect, when executing the above query, both records are included in the userData list! It seems like JPA is executing two queries (despite the EAGER fetch) and ignoring the WHERE on the second one. Why is that? And what other solution than to loop through the userData list on Java level and kick out the entry that the appropriate user should not see?

Answer 1

There is no way to load OrderUserData objects inside an Order object using a query. Maybe you're confusing the ORM functionality, mapping rows in the database to Java objects, with the query functionality.
Mapping means 1-1 correspondence between rows and objects, hence Order objects always contain all OrderUserData objects for each OrderUserData row related to Order rows. The fetch type is just a loading strategy, determining at which time are the related objects fetched, as soon as the containing object is loaded (EAGER) or as soon as the contained objects are accessed (LAZY).
You can obtain your list issuing a query on OrderUserData objects with the proper filters and getting Order objects from each of them, i.e.

SELECT ud FROM OrderUserData ud WHERE (ud.order.seller.id = :userId OR ud.order.buyer.id = :userId) AND ( ud.user IS NULL OR ud.user.id = :userId )