Home » Eclipse Projects » EclipseLink » FetchGroup and column alias
FetchGroup and column alias [message #651396] |
Sun, 30 January 2011 11:40 |
attila Mising name Messages: 45 Registered: April 2010 |
Member |
|
|
Hi ,
When using fetchgroup in a query the generated sql statement does not add an alias to the columns ( Eclipselink 2.1.2 )
Scenario more like a pseudo code to highlight the concept:
@Entity Country{ id, name , version, ... }
@Entity Region { id, name , version, country:Country , ....}
@Entity City { id, name , version, region: Region, country:Country , .... }
Now i'd like to query the City with the following fetch group
FetchGroup f = new FetchGroup();
f.addAttribute("name");
f.addAttribute("id");
f.addAttribute("region.id");
f.addAttribute("region.name");
f.addAttribute("country.id");
f.addAttribute("country.name");
It fails with ORA-00918: column ambiguously defined
as the sql has :
SELECT ..... t0.ID, t0.NAME, t1.ID, t1.NAME .....
The same bahaviour with nested FetchGroup
FetchGroup country = new FetchGroup();
country.addAttribute("id");
country.addAttribute("code");
f.addAttribute("country", country);
It happens when using left join fetch in jql , works fine without it
Am i missing something ?
[Updated on: Sun, 30 January 2011 14:39] Report message to a moderator
|
|
| | |
Re: FetchGroup and column alias [message #651666 is a reply to message #651540] |
Tue, 01 February 2011 08:14 |
attila Mising name Messages: 45 Registered: April 2010 |
Member |
|
|
Hi,
Yes i use pagination:
List<E> list = q.setFirstResult(ctx.getResultStart())
.setMaxResults(ctx.getResultSize())
.getResultList();
Here is the eql:
select e from City e left join fetch e.country left join fetch e.region where e.clientId = :clientId and e.country.id = :countryId and e.region.id = :regionId order by e.name
Here is the SQL:
SELECT *
FROM (SELECT /*+ FIRST_ROWS */
a.*, ROWNUM rnum
FROM (SELECT t1.ID AS a1,
t1.ACTIVE AS a2,
t1.CLIENTID AS a3,
t1.CODE AS a4,
t1.CREATEDAT AS a5,
t1.CREATEDBY AS a6,
t1.MODIFIEDAT AS a7,
t1.MODIFIEDBY AS a8,
t1.NAME AS a9,
t1.NOTES AS a10,
t1.VERSION AS a11,
t1.COUNTRY_ID AS a12,
t1.REGION_ID AS a13,
t0.ID,
t0.CODE,
t0.VERSION,
t2.ID,
t2.CODE,
t2.VERSION
FROM BD_COUNTRY t0, BD_REGION t2, BD_CITY t1
WHERE ((((t1.CLIENTID = ?) AND (t1.COUNTRY_ID = ?)) AND
(t1.REGION_ID = ?)) AND
((t0.ID = t1.COUNTRY_ID) AND (t2.ID = t1.REGION_ID)))
ORDER BY t1.NAME ASC) a
WHERE ROWNUM <= ?)
WHERE rnum > ?
As you can see the last six columns are without alias.
If i remove the left join fetch from the query it works as expected :
select e from City e where ....etc ..
[Updated on: Tue, 01 February 2011 08:16] Report message to a moderator
|
|
| | |
Goto Forum:
Current Time: Thu Apr 25 00:10:02 GMT 2024
Powered by FUDForum. Page generated in 0.02848 seconds
|