Query DSL with blaze-persistence is generating an extra joining

[Mukit09]

Description

We used to use query DSL in our codes to build dynamic queries. As, some methods got deprecated since 5.0.0, from their documentation I have known about blaze-persistence.

So my current query-dsl code with blaze-persistence is:

private Predicate getPredicate() {
        BooleanBuilder booleanBuilder = new BooleanBuilder();

        QPublisherInvoicing qPublisherInvoicing = QPublisherInvoicing.publisherInvoicing;
        QPublisher qPublisher = QPublisher.publisher;
        QCompanyInvoiceDataPublisher qCompanyInvoiceDataPublisher = QCompanyInvoiceDataPublisher.companyInvoiceDataPublisher;

        booleanBuilder.and(qPublisherInvoicing.deletedAt.isNull());

        if (publisher != null) { // publisher is a field variable of the class
            JPQLQuery<Long> publisherIds = JPAExpressions.select(qPublisher.id).from(qPublisher).where(
                qPublisher.name.contains(publisher)
                    .or(qPublisher.id.stringValue().contains(publisher))
            );

            booleanBuilder.and(
                qCompanyInvoiceDataPublisher.companyInvoiceDataPublisherPk.publisher.id.in(publisherIds)
            );
        }
    
        return booleanBuilder.getValue();
}    

public Page<PublisherInvoicing> findAllPublisherInvoices(Pageable pageRequest) {
        Predicate predicate = getPredicate();

        QPublisherInvoicing publisherInvoicing = QPublisherInvoicing.publisherInvoicing;
        QCompanyInvoiceDataPublisher companyInvoiceDataPublisher = QCompanyInvoiceDataPublisher.companyInvoiceDataPublisher;

        BooleanBuilder booleanBuilder = new BooleanBuilder();

        if (predicate != null)
            booleanBuilder.and(predicate);

        OrderSpecifier<?> orderBySpecifier = getOrderById(pageRequest);
        PagedList<PublisherInvoicing> publisherInvoicingPagedList = new BlazeJPAQueryFactory(entityManager, criteriaBuilderFactory)
            .selectFrom(publisherInvoicing)
            .leftJoin(companyInvoiceDataPublisher)
            .on(publisherInvoicing.id.eq(companyInvoiceDataPublisher.companyInvoiceDataPublisherPk.publisherInvoicing.id))
            .where(booleanBuilder)
            .orderBy(orderBySpecifier)
            .fetchPage((int) pageRequest.getOffset(), pageRequest.getPageSize());

        long totalElements = publisherInvoicingPagedList.getTotalSize();

        return new PageImpl<>(publisherInvoicingPagedList, pageRequest, totalElements);
    }

And blaze-persistence configuration is:

@Bean
@Scope(ConfigurableBeanFactory.SCOPE_SINGLETON)
@Lazy(false)
public CriteriaBuilderFactory createAdminCriteriaBuilderFactory() {
    CriteriaBuilderConfiguration config = Criteria.getDefault();
    config.setProperty("com.blazebit.persistence.inline_count_query", "false");

    return config.createCriteriaBuilderFactory(adminEntityManagerFactory);
}

Expected behavior

Earlier the code was like this:

public Page<PublisherInvoicing> findAllPublisherInvoices(Pageable pageRequest) {
        Predicate predicate = getPredicate();

        QPublisherInvoicing publisherInvoicing = QPublisherInvoicing.publisherInvoicing;
        QCompanyInvoiceDataPublisher companyInvoiceDataPublisher = QCompanyInvoiceDataPublisher.companyInvoiceDataPublisher;

        BooleanBuilder booleanBuilder = new BooleanBuilder();

        if (predicate != null)
            booleanBuilder.and(predicate);

        JPAQuery<PublisherInvoicing> jpaQuery = new JPAQueryFactory(entityManager)
            .selectFrom(publisherInvoicing).distinct()
            .leftJoin(companyInvoiceDataPublisher)
            .on(publisherInvoicing.id.eq(companyInvoiceDataPublisher.companyInvoiceDataPublisherPk.publisherInvoicing.id))
            .where(booleanBuilder);

        long totalElements = jpaQuery.fetchCount();

        if (pageRequest != null) {
            jpaQuery.offset(pageRequest.getOffset());
            jpaQuery.limit(pageRequest.getPageSize());
            setOrderById(jpaQuery, pageRequest);
        }

        List<PublisherInvoicing> requiredPublisherInvoicing = jpaQuery.fetch();

        if (pageRequest != null) {
            return new PageImpl<>(requiredPublisherInvoicing, pageRequest, totalElements);
        } else {
            return new PageImpl<>(requiredPublisherInvoicing);
        }
    }

And this generates SQL with same request this:

select distinct p1_0.id,p1_0.account_number,p1_0.bic,p1_0.city,p1_0.company_name,p1_0.country,p1_0.currency_id,p1_0.deleted_at,p1_0.iban,
                p1_0.invoice_type,p1_0.netsuite_vendor_id,p1_0.postal_code,p1_0.street,p1_0.term,p1_0.vat_number
