JPA (Hibernate) / QueryDSL left join with condition doesn't work

Question

I have two tables - user and booking. Each user may have many bookings (one-to-many relationship).

user:                          booking:

    id | name    |               id | country    | user_id | price   |
    -------------|               ------------------------------------|
     1 | Alice   |                1 | Italy      | 1       | 2000    |
     2 | Bob     |                2 | France     | 1       | 2500    |
                                  3 | Spain      | 1       | 3000    |

I want to select all users and all bookings where booking's price is greater than 2000 using Query DSL. If a user doesn't have any bookings or bookings don't match the condition I still want to select this user.

First, let's have a look at how it would look like using a simple SQL left join query:

SELECT u.*, b.* FROM user u LEFT JOIN booking b ON u.id = b.user_id AND b.price > 2000

The above query should provide the following result:

    id | name    | id    | country    | user_id | price   |
    -------------|----------------------------------------|
     1 | Alice   | 2     | France     | 1       | 2500    |
     1 | Alice   | 3     | Spain      | 1       | 3000    |
     2 | Bob     | null  | null       | null    | null    |

Now I want to do it using JPA with Query DSL

JPA-related stuff:

@Entity
public class User {

    @Id
    private Long id;
    private String name;

    @OneToMany(cascade = ALL, fetch = EAGER, orphanRemoval = true, mappedBy = "user")
    private List<Booking> bookings;

    // getters and setters
}

@Entity
public class Booking {

    @Id
    private Long id;
    private String name;
    private Integer price;

    @ManyToOne(fetch = LAZY)
    @JoinColumn(name = "user_id")
    private User user;

    // getters and setters
}

Query DSL:

public List<User> getUsersAndBookings() {
    QUser user = QUser.user;
    QBooking booking = QBooking.booking;

    JPAQuery<User> jpaQuery = new JPAQuery(entityManager);

    List<User> result = jpaQuery.from(user).leftJoin(user.bookings, booking).on(booking.price.gt(2000)).fetchJoin().fetch();

    return result;
}

In fact, this code is not working and I get the following exception:

org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause not allowed on fetched associations; use filters [select user from com.example.demo.entity.User user left join fetch user.bookings as booking with booking.price > ?1]

The problem is that the condition clause is specified in on method - on(booking.price.gt(2000)).

After some research I found that this condition should be specified in where method and should look like this:

List<User> result = jpaQuery.from(user).leftJoin(user.bookings, booking).where(booking.price.gt(2000)).fetchJoin().fetch();

This works, but not how I would expect it to work, since it doesn't return ALL users, it returns only one user (Alice), which has some bookings, matching the condition clause. Basically, it just filters the merged table (result table after left join operation) and that's not what I'm looking for.

I want to retrieve all users, and if there are no any bookings for a specific user, then just have null instead of booking list for this user.

Please help, been struggling for hours without any success.

---

Versions used:

• Spring Boot 2.0.2
• Spring Data JPA 2.0.7
• Hibernate 5.2.16.Final
• QueryDSL 4.1.4

Answer 1

You can use isNull expression in where clause to get the rows that have null values.

Your query should be like this:

jpaQuery.from(user)
    .leftJoin(user.bookings, booking)
    .fetchJoin()
    .where(booking.price.gt(2000).or(booking.id.isNull())).fetch();

Hibernate produced query:

select
user0_.id as id1_1_0_,
bookings1_.id as id1_0_1_,
user0_.name as name2_1_0_,
bookings1_.country as country2_0_1_,
bookings1_.price as price3_0_1_,
bookings1_.user_id as user_id4_0_1_,
bookings1_.user_id as user_id4_0_0__,
bookings1_.id as id1_0_0__
from
user user0_
left outer join
booking bookings1_
on user0_.id=bookings1_.user_id
where
bookings1_.id is null
or bookings1_.price>?

Answer 2

It seems there is no JPA way for this. But I got it fixed in Hibernate way, using Filters org.hibernate.annotations.Filter.

@Entity
@FilterDef(name = "anyName", parameters = {
    @ParamDef(name = "price", type = "integer")
})
public class User {    
    @Id
    private Long id;
    private String name;

    @OneToMany(cascade = ALL, fetch = EAGER, orphanRemoval = true, mappedBy = "user")
    @Filter(name = "anyName", condition = "price > :inputPrice")
    private List<Booking> bookings;
}

Before querying the db, you must enable this filter.

Session session = enityManager.unwrap(Session.class);
session.enableFilter("anyName").setParameter("inputPrice", 2000);

// fetch using hql or criteria; but don't use booking.price.gt(2000) or similar condition there

session.disableFilter("anyName");

Now the result will have a User even if all of his booking prices are below 2000 and bookings list will be empty as expected.

NOTE: The word price in condition should be exactly same as the db column name; not as the model property name.