Hi,

I need to filter a entity on 2 different conditions on a related entity, and both have to qualify:

For example: I require to get an Order which contains apples, but not bananas.

Therefore I would like to join the OrderItem table and filter: OrderItem.ProductName = 'apples' AND OrderItem.ProductName != 'bananas'

Is there a way to perform this using regular PredicateExpressions, or do I require to use "FieldCompareSetPredicates"?

Please advice,

Using: Adapter, 2.6

Something like...

RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(OrderEntity.Relations.OrderItemsUsingOrderId);
bucket.PredicateExpression.Add(OrderItemFields.ProductName=="apples");
bucket.PredicateExpression.AddWithAnd(OrderItemFields.ProductName!="bananas");

Matt

RelationPredicateBucket bucket = new RelationPredicateBucket(); bucket.Relations.Add(OrderEntity.Relations.OrderItemsUsingOrderId); bucket.PredicateExpression.Add(OrderItemFields.ProductName=="apples"); bucket.PredicateExpression.AddWithAnd(OrderItemFields.ProductName!="bananas");

I don't think so, because if an individual row has a product name of apples it will always be not equal to bananas!

He needs the relation added twice, maybe one with an alias, the first filtered on = apples and the second independent on filtered on != bananas

gabrielk wrote:

Hi,

I need to filter a entity on 2 different conditions on a related entity, and both have to qualify:

For example: I require to get an Order which contains apples, but not bananas.

Therefore I would like to join the OrderItem table and filter: OrderItem.ProductName = 'apples' AND OrderItem.ProductName != 'bananas'

It's not clear if by "I would like to join the OrderItem" you meant a Join to filter the Orders, or you want to prefetch the OrderItems. Could you please show us an approximate SQL of such query?

A query could be:

SELECT * FROM Orders WHERE 
(
    /* REQUIRE At least 1 apples */ 
    SELECT COUNT(*)  FROM OrderItem 
    WHERE 
    OrderItem.OrderId=Order.OrderId AND 
    ProductName='apples'
) > 0 
AND
(
    /* Don't allow banans */ 
    SELECT COUNT(*)  FROM OrderItem 
    WHERE 
    OrderItem.OrderId=Order.OrderId AND 
    ProductName='bananas'
) = 0 

Is this possible using a single query/fetch in LLBGen?

Cheers, Gab

I think the following query is easier:

SELECT * FROM Order
WHERE 
OrderId IN (SELECT OrderId From OrderItem WHERE ProductName = 'apple')
AND 
OrderId NOT IN (SELECT OrderId From OrderItem WHERE ProductName = 'banana')

And this can be easily implemented by 2 FieldCompareSetPredicate filters.