QueryDSL get ERROR: column must appear in the GROUP BY clause or be used in an aggregate function

Viewed 179

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
0 Answers
Related