How to make a CriteriaBuilder join with a custom "on" condition?

Question

I want make a query where I join 2 tables, using the CriteriaBuilder. In MySQL the query I'm trying to make would look like this:

SELECT * FROM order LEFT JOIN item ON order.id = item.order_id AND item.type_id = 1 

I want to get all orders and if they have an item of type #1, I want to join with this item. However, if no item of type #1 is found, I still want to get the order. I can't figure out how to make this with the CriteriaBuilder. All I know how to make is:

CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Order> cq = cb.createQuery(Order.class); Root<Order> order = cq.from(Order.class); Join<Order, Item> item = order.join(Order_.itemList, JoinType.LEFT); Join<Item, Type> type = order.join(Item_.type, JoinType.LEFT); cq.select(order); cq.where(cb.equal(type.get(Type_.id), 1)); 

This query is broke, since it results in something like this in MySQL:

SELECT * FROM order LEFT JOIN item ON order.id = item.order_id WHERE item.type_id = 1 

The result will only contain orders with items of type #1. Orders without are excluded. How can I use the CriteriaBuilder to create a query like in the first example?

Answer 1

I think this is the same problem as posed in this question. It looks like it is not possible in CriteriaBuilder. It is possible in Hibernate Criteria API, but that probably won't help you.

JPA Criteria API: Multiple condition on LEFT JOIN

Answer 2

It is possible starting from the version 2.1 of JPA using the on method Join<Z, X> on(Predicate... restrictions);

Here is how:

Root<Order> order = cq.from(Order.class); Join<Order, Item> item = order.join(Order_.itemList, JoinType.LEFT); item.on(cb.equal(item.get(Item_.type), 1));