Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPQL ignores OrderBy annotation when using JOIN FETCH
JPQL ignores OrderBy annotation when using JOIN FETCH [message #998863] Thu, 10 January 2013 16:07 Go to next message
Michal Ondrovic is currently offline Michal Ondrovic
Messages: 7
Registered: February 2010
Junior Member
Dear all,

I have little troubles with JPQL and ordering OneToMany and ManyToMany Lists.
Let's say I have an entity class A:
@Entity
public class A implements Serializable {
...
    @ManyToMany(fetch = FetchType.LAZY)
    @OrderBy("name")
    private List<B> bList; // B is also an entity class
...
}

When I load the entity using
entityManager.find(A.class, id)
or with query
SELECT entity FROM A entity WHERE entity.id = :id
and initialize the lazy list, bList is ordered.
But when I try to load it using JOIN FETCH
SELECT entity FROM A entity LEFT JOIN FETCH entity.bList WHERE entity.id = :id
bList is not ordered!

The question is, is it possible to order lists in JPQL with JOIN FETCH?

My env: EclipseLink 2.4.1, MySQL 5.5.28, Glassfish 3.1.2.2, Netbeans 7.2.1, Java 1.6.37.

Thank you

Michael
Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #999198 is a reply to message #998863] Fri, 11 January 2013 09:50 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1017
Registered: July 2009
Senior Member
Please vote for bug https://bugs.eclipse.org/bugs/show_bug.cgi?id=241874 to have this resolved.

A potential workaround might be to add ordering to the query that has the fetch join using the entity id and the entity.bList order columns.

Best Regards,
Chris
Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #1000717 is a reply to message #999198] Tue, 15 January 2013 05:13 Go to previous messageGo to next message
Michal Ondrovic is currently offline Michal Ondrovic
Messages: 7
Registered: February 2010
Junior Member
Please, how can I set the order in JPQL? This query:

SELECT entity FROM A entity LEFT JOIN FETCH entity.bList
WHERE entity.id = :id
ORDER BY entity.bList.name

throws this expcetion:
java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:
Exception Description: Problem compiling [SELECT entity FROM A entity LEFT JOIN FETCH entity.bList
WHERE entity.id = :id
ORDER BY entity.bList.name]
Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.4.1.v20121003-ad44345): org.eclipse.persistence.exceptions.JPQLException
...
The state field path 'entity.bList.name' cannot be resolved to a valid type.
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:150)

I've tried also namedqueries with query hints (batch, fetch,...), with no success.

Thank you

Michal
Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #1000851 is a reply to message #1000717] Tue, 15 January 2013 09:41 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Try,

SELECT entity FROM A entity LEFT JOIN FETCH entity.bList list
WHERE entity.id = :id
ORDER BY list.name


James : Wiki : Book : Blog : Twitter
Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #1000876 is a reply to message #1000851] Tue, 15 January 2013 10:30 Go to previous messageGo to next message
Michal Ondrovic is currently offline Michal Ondrovic
Messages: 7
Registered: February 2010
Junior Member
Unfortunately you cannot use aliases for fetch joins(JPA spec)
Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #1000877 is a reply to message #1000876] Tue, 15 January 2013 10:30 Go to previous messageGo to next message
Michal Ondrovic is currently offline Michal Ondrovic
Messages: 7
Registered: February 2010
Junior Member
Unfortunately you cannot use aliases for fetch joins(JPA spec)
Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #1000905 is a reply to message #1000877] Tue, 15 January 2013 11:57 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1017
Registered: July 2009
Senior Member
What happened when you specified the fetch join using a query hint instead of in the JPQL?

query = em.createQuery("SELECT entity FROM A entity Join entity.bList list
WHERE entity.id = :id ORDER BY list.name ");
query.setHint("eclipselink.join-fetch", "entity.bList");


Best Regards,
Chris
Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #1001880 is a reply to message #1000876] Thu, 17 January 2013 09:06 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

EclipseLink 2.4 allows alias on a fetch join.


James : Wiki : Book : Blog : Twitter
Previous Topic:eclipselink 2.4 TABLE_PER_TENANT shared EMF
Next Topic:Parameterized SessionCustomizer with spring (or without)
Goto Forum:
  


Current Time: Sat Aug 23 03:33:40 EDT 2014

Powered by FUDForum. Page generated in 0.01769 seconds