Re: [eclipselink-users] Limiting the returned children in a query select

[Tom Ware <tom.ware@xxxxxxxxxx>]

Hi Mike,

  The problem with what you are trying to do is that when EclipseLink retrieves 
relationships from your event, it has to retrieve the whole relationship.  If it 
retrieves only some elements of the relationship (e.g. only the transactions 
with status = 'FAILED') the object will not be correct - it will be missing any 
transactions it contains with other status values.

  You should be able to write a query that selects all the appropriate 
transactions with status failed and their related events.  'Something like:

 select t, e from Transaction t join t.source s join s.event e where t.status = 
'FAILED'

  Depending on how your relationships are structured, support for conditional 
joins as suggested by Bernard may help you write a query that will return 
results that are closer to what you are looking for - have a look at the 
examples in the bug.

  The bottom line, however, is that when EclipseLink reads an object, it 
partially populate a list that represents a xToMany relationship.  There is some 
EclipseLink-Native-API functionality that could help you create a mapping or a 
will return just the 'FAILED' statuses, but that involves have a mapping that 
returns failed statuses all the time.

-Tom

bht@xxxxxxxxxxxxx wrote:
> Hi Mike,
>
> This should be a FAQ :)
>
> Please see the recent thread "Conditional LEFT JOIN in JPQL".
>
> I would suggest you vote for
>
> https://bugs.eclipse.org/bugs/show_bug.cgi?id=312146
>
> Regards
>
> Bernard
>
> On Sat, 15 May 2010 14:58:52 -0700, you wrote:
>
> > I have the following relationship:
> > event->source->transaction, where -> represents a bidirectional
> > one-to-many relationship.
> >
> > I am trying to select the Event object map for which it's related
> > transaction.status field = 'FAILED', with that object map containing
> > ONLY the transactions that meet that criteria. The following sql is
> > successful:
> > select * from event left join source ON event.id = source.event_id left
> > join transaction ON source.id = transaction.source_id where
> > transaction.status = 'FAILED';
> >
> > The following JPQL ends up returning the correct Event, but it also
> > contains all it's Transaction children, not just the ones with
> > transaction.status = 'FAILED':
> > select e FROM Event e LEFT JOIN e.sources s LEFT JOIN s.transactions t
> > where t.status = 'FAILED'
> >
> > Any idea's?
> >
> > Thanks,
> > Mike
> >
> > _______________________________________________
> > eclipselink-users mailing list
> > eclipselink-users@xxxxxxxxxxx
> > https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users