Spring DTO Projection query doesn't return all results due to inner join instead of left join

Question

I am trying to get a simple DTO projection on the entity User which has a list of orders. The projection should contain only the user firstName, lastName and the number of Orders in the linked table.

User class:

@Entity
@Table(name = "user")
public class User {

    @Column(name = "firstName")
    private String firstName;

    @Column(name = "lastName")
    private String lastName;

    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    private Set<Order> orders;

    // many other fields here

}

Order class:

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

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

    // many other fields here

}

And then I have the DTO object:

public class UserDetailOrderCountDto {

    private String firstName;
    private String lastName;
    private int orderCount;

    public UserDetailOrderCountDto(String firstName, String lastName, int orderCount) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.orderCount = orderCount;
    }

    // getters, setters, ...

}

And finally the repository with the query:

public interface UserRepository extends JpaRepository<User, Long> {

    @Query("select new a.b.c.UserDetailOrderCountDto(u.firstName, u.lastName, size(u.orders)) from User u group by u.firstName, u.lastName")
    List<UserDetailOrderCountDto> findUsersAndOrderCount();

}

The DB contains 2 orders for 2 users. There are many users without any order (which I still want to receive with the orderCount as 0). The query in the repository returns 2 DTOs for 2 users with 1 order each (correct) but users without orders are skipped (because it's not left-joined). The query generated by Hibernate is as follows:

select user0_.firstName as col_0_0_, user0_.lastName as col_1_0_, count(orders1_.user_id) as col_2_0_ from user user0_, orders orders1_ where user0_.id=orders1_.user_id group by user0_.firstName , user0_.lastName

How can I force Hibernate to give me all the users (aka left join, but without native queries if possible)? Or any other approach to get the solution I'd like? Any help appreciated. Thank you.

Update 1: If I try to force Hibernate to join tables with FetchMode.JOIN, it still uses inner join.

@OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
@Fetch(FetchMode.JOIN)
private Set<Order> orders;

The query then looks like this:

select user0_.firstName as col_0_0_, user0_.lastName as col_1_0_, count(orders1_.user_id) as col_2_0_ from user user0_ cross join orders orders1_ where user0_.id=orders1_.user_id group by user0_.firstName , user0_.lastName

Answer 1

JPA Query

You can indicate left join following next approach:

  @Query("SELECT new com.your.package.dto.UserDetailOrderCountDto(u.firstName, u.lastName, COUNT(o)) "
          + "FROM User u LEFT JOIN u.orders o group by u.firstName, u.lastName")
  List<UserDetailOrderCountDto> findUsersAndOrderCount();

Just make sure you change your class attribute orderCount to long:

public class UserDetailOrderCountDto {
  private String firstName;
  private String lastName;
  private long orderCount;

  public UserDetail() {
  }

  public UserDetail(String firstName, String lastName, long orderCount) {
    this.firstName = firstName;
    this.lastName = lastName;
    this.orderCount = orderCount;
  }

  // Getters and setters
}

Notice, this works with below configuration on your User class:

@OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
private Set<Order> orders;

Using Native Queries

You can use a native query instead, so you can define the left-join:

@Query(value = "select u.first_name as firstName, u.last_name as lastName, count(o.id) as orderCount from user u left join orders o on u.id = o.user_id  group by u.first_name, u.last_name;"
       , nativeQuery = true)
  List<UserDetailOrderCountDto> findUsersAndOrderCount();

You just need to make sure the resulting column name match the property name of your bean.

Also, on newest versions of spring you do not need to create the bean, you can instead define an interface and spring create a bean that inherit from interface:

public interface UserDetailOrderCountDto {
  public String getFirstName();
  public String getLastName();
  public int getOrderCount();
}