Hi there...

I'm trying to write a filter; I've got most filters working for me based on the best practices.

One has me puzzled, though:

I've got three linked tables table A (PK A_ID) - table AB (A_ID,B_ID) - table B (PK B_ID)

i.e., record in A has a number of B's associated with it though an n:m table.

I wish to filter to display only the records from table A, which have references to, say, B1, B2 AND B3 (specific records in table B).

I can make an 'OR' filter work using prefetchpath to table AB and say the B_ID must be in array containing B1, B2, B3.

But how do I specify that ALL three values from B must be associated with A?

Any help is greatly appreciated,

Best wishes,

Anders, Denmark

Hello,

you can defined your predicate in that way:

dim rpb as new relationpredicatebucket()
rpb.relation.add(AEntity.relation.ABEntityUsingA_ID,JoinHint.Inner)
rpb.relation.add(ABEntity.relation.BEntityUsingB_ID,JoinHint.Inner)

dim idBToFilter as integer()={1,8,9}
rpb.filter.add(BFields.B_ID=idBToFilter)

After that you can add your prefetch from a to ab and b, and you will have all A Entities that are linked to B entities with the specified condition.

Hi jbb,

Thanks for the fast reply. It was very straightforward to apply. However, it appears to do excactly the same as my OR'ing that I'm trying to change to AND'ing - I believe it allows for any entity from A, which has just one of the entities 1, 8 or 9 from B?

I wish to get only the entities from A that has all of the referenced B-entities.

At the risk of complicating things, I post the code with the real names (A=SupplierEntity, B=QualificationLevel, AB=SupplierQualificationLevel)

I have checket that the indices are correct; they list the indices from B (ie. QualificationLevel) and the array in my code contain them correctly.

My RelationPredicateBucket contains excactly one expression - the one we are talking about.

        ' inform LLBLGen of which related tables to use in the join.
        A.Relations.Add(SupplierEntity.Relations.SupplierQualificationLevelEntityUsingSupplierId, JoinHint.Inner)
        A.Relations.Add(SupplierQualificationLevelEntity.Relations.QualificationLevelEntityUsingQualificationLevelId, JoinHint.Inner)

        ' Run through the CheckBoxList and add only QualificationLevelId's for those checked in the GUI.
        For i = 0 To cblQL.Items.Count - 1
            If (cblQL.Items(i).Selected) Then
                QLIndices.Add(cblQL.Items(i).Value)
            End If
        Next i

        ' now QLIndices contain a list of the QualificationLevelId's that the Supplier MUST have to be listed.

        ' Add the predicate expression to bucket (applied to FilterToUse below when we are done building it).
        A.PredicateExpression.Add(QualificationLevelFields.QualificationLevelId = QLIndices)
        ' ---

Hello,

if you want all the SupplierEntity that have ALL of the QualificationLevelEntity, you can use :

for each QLIndice as integer in QLIndices 
 A.PredicateExpression.AddWithAnd(QualificationLevelFields.QualificationLevelId = QLIndices)
next

jbb wrote:

Hello,

if you want all the SupplierEntity that have ALL of the QualificationLevelEntity, you can use :

for each QLIndice as integer in QLIndices 
 A.PredicateExpression.AddWithAnd(QualificationLevelFields.QualificationLevelId = QLIndices)
next

Hi jbb,

Thanks - but no.

That still gives me SupplierEntities that has one or more of the indices. It does not give me those that has all indices.

Excactly as my starting point...

Have you tried it yourself and if so, do you have an example from, say, Northwind? Then I could look into what difference there might be...

