Hello,
Because a particular orphanLineItem object is gauranteed to not be in
every inUseLineItems list, every orphanLineItem gets returned: there is
always some inUseLineItems that is used to satisfy the not In clause.
You will need to use subqueries, something like
SELECT orphanLineItem FROM LineItem orphanLineItem where
orphanLineItem.id NOT IN ( Select distinct(inUseLineItems.id) FROM Order
o, In(o.lineItems) )
It may not be perfect, but to me reads as get all the LineItem items
whose IDs are not in a collection of LineItems ids referenced by Orders.
Best Regards,
Chris
Farrukh Najmi wrote:
>
> Hi Guys,
>
> Can someone knowledgable on JPQL at least let me know if there is
> anything wrong with my query?
> Thanks for your help.
>
> Farrukh Najmi wrote:
>> 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
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>