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

It is possible from version 2.1 of JPA included 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));