Query DSL with blaze-persistence is generating an extra joining
Mukit09 opened this issue · 6 comments
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
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.
@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());
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);
}
No solution yet!
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).