Hello

I'm struggling to explain this one, so I'll try to explain exactly what I'm doing... here goes.

My DB basically has this heirarchy:

Product -> Product Type -> ProductTypeOptionProfile -> ProductOptionProfileValues

So my Product Option Profile might be called “green xl jumper” and the optionprofile values might be 32 (green) 75 (xl) and 293 (jumper).

When I write my filter, I can do:

filter.addwithor(ProductOptionProfileValueFields.TypeValueID = 32) 

and this will get me the items that are green OR xl or a jumper. I can use if statements to get around this at the moment but obviously long-term this isn’t going to be ideal.... what i want to do is this:

filter.add(ProductTypeOptionProfileEntity.ProductOptionProfileValues[0].TypeValueID = 32);
filter.add(ProductTypeOptionProfileEntity.ProductOptionProfileValues[1].TypeValueID = 75);
filter.add(ProductTypeOptionProfileEntity.ProductOptionProfileValues[2].TypeValueID = 293);

Does that make sense? Hopefully it does

Many (many) thanks

Darren

Hi Darren. Only to clarify this, would you like:

A. Retrieve all products where optionprofile values CONTAINS any of those values (32 OR 75 OR 293)?

or,

B. Retrieve all products where optionprofile values CONTAINS 32 AND 75 AND 293?

or,

C. Retrieve all products where optionprofile values CONTAINS 32 AND 75 AND 293 in that exactly order?

Hi daelmo

I'm really sorry it has been so long since I got back to you - a combination of illness and heavy workload has taken me away from this project and I'm just getting back to it now.

I would like to get all the nodes where the option profile contains those three values - if I can do it in that order, then even better (perfect!!).

As I have found, adding the "addwithand" doesn't seem to work for me.

Many thanks for your help

Darren

Generally with these more complicated things, you should try to do it in SQL first, then adapt it.

I was thinking of something along the lines of: filter1.add(ProductOptionProfileValueFields.TypeValueID = 32) filter1.addwithand(ProductOptionProfileValueFields.PrimaryKeyField = 1) filter2.add(ProductOptionProfileValueFields.TypeValueID = 75) filter2.addwithand(ProductOptionProfileValueFields.PrimaryKeyField = 2) filter3.add(ProductOptionProfileValueFields.TypeValueID = 239) filter3.addwithand(ProductOptionProfileValueFields.PrimaryKeyField = 3) filter.add(filter1) filter.addwithand(filter2) filter.addwithand(filter3)

but that wouldn't work!!!

