Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
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 Go to next message
Jason Drake is currently offline Jason DrakeFriend
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 #379899 is a reply to message #379898] Mon, 25 August 2008 16:01 Go to previous messageGo to next message
Jason Drake is currently offline Jason DrakeFriend
Messages: 13
Registered: July 2009
Junior Member
Another question... does anyone know why in the first valid query that
EclipseLink/Toplink inserts the T2 and T3? I ask because these are the
same tables as T0 and T1 and there are not part of the selection (only the
WHERE clause). It seems they are redundant to me.

Thanks
Re: Join-Fetch with 1--> 0..N Cardinality [message #380523 is a reply to message #379899] Wed, 27 August 2008 12:55 Go to previous messageGo to next message
James is currently offline JamesFriend
Messages: 272
Registered: July 2009
Senior Member
The issue that multiple join-fetch hints are not supported is a bug, only
a single join-fetch hint is support currently. Please log this bug,

https://bugs.eclipse.org/bugs/

The issue of the duplicate table alias seems odd. It seems like you have
either mapped or joined things incorrectly. Please include the complete
query and mapping for the classes.

-- James
Re: Join-Fetch with 1--> 0..N Cardinality [message #380533 is a reply to message #380523] Wed, 27 August 2008 16:49 Go to previous messageGo to next message
Jason Drake is currently offline Jason DrakeFriend
Messages: 13
Registered: July 2009
Junior Member
James, I believe you have mis-read the issue. The issue of multiple
fetch-joins in orm.xml is not working (as was cited earlier), however
using programatic query hints I have been able to get this to work
(although it does require careful construction of the query).

The issue I am discussing here is the 0....N cardinality of a one to many
relationship. When the other side is empty (ie. there are currently no
many's) and you specify this side in a join-fetch the resultant query will
return nothing for the left side (the main subject of the query). This is
because the join-fetch adds a "AND left.id=right.left_refid" condition,
but the right side is empty so this condition is never satisfied. I think
the solution here is something like:

AND ( left.id=right.left_refid OR left.id NOT IN (select right.left_refid
FROM leftjointable) )

I realize this is poor syntax ( I am not a SQL expert - hence while I use
JPA!)
Re: Join-Fetch with 1--> 0..N Cardinality [message #380539 is a reply to message #380533] Thu, 28 August 2008 13:53 Go to previous messageGo to next message
James is currently offline JamesFriend
Messages: 272
Registered: July 2009
Senior Member
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
Re: Join-Fetch with 1--> 0..N Cardinality [message #381064 is a reply to message #380539] Fri, 29 August 2008 20:31 Go to previous messageGo to next message
Jason Drake is currently offline Jason DrakeFriend
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.
Re: Join-Fetch with 1--> 0..N Cardinality [message #381067 is a reply to message #381064] Tue, 02 September 2008 06:34 Go to previous messageGo to next message
Frank Schwarz is currently offline Frank SchwarzFriend
Messages: 25
Registered: July 2009
Location: Dresden
Junior Member
> This worked, as long as the ownership relationship was 1:N, but failed
> when it was 0:N.

Hi
Re: Join-Fetch with 1--> 0..N Cardinality [message #381068 is a reply to message #381064] Tue, 02 September 2008 06:42 Go to previous messageGo to next message
Frank Schwarz is currently offline Frank SchwarzFriend
Messages: 25
Registered: July 2009
Location: Dresden
Junior Member
> 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.

Hi Jason,

I am experiencing the same problems with join-fetch hints. Those hints do
not seem to care too much about mapping metadata. They seem to treat all
relationships as mandatory thus producing inner joins instead of left
outer joins in the SQL.

IMHO, a fetch plan should be orthogonal to weather a relationship is
mandatory or optional. See also
https://bugs.eclipse.org/bugs/show_bug.cgi?id=244124

Kind regards,
Frank
Re: Join-Fetch with 1--> 0..N Cardinality [message #381072 is a reply to message #381064] Tue, 02 September 2008 13:59 Go to previous messageGo to next message
James is currently offline JamesFriend
Messages: 272
Registered: July 2009
Senior Member
The join-fetch query hint does not currently support outer-joins (log or
vote for this bug please). EclipseLink does support this through its
ReadAllQuery and Expression API. You will need to cast to the EclipseLink
JpaQuery to do this.

ReadAllQuery query = (ReadAllQuery)((JpaQuery)query).getDatabaseQuery();
query.addJoinedAttribute(query.getExpressionBuilder().anyOfA llowingNone( "catalogueNumbers"));
query.addJoinedAttribute(query.getExpressionBuilder().anyOfA llowingNone( "stampOwnerships"));


-- James
Re: Join-Fetch with 1--> 0..N Cardinality [message #381082 is a reply to message #381072] Thu, 04 September 2008 22:07 Go to previous message
Jason Drake is currently offline Jason DrakeFriend
Messages: 13
Registered: July 2009
Junior Member
James, this works like a charm! Thanks. I think when I was trying to
follow the previous code hint I was close, but this worked like a champ.

I will place a vote on the bug, and since I am putting these hint
additions in a private method, I can wrap this very cleanly.

Sorry it took a while to get this resolved up (on my end).

-Jason
Previous Topic:logs and MOXy
Next Topic:NPE running JPA with Spring 2.5 & Tomcat 5
Goto Forum:
  


Current Time: Thu Dec 18 19:48:49 GMT 2014

Powered by FUDForum. Page generated in 0.02791 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software