Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JoinFetch does not work with ORDER BY in query
icon11.gif  JoinFetch does not work with ORDER BY in query [message #552420] Thu, 12 August 2010 07:59 Go to next message
Mikael Nousiainen is currently offline Mikael Nousiainen
Messages: 7
Registered: August 2010
Junior Member
I have two entities: Registry and Channel. A Registry has the following mapping to Channel:

@ManyToOne(optional = true, fetch = FetchType.LAZY)
@JoinColumn(name = "DEFAULT_CHANNEL_ID", nullable = true)
@JoinFetch(JoinFetchType.OUTER)
private Channel defaultChannel;

I want to sort registries based on the defaultChannel.name property and I also want to fetch Registry entities
with NULL defaultChannel. This should be possible with OUTER join. But as soon as I add ORDER BY defaultChannel.name,
EclipseLink forgets the OUTER JOIN and uses a normal WHERE condition and all NULL values are excluded from the results.

I'm using CriteriaBuilder API with latest EclipseLink 2.1.0 in GlassFish 3.0.1. I'm using Root.get("defaultChannel").get("name")
and passing that value to CriteriaBuilder.asc() or desc() and finally that to CriteriaQuery.orderBy() to do sorting. The underlying database is MySQL 5.

Generated SQL when NOT sorting:

SELECT t1.ID AS a1, t1.PRIVATE_REGISTRY AS a2, t1.DESCRIPTION AS a3, t1.NAME AS a4, t1.PROVIDER AS a5, t1.EXTERNAL_ID AS a6, t1.REGISTRY_POLICY_ID AS a7, t1.DEFAULT_CHANNEL_ID AS a8, t1.CREATED_BY AS a9, t1.MODIFIED_BY AS a10, t1.CREATION_TIME AS a11, t1.MODIFICATION_TIME AS a12, t0.ID AS a13, t0.DIRECTION AS a14, t0.DESCRIPTION AS a15, t0.NAME AS a16, t0.ACTIVE AS a17, t0.TYPE AS a18, t0.CHANNEL_IDENTIFIER AS a19, t0.CREATED_BY AS a20, t0.MODIFIED_BY AS a21, t0.CREATION_TIME AS a22, t0.MODIFICATION_TIME AS a23 FROM REGISTRY t1 LEFT OUTER JOIN CHANNEL t0 ON (t0.ID = t1.DEFAULT_CHANNEL_ID) ORDER BY t1.NAME ASC LIMIT ?, ?

Generated SQL when using ORDER BY:

SELECT t1.ID AS a1, t1.PRIVATE_REGISTRY AS a2, t1.DESCRIPTION AS a3, t1.NAME AS a4, t1.PROVIDER AS a5, t1.EXTERNAL_ID AS a6, t1.REGISTRY_POLICY_ID AS a7, t1.DEFAULT_CHANNEL_ID AS a8, t1.CREATED_BY AS a9, t1.MODIFIED_BY AS a10, t1.CREATION_TIME AS a11, t1.MODIFICATION_TIME AS a12, t0.ID AS a13, t0.DIRECTION AS a14, t0.DESCRIPTION AS a15, t0.NAME AS a16, t0.ACTIVE AS a17, t0.TYPE AS a18, t0.CHANNEL_IDENTIFIER AS a19, t0.CREATED_BY AS a20, t0.MODIFIED_BY AS a21, t0.CREATION_TIME AS a22, t0.MODIFICATION_TIME AS a23 FROM CHANNEL t0, REGISTRY t1 WHERE (t0.ID = t1.DEFAULT_CHANNEL_ID) ORDER BY t0.NAME ASC LIMIT ?, ?

[Updated on: Thu, 12 August 2010 08:00]

Report message to a moderator

Re: JoinFetch does not work with ORDER BY in query [message #552562 is a reply to message #552420] Thu, 12 August 2010 16:01 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

The, Root.get("defaultChannel")

is specifiy an inner join, so is canceling the outer join. You need to use,

join("defaultChannel", JoinType.LEFT)

instead of the get().


James : Wiki : Book : Blog : Twitter
Re: JoinFetch does not work with ORDER BY in query [message #552663 is a reply to message #552420] Fri, 13 August 2010 06:03 Go to previous message
Mikael Nousiainen is currently offline Mikael Nousiainen
Messages: 7
Registered: August 2010
Junior Member
Thanks a lot! That solved the problem.
Previous Topic:@Valid and collections
Next Topic:Join table with constant value column
Goto Forum:
  


Current Time: Fri Oct 31 18:10:01 GMT 2014

Powered by FUDForum. Page generated in 0.18762 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software