It could look like I need to filter from the entire Supplier list on my web server :-(

I believe this was a typo:

A.PredicateExpression.AddWithAnd(QualificationLevelFields.QualificationLevelId = QLIndices)

Remove the last "s" from that line ("QLIndice" instead of "QLIndices")?

However, I think this is still not going to give you the results that you want. I believe adding all your predicates with "AND" instead of "OR" will result in never getting any records back, as a single row can't have more than one qualification level Id.

Someone actually asked a very similar question in another thread today--please see my response to that. What you are trying to do is actually much trickier than it may seem at first glance.

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8973

Phil

Hi Phil,

Thanks for joining the discussion!

psandler wrote:

I believe this was a typo: Remove the last "s" from that line ("QLIndice" instead of "QLIndices")?

My array was called QLIndices, though using plural may not be best practice ( id equals array generates 'select where id in (...)' behind the scenes in LLBL), so I think is was correct.

psandler wrote:

However, I think this is still not going to give you the results that you want. I believe adding all your predicates with "AND" instead of "OR" will result in never getting any records back, as a single row can't have more than one qualification level Id.

Someone actually asked a very similar question in another thread today--please see my response to that. What you are trying to do is actually much trickier than it may seem at first glance.

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8973

Phil

It is indeed tricky and I have had some trouble trying to construct the sql so I could present it in this thread and say "how to do this?". The only solution I've found so far is

SELECT
    *
FROM
    Supplier s
WHERE s.id in ("select those with qualificationA") and s.id in ("select those with qualificationB")... 

I'm not sure I would want such a construct in my solution ;-)

As to your suggestion in the other thread, I think

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

would exclude Orders that had all the required products but also other products, would it? And removing the COUNT requirement would mean getter the product requirements OR'ed. It's indeed closer, but still not quite what I was looking for.

I have made the quick-and-dirty solution for now (getting the gross list and filtering in code on the web server), but would like to switch to 'the correct solution' for performance and esthetics.

Maybe I was a bit fast 'dissing' your code, Phil!

If [Products] was replaced with a query that returned only the required [Products], I guess the Count would be fine - any 'extra' [Products] would be ignored.

Now, jbb, how to do this using LLBLGen !?! ;-) Since the query/subset is a dynamic construct, I wouldn't want this as a view in my sql server database.

SELECT
    orderId
FROM
    [order details] o
    INNER JOIN productSUBSET_11_42_AND_72 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

I'm definitely sure this question has come up before (not the thread phil refered to) however its very hard to find such a thread back, because what to use for the keywords?

Anyway, I came up with a quick/dirty solution, see this query:

select * from customers
where customerid 
in 
(
    select customerid from orders where employeeid =1
)
AND
customerID
IN
(
    select customerid from orders where employeeid =2
)

which gives all customers which have an order which is filed by employee 1 and an order which is filed by employee 2.

(same problem).

We support all kinds of IN operators, also 'ALL', I'm not sure if that will help in some way, but it's an interesting query and indeed much more complex tahn you would thing at first.

I vaguely remember it was solvable with a weird join, but I'm not sure...

(edit) I now see the thread from google groups phil referred to, indeed it's the same problem, and the NOT EXISTS queries are writable with LLBLGen Pro.

AndersJuul wrote:

Hi Phil,

Thanks for joining the discussion!

psandler wrote:

I believe this was a typo: Remove the last "s" from that line ("QLIndice" instead of "QLIndices")?

My array was called QLIndices, though using plural may not be best practice ( id equals array generates 'select where id in (...)' behind the scenes in LLBL), so I think is was correct.

Arg, just a quick clarification before I delve into the rest.

The code I quoted above was intended to be a quote of jbb's post, not yours:

for each QLIndice as integer in QLIndices 
A.PredicateExpression.AddWithAnd(QualificationLevelFields.QualificationLevelId = QLIndices)
next

I believe this was indeed a typo, as (I think) he intended that you would loop through the indices and add each one with an AND. So it wasn't intended as a knock on your naming conventions.

If I'm not mistaken, his code would result in adding an "IN(...)" predicate once for each item in the QLIndices collection, each of which would be ANDed together.

Phil

AndersJuul wrote:

Maybe I was a bit fast 'dissing' your code, Phil!

It'a actually more or less stolen from that google groups post I linked to, but I forgive you in any case.

AndersJuul wrote:

If [Products] was replaced with a query that returned only the required [Products], I guess the Count would be fine - any 'extra' [Products] would be ignored.

Unless I'm missing something, the code I posted does work.

If you change it to this:

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
    --"2" is the number of products we are filtering on
    COUNT(p.productId) = 2

You still get the correct orderId back.

However, I think the solution both you and Frans came up with is actually better when you are talking about creating a dynamic query:

SELECT DISTINCT
    orderId
FROM
    [order details] o
    INNER JOIN products p ON o.productid = p.productid
WHERE
    orderid in (select orderid from [order details] WHERE productid = 11)
    AND orderid in (select orderid from [order details] WHERE productid = 42)
    AND orderid in (select orderid from [order details] WHERE productid = 72)

This is very easy to do with LLBL if I'm not mistaken--just add a series of FieldCompareSetPredicates dynamically. The only downside is that this can be inefficient in SQL, especially if the number of these predicates is large.

Otis wrote:

We support all kinds of IN operators, also 'ALL',

Frans, what are you referring to when you say "ALL" is supported? Can't find it in the docs and I'm curious how it works.

Phil

I haven't seen an example of that either, in the SQL standard, there's an ALL operator. Sqlserver for example supports that, the thing is.. it might be it can be used for this, but I'm not sure . I just added code to support all these operators, perhaps there's some example online which uses ALL...