Sort by property of an associated object generates inner join instead of left join with QueryDsl and Hibernate [DATAJPA-427]

[spring-projects-issues]

petar zrinščak opened DATAJPA-427 and commented

I have a custom repository implementation with QueryDsl predicate. When I sort by property of an associated object query that gets generated uses inner join instead left join statement.

I also have a custom repository implementation with JpaSpecification executor. There everything works well.

I have attached a test case

---

Affects: 1.4.2 (Babbage SR1)

Attachments:

• spring-data-test.zip (37.07 kB)

Issue Links:

• DATAJPA-491 Sort by property of an associated object generates left join only for the first level

Backported to: 1.4.3 (Babbage SR2)

[spring-projects-issues]

Thomas Darimont commented

Hello Petar,

thanks for the useful testcase! It turns out that this is not a problem of spring data jpa but rather a bug in querydsl.
I filed: querydsl/querydsl#551

As a workaround I'd suggest to use a custom repository method or a JPAQL/SQL query.

Cheers,
Thomas

[spring-projects-issues]

Thomas Darimont commented

In the mean time timowest from the querydsl team suggested another workaround:

@Override
public Page<Mail> findByContent2(String content, Pageable page) {
     QMail mail = QMail.mail;
     QSender sender = QSender.sender;
           long total = ....
     List<Mail> list = new JPAQuery(entityManager).from(mail).leftJoin(mail.sender, sender)
               .where(mail.content.eq(content)).orderBy(sender.name.asc())
                           //... adjust ordering and limits accordingly 
                           .list(mail)
     return new PageImpl<Mail>(list, page, total);
}

[spring-projects-issues]

Thomas Darimont commented

Requires some changes in DATACMNS

[spring-projects-issues]

Thomas Darimont commented

Moved to next Sprint Codd M1

[spring-projects-issues]

Thomas Darimont commented

Added PR: #53

[spring-projects-issues]

Thomas Darimont commented

Please revise

[spring-projects-issues]

Thomas Darimont commented

Added support for sorting by properties of embeddable value objects.

Please revise