Eclipse Community Forums: EclipseLink » JPQL ignores OrderBy annotation when using JOIN FETCH

Help

Home

Home » Eclipse Projects » EclipseLink » JPQL ignores OrderBy annotation when using JOIN FETCH

Show: Today's Messages :: Show Polls :: Message Navigator

JPQL ignores OrderBy annotation when using JOIN FETCH [message #998863]

Thu, 10 January 2013 21:07

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

Report message to a moderator

Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #999198 is a reply to message #998863]

Fri, 11 January 2013 14:50

Chris Delahunt

Messages: 1389
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

Report message to a moderator

Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #1000717 is a reply to message #999198]

Tue, 15 January 2013 10:13

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

Report message to a moderator

Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #1000851 is a reply to message #1000717]

Tue, 15 January 2013 14:41

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

Report message to a moderator

Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #1000876 is a reply to message #1000851]

Tue, 15 January 2013 15:30

Michal Ondrovic

Messages: 7
Registered: February 2010

Junior Member

Unfortunately you cannot use aliases for fetch joins(JPA spec)

Report message to a moderator

Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #1000877 is a reply to message #1000876]

Tue, 15 January 2013 15:30

Michal Ondrovic

Messages: 7
Registered: February 2010

Junior Member

Unfortunately you cannot use aliases for fetch joins(JPA spec)

Report message to a moderator

Re: JPQL ignores OrderBy annotation when using JOIN FETCH [message #1000905 is a reply to message #1000877]

Tue, 15 January 2013 16:57

Chris Delahunt

Messages: 1389
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

Report message to a moderator