Using QueryDSL, I want to select Customer along with number of their Contact that belong to them.
Customer.java
@Entity
public class Customer {
@Id
private long id;
...
@OneToMany(mappedBy = "createdBy", fetch = FetchType.LAZY)
private List<Contact> contacts;
}
Contact.java
@Entity
public class Contact {
@Id
private long id;
...
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "created_by")
private Customer createdBy;
}
CustomerResponse.java
public class CustomerResponse {
private Long id;
...
private int totalContact;
}
The code I use to query data:
QCustomer customer = QCustomer.customer;
QBean<CustomerResponse> customerRes = Projections.bean(
CustomerResponse.class,
customer.id,
...
customer.contacts.size().as("totalContact")
);
JPAQuery<CustomerResponse> query = new JPAQuery<>(entityManager)
.select(customerRes)
.from(customer);
return queryPredicate.fetch(query);
But I get this error when running above code:
Hibernate:
select
customer0_.id as col_0_0_,
count(contacts8_.created_by) as col_1_0_
from
customer customer0_
join
contact contacts1_
where
customer0_.id=contacts1_.created_by
order by
customer0_.id asc limit ?
o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42803
o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: column "customer0_.id" must appear in the GROUP BY clause or be used in an aggregate function