| QueryHints.LEFT_FETCH not working [message #778458] |
Thu, 12 January 2012 11:34  |
Eclipse User |
|
|
|
First of all apologies if this has been posted before or its in the wrong place. I have had a good trawl through these forums but cant seem to solve it.
INFO: EclipseLink, version: Eclipse Persistence Services - 2.3.0.v20110604-r9504
INFO: Running GlassFish Version: GlassFish Server Open Source Edition 3.1.1 (build 12)
Here is my scenario
@Entity
public class ObjectA{
/.......... other code ........./
private Integer subvalueid;
private String textvalue;
@OneToOne(optional=true,fetch=FetchType.EAGER)
@JoinColumn(name="subvalueid",unique=true,nullable=false,updatable=false,insertable=false)
private ObjectB subvalue;
}
@Entity
public class ObjectB{
/.......... other code ........./
private Integer id;
private String description;
}
Query query = entityManager.createQuery("SELECT A FROM ObjectA a WHERE a.textvalue = :val1 OR a.subvalue.description = :val2");
query.setHint(QueryHints.LEFT_FETCH, "a.subvalue");
query.setParameter("val1", "/..something ../");
query.setParameter("val2", "/..something ../");
query.getResultList();
My query produces
SELECT /*lots of fields*/ FROM ObjectA a, ObjectB b WHERE ((a.textvalue = ? OR (b.description) = ?) AND (b.ID = a.subvalueid))
b.ID = a.subvalueid means that only records where there is a match between the relationship are returned.
I want to try and enforce the query to produce a LEFT JOIN so that it includes records in table / ObjectA even if there is no entry in table / ObjectB
I have tried allsorts of QueryHints and JOIN FETCH on the query but am having no such look.
Any help is greatly appreciated.
Many thanks in advance
Jonathan Jones
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: QueryHints.LEFT_FETCH not working [message #778678 is a reply to message #778458] |
Fri, 13 January 2012 11:12  |
| Eclipse User |
|
|
|
The fetch join is working, the problem is that your query is using an inner join on the same a.subvalue relationship which is more restrictive than the left outer join. The query hint is independent of the expression, and will not override asspects of it.
Either remove the "OR a.subvalue.description = :val2" from the where clause, or define it so that it also uses an outer left join. ie
Query query = entityManager.createQuery(""SELECT A FROM ObjectA a outer join a.subvalue s where a.textvalue = :val1 OR s.description =: val2");
query.setHint(QueryHints.LEFT_FETCH, "a.subvalue");
Best Regards,
Chris
|
|
|
Powered by
FUDForum. Page generated in 0.24317 seconds
Home