Home » Eclipse Projects » EclipseLink » JoinFetch does not work with ORDER BY in query
 JoinFetch does not work with ORDER BY in query [message #552420] |
Thu, 12 August 2010 03:59  |
Eclipse User |
|
|
|
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 04:00] by Moderator |
|
| | |
Goto Forum:
Current Time: Wed Jul 23 13:36:55 EDT 2025
Powered by FUDForum. Page generated in 1.45615 seconds |
Home