GroupBy association field fetchCount() does not work

[yor1g]

QBusWasteGenerate busWasteGenerate = QBusWasteGenerate.busWasteGenerate;
blazeJPAQueryFactory.from(busWasteGenerate)
.select(
        busWasteGenerate.company.code,
        busWasteGenerate.wasteCode
)
.groupBy(
        busWasteGenerate.company.code,
        busWasteGenerate.wasteCode
)
.fetchCount();

Hibernate: select count(distinct bwg1_0.waste_code) from bus_waste_generate bwg1_0

QBusWasteGenerate busWasteGenerate = QBusWasteGenerate.busWasteGenerate;
QCompany company = QCompany.company;

blazeJPAQueryFactory.from(busWasteGenerate)
        .leftJoin(company).on(company.id.eq(busWasteGenerate.company.id))
        .select(
                company.code,
                busWasteGenerate.wasteCode
        )
        .groupBy(
                company.code,
                busWasteGenerate.wasteCode
        )
        .fetchCount();

Hibernate: select count(distinct c1_0.code,bwg1_0.waste_code) from bus_waste_generate bwg1_0 join company c1_0 on c1_0.id=bwg1_0.company_id

[beikov]

Can you please clarify what it is that you think doesn't work?

[yor1g]

Can you please clarify what it is that you think doesn't work?

@Entity
@Getter
@Setter
public class BusWasteGenerate {

    @Id
    private Long id;

    private String wasteCode;

    private String wasteDescription;

    @ManyToOne(fetch = FetchType.LAZY)
    private Company company;
}

@Entity
@Getter
@Setter
public class Company {

    @Id
    private Long id;

    private String code;

    private String name;
}

        var count = criteriaBuilderFactory.create(em, BusWasteGenerate.class)
                .from(BusWasteGenerate.class, "busWasteGenerate")
                .select("busWasteGenerate.wasteCode")
                .select("busWasteGenerate.company.code")
                .groupBy("busWasteGenerate.wasteCode", "busWasteGenerate.company.code")
                .getCountQuery()
                .getSingleResult();

Hibernate: select count(distinct bwg1_0.waste_code) from bus_waste_generate bwg1_0

        var count2 = criteriaBuilderFactory.create(em, BusWasteGenerate.class)
                .from(BusWasteGenerate.class, "busWasteGenerate")
                .leftJoinOn(Company.class, "company").on("company.id").eq("busWasteGenerate.company.id").end()
                .select("busWasteGenerate.wasteCode")
                .select("company.code")
                .groupBy("busWasteGenerate.wasteCode", "company.code")
                .getCountQuery()
                .getSingleResult();

Hibernate: select count(distinct bwg1_0.waste_code,c1_0.code) from bus_waste_generate bwg1_0 join company c1_0 on c1_0.id=bwg1_0.company_id

Left join must be used to ensure correct querying.

[beikov]

In your real model, did you mark wasteCode as unique? Because if so, then grouping by the wasteCode already leads to "unique results" i.e. there is no need to left join a *-to-one association.