Adapter - How to perform "AND" filter on the same field in a 1-M relation

Posts   
 
    
aliem
User
Posts: 21
Joined: 03-Oct-2006
# Posted on: 09-Feb-2007 23:33:54   

This should be a simple task that I am having problems coding.

Here is a simplified depiction of the scenario:

I have two tables:

Listing -ListingId (PK)

ListingKeyword -ListingId (FK on Listing.ListingId) -KeywordId -ListingKeywordId (PK - not really necessary)

They are related as Listing[1-M]ListingKeyword

I want to filter on Listing by ListingKeyword.KeywordId in an "AND" fashion.

So a simple example would be get all Listings with a (KeywordId = 1 And KeywordId = 2)

For some reason my code below will not work. If I do "AddWithOr" it will work as you would expect, BUT if I do with "AddWithAnd", no filter matches a query with 2 or more parameters.

Here is the code (simplified):


    
DataAccessAdapter adapter = new DataAccessAdapter(true);
EntityCollection<ListingEntity> listings = new EntityCollection<ListingEntity>(new ListingEntityFactory());

RelationPredicateBucket bucket = new RelationPredicateBucket();
 bucket.Relations.Add(ListingEntity.Relations.ListingKeywordEntityUsingListingId);

PredicateExpression listingFilter = new PredicateExpression();
 listingFilter.AddWithAnd(ListingKeywordFields.KeywordId == 1);
 listingFilter.AddWithAnd(ListingKeywordFields.KeywordId == 2); //Problem here!

bucket.PredicateExpression.Add(listingFilter);
adapter.FetchEntityCollection(listings, bucket, 0);




So this will not work, even though there are Listings which match KeywordId = 1 and 2 it will display nothing. If I do "AddWithOr" it will display all listings that match.

My only guess is that I must do something different with the way I create the relations, perhaps the order is incorrect?

I figure there must be a simple solution to this as tag/keyword filtering is maybe the most common requirement of web/database applications.

Many thanks for any help!

aliem
User
Posts: 21
Joined: 03-Oct-2006
# Posted on: 10-Feb-2007 00:04:15   

I think I'm just too rusty on my SQL coding as what I'm trying to do does not make sense, as I now realize. No SQL call will return a row with a filter on the same field with two different values simple_smile .

... I need to think about this more obviously simple_smile .

psandler
User
Posts: 540
Joined: 22-Feb-2005
# Posted on: 10-Feb-2007 00:29:06   

aliem wrote:

I think I'm just too rusty on my SQL coding as what I'm trying to do does not make sense, as I now realize. No SQL call will return a row with a filter on the same field with two different values simple_smile .

... I need to think about this more obviously simple_smile .

I was just going to post that same thought.

What you are trying to do is actually trickier than it sounds.

See post #7 from this thread by Joe Celko: Google groups thread

Here is an example (in SQL) using the Northwind database similar to what you are trying to do.


SELECT
    orderId
FROM
    [order details] o
    INNER JOIN products p ON o.productid = p.productid
WHERE
    p.productid = 11
    OR p.productid = 42
    OR p.productid = 72
GROUP BY
    o.orderId
HAVING
    --"3" is the number of products we are filtering on
    COUNT(p.productId) = 3

Basically, this translates to: get all order details that match any of the products, and return those that have a count equal to the number of products we are filtering on.

HTH,

Phil