Home » Eclipse Projects » EclipseLink » Join-Fetch with 1--> 0..N Cardinality
Join-Fetch with 1--> 0..N Cardinality [message #379898] |
Mon, 25 August 2008 15:58 |
Jason Drake Messages: 13 Registered: July 2009 |
Junior Member |
|
|
I have a question. I had a made a small change to some of my JPA based
services and while it worked fine in the client(s), the unit tests were
failing. Upon closer examination, the issue seems to be tied to my usage
of performing multiple join-fetch statements on a query.
In all cases I am performing a query on object Stamp, which has two
relationships which for these queries are important for me to fetch at
once (since some values are used in tables).
The first relationship, CatalogueNumbers is a 1:1...N relatinship.
The second relationship, Ownership is an optional one of 1:0...N
cardinality.
When I include the query hints to include both stamp.catalogueNumbers and
stamp.stampOwnerships the Queries look alright, but the results always
come back empty. I am assuming this is because there is an AND condition
where "stamp.id=ownership.stamp.id" only ownership is empty in this case.
Does anyone know how to change the query to "optionally" join-fetch if
ownership is defined?
My original query was a named query of:
"SELECT stamp FROM Stamp stamp WHERE stamp.country.id=:country_id"
The resultant SQL (I am using MySQL 5 if that is relevant) for a single
join-fetch to CatalogueNumbers is (which works):
SELECT t1.ID, t1.CREATESTAMP, t1.MODIFYSTAMP, t1.DESCRIPTION,
t1.DENOMINATION, t1.PRINTING, t1.COUNTRY_ID, t0.ID, t0.CREATESTAMP,
t0.MODIFYSTAMP, t0.CATALOGUEVALUE, t0.ACTIVE, t0.CAT_CONDITION, t0.NUMBER,
t0.STAMP_ID, t0.CATALOGUE_REF FROM CATALOGUENUMBERS t3, STAMPS t2, STAMPS
t1, CATALOGUENUMBERS t0 WHERE (((t1.ID = t2.ID) AND (t3.NUMBER LIKE ?))
AND ((t2.ID = t3.STAMP_ID) AND (t0.STAMP_ID = t1.ID)))
Whereas the mutliple join-fetch (including the 0 cardinality Ownership) is:
SELECT t1.ID, t1.CREATESTAMP, t1.MODIFYSTAMP, t1.DESCRIPTION,
t1.DENOMINATION, t1.PRINTING, t1.COUNTRY_ID, t0.ID, t0.CREATESTAMP,
t0.MODIFYSTAMP, t0.CATALOGUEVALUE, t0.ACTIVE, t0.CAT_CONDITION, t0.NUMBER,
t0.STAMP_ID, t0.CATALOGUE_REF, t2.ID, t2.CREATESTAMP, t2.MODIFYSTAMP,
t2.PRICE, t2.PURCHASED, t2.GRADE, t2.THECONDITION, t2.IMAGE, t2.NOTES,
t2.CURRENCY, t2.STAMP_ID, t2.ALBUM_ID FROM CATALOGUENUMBERS t4, STAMPS t3,
OWNERSHIP t2, STAMPS t1, CATALOGUENUMBERS t0 WHERE (((t1.ID = t3.ID) AND
(t4.NUMBER LIKE ?)) AND (((t3.ID = t4.STAMP_ID) AND (t0.STAMP_ID = t1.ID))
AND (t2.STAMP_ID = t1.ID)))
|
|
| | | | |
Re: Join-Fetch with 1--> 0..N Cardinality [message #381064 is a reply to message #380539] |
Fri, 29 August 2008 20:31 |
Jason Drake Messages: 13 Registered: July 2009 |
Junior Member |
|
|
James wrote:
> The use an outer join with join-fetch you have to use the
> getAllowingNull() or anyOfAllowingNone() Expression methods, otherwise an
> inner join is used and empty or null relationships will be filtered.
> If you are still having issues please include your full query.
> -- James
James,
I am struggling a little here.
orm.xml named query is:
<named-query name="Stamp.FindByCountry">
<query>SELECT stamp FROM Stamp stamp WHERE
stamp.country.id=:country_id</query>
</named-query>
Stamp has a 0:N relationship to Ownership (attribute is "stampOwnerships")
Stamp has a 1:N relationship to CatalogeNumber (attribute is
catalogueNumbers)
I want the CatalogueNumbers and Ownerships fetched when I run this query
so that they can be used efficiently in a table or results which uses
values from those relationships (as well as the stamp).
in my services....
Query qs = entity.createNamedQuery("Stamp.FindByCountry");
qs.setParameter("country_id", country.getId());
// (here was where I was doing my join-fetch see below)
List values = qs.getResultList();
For the join-fetch, I was doing the following....
qs.setHint("eclipselink.join-fetch","catalogueNumbers");
qs.setHint("eclipselink.join-fetch","stampOwnerships");
This worked, as long as the ownership relationship was 1:N, but failed
when it was 0:N.
Looking at your post, it seems I can use the ExpressionBuilder to add the
"anyOfAllowingNone( )" call, but how do I add this to the Query? I should
note, this query is executed in a findAll( ) method in my services which
also will call other named queries if I am searching by other
objects/conditions, so Ideally I'd like to apply some solution to the
Query if possible.
|
|
| | | | |
Goto Forum:
Current Time: Wed Sep 25 12:02:09 GMT 2024
Powered by FUDForum. Page generated in 0.04875 seconds
|