Depending on how much you can refactor the database an alternative would be a bitwise comparison operator. However this requires you you refactor your TypeValueIds so they are exponential (0,1,2,4, and change how the values are stored in the ProductOptionProfileValues table. Then you could write a bitwise operator to filter the values TypeValueId = 7 would pull records with IDs = 1, 2 & 4.

This is where I first encountered the idea http://sqlblog.com/blogs/denis_gobo/archive/2007/05/29/test.aspx. Maybe it would work in your scenario. I haven't needed to implment this in any of my solutions yet, but I find the approach very interesting.

Would you please post the exact SQL Query that you want to execute? Thanks.

This is a killer SQL query, even though the question seems simple. In for a ride? Read this groups post by Celko

These queries are doable with llblgen pro, but it's not trivial. Please let us know which SQL query, build using the knowledge of the post linked above will return the rows you want so we can help you write the C# code for it

Hi All

Sorry for the late reply - my DSL has been down and BT have only just got me re-connected. (Web developer with no web access.... )

With regards to the SQL I would LIKE to execute, here's what it looks like:

SELECT   dbo.Product.ProductID, dbo.Product.Name, dbo.ProductTypeOptionAttributeCombination.CombinationID, dbo.ProductTypeOptionAttributeCombination.Name AS Expr1, 
                      dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeCombinationID, 
                      dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeID, dbo.ProductTypeOptionAttribute.Name AS Expr2
FROM         dbo.Product INNER JOIN
                      dbo.ProductTypeOptionAttributeCombination ON dbo.Product.ProductID = dbo.ProductTypeOptionAttributeCombination.ProductID INNER JOIN
                      dbo.ProductTypeOptionAttributeCombinationProfile ON 
                      dbo.ProductTypeOptionAttributeCombination.CombinationID = dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeCombinationID INNER JOIN
                      dbo.ProductTypeOptionAttribute ON 
                      dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeID = dbo.ProductTypeOptionAttribute.AttributeID INNER JOIN
                      dbo.ProductTypeOptions ON dbo.ProductTypeOptionAttribute.OptionID = dbo.ProductTypeOptions.OptionID

Now, I appreciate that this may mean nothing to you, and if it helps for me to upload some SQL to create the tables and insert some sample data, I can do.

• I've not put the WHERE clause on this *

I have attached a CSV of a sample dataset - this is in fact for a database of tyres - so each tyre will have four set 'OptionAttribute' entries against the 'Combination'.

So if you look at the first line, CombinationID 37702 would be a tyre sized 0/0/0/H.

In my LLBL Filter, I would like to get all the OptionCombination entities where it has OptionAttribute entities with the ID 31, 1, 113 and 147.

I appreciate that this example using tyres might be a bit confusing as it isn't very intuitiive. If you would like me to create an example using shoes (for example) let me know and I will put something together.

Many thanks for your assistance!

Darren

• I've not put the WHERE clause on this *

You have dropped the important part, the reason behind asking to supply th SQL. Otherwise it would be just a DynamicList with some relations added to define the JOINS.

In my LLBL Filter, I would like to get all the OptionCombination entities where it has OptionAttribute entities with the ID 31, 1, 113 and 147.

Is this a different query than the posted one. As it seems here that you want to fetch OptionCOmbination entities only without fields from other entities, right?

The 'ProductTypeOptionAttributeCombinationProfile' is where I say each product has Attribute X Y or Z (i.e. in this isntance a tyre sized 0, 0, 0 H - or it may be a red XL jumper...)

So, I would like to get a ProductEntityCollection of all the Products where:

dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeID = Val1
AND
dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeID = Val2
AND
dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeID = Val3
AND
dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeID = Val4

When I tried to do this in my original filter (of which i shortened in my first post to make the syntax simpler simpler):

filter.Add(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId == Int32.Parse(sWidth));
filter.AddWithAnd(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId == Int32.Parse(sProfile));
filter.AddWithAnd(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId == Int32.Parse(sSize));
filter.AddWithAnd(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId == Int32.Parse(sRating));

I am presuming that this doesn't work because it is looking at having the ProductTypeOptionAttributeId match all four values. What I would like to do is something like this:

filter.Add(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId[0] == Int32.Parse(sWidth));
filter.AddWithAnd(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId[1] == Int32.Parse(sProfile));
filter.AddWithAnd(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId[2] == Int32.Parse(sSize));
filter.AddWithAnd(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId[3] == Int32.Parse(sRating));

I'm sure there is a simple way of doing this search, I think the main problem is me relaying exactly what I want this to do

Thanks very much for your help and patience

Darren

Is this the query you want then?

SELECT 
        dbo.Product.ProductID, 
        dbo.Product.Name, 
        dbo.ProductTypeOptionAttributeCombination.CombinationID, 
        dbo.ProductTypeOptionAttributeCombination.Name AS Expr1,
        dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeCombinationID,
        dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeID, 
        dbo.ProductTypeOptionAttribute.Name AS Expr2
FROM    dbo.Product 
            INNER JOIN dbo.ProductTypeOptionAttributeCombination 
                ON dbo.Product.ProductID = dbo.ProductTypeOptionAttributeCombination.ProductID 
            INNER JOIN dbo.ProductTypeOptionAttributeCombinationProfile 
                ON dbo.ProductTypeOptionAttributeCombination.CombinationID = dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeCombinationID 
            INNER JOIN dbo.ProductTypeOptionAttribute 
                ON dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeID = dbo.ProductTypeOptionAttribute.AttributeID 
            INNER JOIN dbo.ProductTypeOptions 
                ON dbo.ProductTypeOptionAttribute.OptionID = dbo.ProductTypeOptions.OptionID
WHERE
    dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeID = Val1
    AND dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeID = Val2
    AND dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeID = Val3
    AND dbo.ProductTypeOptionAttributeCombinationProfile.ProductTypeOptionAttributeID = Val

if it is that query the one you wan it, then see the end of this thread http://llblgen.com/TinyForum/Messages.aspx?ThreadID=11745 to help you understand how dynamic lists work.

Thanks Goose - that does look as though it is the code I need for this. I'll take a close look at the other thread this afternoon and let you know how I get on.

Many thanks

Darren

Thanks Goose - that does look as though it is the code I need for this.

I'll close this thread, and you may re-open it again if you post a reply.