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

[spring-projects-issues]

Vijay opened DATAJPA-1332 and commented

I am having the exact issue that is mentioned on this link https://jira.spring.io/browse/DATAJPA-726 It is mentioned as solved there, but I am getting the same issue, and a search on stackoverflow shows that the problem persists. I am using SpringBoot 1.5.12.

Basically, I am doing a pagination on JOIN query as follows

@Query(Select DISTINCT new com.api.dto.FilterDTO(c.id, p.id, CASE WHEN item.dateDue IS NOT NULL THEN item.dateDue ELSE p.dateDue END AS newDateDue) FROM Customer c join c.productOrder p JOIN p.items item ")
List<FilterDTO> findDates(Pageable pageable);

And my page request is

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

However, when i run this I keep getting the error

could not resolve property: newDateDue of: com.api.beans.Customer

Evidently, it is searching for the sort field (i.e. newDateDue) in the Customer entity where it is not present.

So my question is

1. Is Pagable not applicable when a JOIN is present ?
2. Then is the only alternative to use native sql query ? But the link provided above says, this has been solved and it works for Spring Data JPA - I would like to continue using Spring Data JPA for this if it works - please help.

Thanks.

Note:There are other queries too on stackoverflow with similiar problems but no solutions. Links are as below

• https://stackoverflow.com/q/43993952/6043669
• https://stackoverflow.com/q/5903774/6043669

---

1 votes, 4 watchers

[spring-projects-issues]

Oliver Drotbohm commented

Do you have an executable sample, that shows the problem?

[spring-projects-issues]

Vijay commented

I don't have a separate sample just for this - but can create one if you feel this shouldn't be happening. I was wondering if aliases are supported in first places in pagination.

 

Do let me know, so that I can create a sample for you. Thanks

[spring-projects-issues]

Oliver Drotbohm commented

They are. However, without you specifying what dateDue is supposed to refer to, how are we supposed to know whether it's supposed to be bound to c, p or item? Using item.dueDate should work, I guess

[spring-projects-issues]

Vijay commented

Hello again,

I am creating an alias field, newDateDue (I have renamed the alias field name since it was probably confusing to have the same alias name as the field names) from two columns & I wanted to sort by this alias field newDateDue. Since it is an alias field, I can't specify either c,p or item.

(The Use case is that one of item or p can have a dueDate and I want to combine the dueDate from the 2 tables and sort by the new aliased field newDueDate).

My thinking is that if order by alias is possible without specifying table names (as in here https://coderanch.com/t/545957/databases/JPQL-SELECT-queries-field-aliases] then it probably is possible here too. 

Hope I am clear now.

Thanks

[spring-projects-issues]

Vijay commented

Hello, could you please guide on this. Thanks

[spring-projects-issues]

Vijay commented

I resolved this through Criteria Queries - which are powerful, although tough to learn.

Criteria Query cut through this (seemingly difficult query using  spring data) like a knife through butter. 

Thanks

[spring-projects-issues]

KnyGoo commented

Vijay

Can you provide an example please?

 

I have the same issue...

[spring-projects-issues]

Alexander commented

Hi all! The issue is still valid for at least Spring Data Jpa 2.1.x, 2.2.x

I've created a simple project to reproduce the problem - https://github.com/baranchikovaleks/data-jpa-sort

[derylspielman]

I too am facing this exact same issue. It is tacking on the alias "f." of the main entity instead of first searching for alias names in the list of selects. This works if I use "cl.name" as a sort field, but fails if I use alias "clientName".

Caused by: org.hibernate.QueryException: could not resolve property: clientName of: com.example.FtmRemoteFileSetting [SELECT NEW com.example.FtmRemoteFileSettingDto(f.fileName, cl.name AS clientName) FROM com.example.FtmRemoteFileSetting f LEFT JOIN f.client cl order by f.clientName asc]
        at org.hibernate.QueryException.generateQueryException(QueryException.java:120)
        at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:220)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
        at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
        at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
        at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)
        at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:604)
        at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:716)

[gregturn]

Looks like this issue will be resolved via #2863.

[gregturn]

If you check https://github.com/spring-projects/spring-data-jpa/commit/39e12eae6d64ba7192835e052a082b93b287d911, you should see that aliasing is now properly handled in sorts, and that the query at the top is included as a test case.

Feel free to check out Spring Data JPA 3.1.0-SNAPSHOT and test it out for yourself.

[gregturn]

Closing this as resolved by the earlier message.