Pageable Always Adding From Entity to Sort Field Name [DATAJPA-726]

[spring-projects-issues]

Corey Mosher opened DATAJPA-726 and commented

When using @Query in a JPA repository along with Pageable and attempting to sort on a field in a joined entity, the from entity gets prepended to the sort field. For example, if I do the following query:

@Query(value = "select p from Customer c join c.productOrder p where p.delayed = true")

If I pass a PageRequest sorting on a field in the from entity (Customer), everything will work fine:

PageRequest page = new PageRequest(0, 20, Direction.DESC, "lastName");

However, if I simply decide to sort by a field in the ProductOrder entity with this PageRequest:

PageRequest page2 = new PageRequest(0, 20, Direction.DESC, "p.lineItems");

I will get the following error:

Caused by: org.hibernate.QueryException: could not resolve property: p of: hello.Customer [select p from hello.Customer c join c.productOrder p where p.delayed = true order by c.p.lineItems desc]

c. was prepended onto the sort field. I am attaching a modified version of the Spring Data JPA tutorial from the Spring site that illustrates the issue

---

Attachments:

• gs-accessing-data-jpa-master.zip (86.25 kB)

Referenced from: commits 51a8885, baae6c7, 28ac667, 67b93c2

Backported to: 1.8.1 (Fowler SR1), 1.7.3 (Evans SR3), 1.6.6 (Dijkstra SR6)

[spring-projects-issues]

Oliver Drotbohm commented

That should be fixed. We now inspect all kinds of joins for aliases

[spring-projects-issues]

Oliver Drotbohm commented

I just realized that the better way of defining the sort would be new Sort("productOrder.lineItems") instead of p.lineItems as this avoids the client having the need to know about the alias used in the query declaration and the sort expression basically expresses the full path to reference the property to sort on

[spring-projects-issues]

Corey Mosher commented

Good to know. I tried the example using 1.7.3 snapshot and the problem seems to be gone, so once 1.7.3 goes to release and it's picked up by spring boot, it should resolve the issue for me

[spring-projects-issues]

Oliver Drotbohm commented

That's great to hear. Be advised that you can always trick Boot into newer versions of Spring Data by setting spring-data-releasetrain.version property to the version you want to use. Fowler-SR1 and Evans-SR3 are currently planned to be released at the very same time mid June roundabout

[Dawood-ibn-Kareem]

I still have this problem. I'm using spring-core 5.3.22 and spring-data-jpa 2.7.2. What versions of each do I need to have the fix?

[odrotbohm]

Would be cool if you could provide us with a minimal reproducer and create a new ticket for that.

[gregturn]

@Dawood-ibn-Kareem Please follow #2960 and you can track our progress on resolving this issue. Pay note, that while that ticket is assigned to Spring Data JPA 3.2, the solution will certainly be backported to 3.1. However, it's unlikely to be backported to 2.7, considering we don't have the newly built HQL/JPQL parser available, and such detailed operations are very difficult with the old QueryUtils.