Hi guys

It's a while since I've had to do this, and I've been racking my brain and just can't fathom it out!

I need to use RelationPredicateBuckets to generate the following SQL. I can build up the buckets but it's the 4 relationships that I'm struggling with.

A debt of eternity to anyone who can help!

Many thanks

Darren

SELECT DISTINCT PTOA.* 
FROM ProductTypeOptionAttribute AS PTOA
INNER JOIN ProductTypeOPtionAttributeCombinationProfile AS PTOACP ON PTOACP.ProductTypeOPtionAttributeID = PTOA.AttributeID
INNER JOIN ProductTypeOPtionAttributeCombination AS PTOAC ON PTOAC.CombinationID = PTOACP.ProductTypeOptionAttributeCombinationID
--JOIN BACK
INNER JOIN ProductTypeOPtionAttributeCombinationProfile AS PTOACP2 ON PTOAC.CombinationID = PTOACP2.ProductTypeOptionAttributeCombinationID
INNER JOIN ProductTypeOPtionAttribute AS PTOA2 ON PTOACP2.ProductTypeOPtionAttributeID = PTOA2.AttributeID
WHERE PTOA.OptionID = 59
AND PTOAC.ProductTypeID = 11
AND PTOAC.ProductID = 218
--FIRST
AND PTOA2.AttributeID = 42
AND PTOA2.OptionID = 58

If it helps, here's what we've got already... the bit that is missing is for the relationships for each of the predicates:

IPredicateExpression filter = new PredicateExpression();
filter.Add(ProductTypeOptionAttributeFields.OptionId == dl.Key);
filter.AddWithAnd(ProductTypeOptionAttributeCombinationFields.ProductTypeId == DataSource.DataItem.ProductTypeId);
filter.AddWithAnd(ProductTypeOptionAttributeCombinationFields.ProductId == DataSource.ProductID);
bucket.PredicateExpression.Add(filter);

bucket.Relations.Add(ProductTypeOptionAttributeEntity.Relations.ProductTypeOptionAttributeCombinationProfileEntityUsingProductTypeOptionAttributeId, JoinHint.Inner);
bucket.Relations.Add(ProductTypeOptionAttributeCombinationProfileEntity.Relations.ProductTypeOptionAttributeCombinationEntityUsingProductTypeOptionAttributeCombinationId, JoinHint.Inner);

var filtered = _dropdowns.Where(k => ((DropDownList)k.Value[1]).SelectedValue != "-1" && k.Key != dl.Key);
foreach (var filteredDdl in filtered)
{

    IPredicateExpression subFilter = new PredicateExpression();
    subFilter.AddWithAnd(ProductTypeOptionAttributeFields.AttributeId == int.Parse(((DropDownList)filteredDdl.Value[1]).SelectedValue));
    subFilter.AddWithAnd(ProductTypeOptionAttributeFields.OptionId == filteredDdl.Key);
    bucket.PredicateExpression.AddWithAnd(subFilter);       
}


ProductTypeOptionAttributeCollection attrs = new ProductTypeOptionAttributeCollection();
attrs.GetMulti(bucket.PredicateExpression, -1, null, bucket.Relations);

Long table names I hear you say.... sorry!

Hi there.

You should use aliases. Use the relations.add overload to specify your query aliases. Then in your fields in the predicate use the .SetObjectAlias to specfy the alias as well.

Give it a try and come back here if you need further help.

Hi Daelmo

That's great - thanks for the pointer - I've seen some examples on here on how to use alias with DataTables but not EntityCollections.

Could you point me in the right direction please of an example?

Thanks

Darren

Hi Darren,

This is an approximate code that shows how to use aliases in your code:

IPredicateExpression filter = new PredicateExpression();


bucket.Relations.Add(ProductTypeOptionAttributeEntity.Relations.ProductTypeOptionAttributeCombinationProfileEntityUsingProductTypeOptionAttributeId, 
   "PTOA", "PTOACP", JoinHint.Inner);

bucket.Relations.Add(ProductTypeOptionAttributeCombinationProfileEntity.Relations.ProductTypeOptionAttributeCombinationEntityUsingProductTypeOptionAttributeCombinationId, 
"PTOACP", "PTOAC", JoinHint.Inner);

bucket.Relations.Add(ProductTypeOptionAttributeCombinationEntity.Relations.ProductTypeOptionAttributeCombinationProfileEntityUsingCombinationID, 
"PTOAC", "PTOACP2", JoinHint.Inner);

bucket.Relations.Add(ProductTypeOptionAttributeCombinationProfileEntity.Relations.ProductTypeOptionAttributeEntityUsingProductTypeOptionAttributeId, 
   "PTOACP2", "PTOA2", JoinHint.Inner);


filter.Add(ProductTypeOptionAttributeFields.OptionId.SetObjecAlias("PTOA") == dl.Key);

filter.AddWithAnd(ProductTypeOptionAttributeCombinationFields.ProductTypeId.SetObjectAlias("PTOAC") == DataSource.DataItem.ProductTypeId);

filter.AddWithAnd(ProductTypeOptionAttributeCombinationFields.ProductId.SetObjectAlias("PTOAC") == DataSource.ProductID);

bucket.PredicateExpression.Add(filter);

....

ProductTypeOptionAttributeCollection attrs = new ProductTypeOptionAttributeCollection();
attrs.GetMulti(bucket.PredicateExpression, -1, null, bucket.Relations);

BTW, I saw this snippet that is part of you predicate expression. But I fail to see what are you doing there. You are adding predicate expressions on the same field multiple times and using "AND". This will return 0 results if you have more than one selected value in the dropDownList. Am I wrong? Anyway this is not part of the original issue. I am just curious.

Hi daelmo

That code has been a brilliant help - thank you! We just needed to tweak the start and end alias a little but it's done the trick.

We're essentially joining (n) instances of a table to a master table and then filtering each of the tables - in this instance four tables to find matching product options. It's not the easiest thing to explain to be honest!!

However, we're happy an the client will be very happy to have this resolved

Thanks for the help - It's massively appreciated.

Darren