from publisher_invoice_data p1_0 left join company_invoice_data_publisher c1_0 on p1_0.id=c1_0.invoice_id
where p1_0.deleted_at is null and c1_0.publisher_id in(
select p4_0.id from publisher p4_0 where p4_0.name like ? escape '!' or cast(p4_0.id as char)
                                                                            like ? escape '!') order by p1_0.id desc limit 0,10

My expectation is after using blaze-persistence, the generated query should also be like this.

Actual behavior

And it generates a query like this with the code given in the description:

select p1_0.id,p1_0.account_number,p1_0.bic,p1_0.city,p1_0.company_name,p1_0.country,p1_0.currency_id,p1_0.deleted_at,p1_0.iban,p1_0.invoice_type,
       p1_0.netsuite_vendor_id,p1_0.postal_code,p1_0.street,p1_0.term,p1_0.vat_number
from publisher_invoice_data p1_0 left join company_invoice_data_publisher c1_0 on (p1_0.id=c1_0.invoice_id)
where p1_0.id in
      ((select * from ((select p3_0.id from publisher_invoice_data p3_0 left join company_invoice_data_publisher c2_0 on (p3_0.id=c2_0.invoice_id)
            where p3_0.deleted_at is null and c2_0.publisher_id in
                (select p6_0.id from publisher p6_0 where p6_0.name like ? escape '!' or cast(p6_0.id as char) like ? escape '!')
            group by p3_0.id order by p3_0.id desc limit 10)) tmp
    ))
order by p1_0.id desc

Conclusion

Blaze-persistence is doing an extra joining here. So I'm interested to know if I'm doing anything wrong here. Or is there any option to stop that extra joining? Can I create the query just like queryDsl's JPAQueryFactory used to do?

Environment

Version: 1.6.11
JPA-Provider: Hibernate
DBMS: Maria DB
Application Server: Spring Boot 3.1.7

[jwgmeligmeyling]

Can you try .leftJoin(companyInvoiceDataPublisher, companyInvoiceDataPublisher)?

This explicitly binds the join to the alias companyInvoiceDataPublisher which hopefully triggers the criteria renderer to reuse the alias from the outer query.

[Mukit09]

@jwgmeligmeyling, No luck! Still generating same query:

select p1_0.id,p1_0.account_number,p1_0.bic,p1_0.city,p1_0.company_name,p1_0.country,p1_0.currency_id,p1_0.deleted_at,p1_0.iban,p1_0.invoice_type,
       p1_0.netsuite_vendor_id,p1_0.postal_code,p1_0.street,p1_0.term,p1_0.vat_number
from publisher_invoice_data p1_0 left join company_invoice_data_publisher c1_0 on (p1_0.id=c1_0.invoice_id)
where p1_0.id in
      ((select * from ((select p3_0.id from publisher_invoice_data p3_0 left join company_invoice_data_publisher c2_0 on (p3_0.id=c2_0.invoice_id)
            where p3_0.deleted_at is null and c2_0.publisher_id in
                (select p6_0.id from publisher p6_0 where p6_0.name like ? escape '!' or cast(p6_0.id as char) like ? escape '!')
            group by p3_0.id order by p3_0.id desc limit 10)) tmp
    )) 
order by p1_0.id desc

I changed to:

PagedList<PublisherInvoicing> publisherInvoicingPagedList = new BlazeJPAQueryFactory(entityManager, criteriaBuilderFactory)
            .selectFrom(publisherInvoicing)
            .leftJoin(companyInvoiceDataPublisher, companyInvoiceDataPublisher)
            .on(publisherInvoicing.id.eq(companyInvoiceDataPublisher.companyInvoiceDataPublisherPk.publisherInvoicing.id))
            .where(booleanBuilder)
            .orderBy(orderBySpecifier)
            .fetchPage((int) pageRequest.getOffset(), pageRequest.getPageSize());

[Mukit09]

Hello @beikov ! Any suggestion, please?

[Mukit09]

I added this in the root comment:

And blaze-persistence configuration is:

@Bean
@Scope(ConfigurableBeanFactory.SCOPE_SINGLETON)
@Lazy(false)
public CriteriaBuilderFactory createAdminCriteriaBuilderFactory() {
    CriteriaBuilderConfiguration config = Criteria.getDefault();
    config.setProperty("com.blazebit.persistence.inline_count_query", "false");

    return config.createCriteriaBuilderFactory(adminEntityManagerFactory);
}

[Mukit09]

No solution yet!

[jwgmeligmeyling]

The other join is added through an implicit join. I'm not sure where the implicit join is generated, but it appears to be from blaze-persitence-core and not the Querydsl integration.

You can still try:

.leftJoin(association). defaultJoin()

To force a default join being rendered so that hopefully Blaze-Persistence pick the alias up in the subquery.

If that doesn't fix it we'll need a reproducer (my guess would be that this is reproducible with just core and not the querydsl integration).