persistence@glassfish.java.net

Re: Need help with query to find LineItems not in any Order

From: Farrukh Najmi <farrukh_at_wellfleetsoftware.com>
Date: Wed, 29 Apr 2009 09:58:44 -0400

Hi Wouter,

Thanks for the suggestion. Unfortunately, I do not have flexibility of
changing the bean definition and need a solution for the unidirectional
Parent/ComposedCollection relationship.

Wouter van Reeven wrote:
> Hi,
>
>
> How about making the OneToMany bidirectional and querying for all LineItems
> where order is empty?
>
>
> HTH, Wouter van Reeven
>
> On Wed, Apr 29, 2009 at 09:46:58AM -0400, Farrukh Najmi wrote:
>
>> Hi Guys,
>>
>> I have the following JPA entity beans based on familiar Order/LineItem
>> example used in JPA spec:
>>
>> public class Order {
>>
>> ...
>> protected List<LineItem> lineItems;
>>
>> @OneToMany(targetEntity = LineItem.class, cascade = {
>> CascadeType.ALL
>> })
>> @JoinColumn(name = "LINEITEM_ORDER_ID")
>> public List<LineItem> getLineItems() {...}
>> }
>>
>> public class LineItem {
>> ...
>> }
>>
>> I need a query that will find all LineItems that are not in an Order's
>> lineItem collection attribute. The following query does not seem to
>> work. It returns all LineItems and not just the orphan LineItems.
>>
>> SELECT DISTINCT Object(orphanLineItem) FROM LineItem orphanLineItem,
>> Order o, IN (o.lineItems) inUseLineItems WHERE orphanLineItem NOT IN
>> inUseLineItems
>>
>> Any suggestions would be greatly appreciated on how to fix above query
>> so it matches only those LineItems that are not in any Order's lineItems
>> collection attribute. Thanks.
>>
>> --
>> Regards,
>> Farrukh
>>
>> Web: http://www.wellfleetsoftware.com
>>
>>
>>
>
>


-- 
Regards,
Farrukh
Web: http://www.wellfleetsoftware.com