Eclipse Community Forums: EclipseLink » JoinFetch does not work with ORDER BY in query

Help

Home

Home » Eclipse Projects » EclipseLink » JoinFetch does not work with ORDER BY in query

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

JoinFetch does not work with ORDER BY in query [message #552420]

Thu, 12 August 2010 07:59

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

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

Report message to a moderator