Hi Guys,

I am trying to convert this statement:

select g.PriceEach, b.priceeach, h.name from metadataproductitemselected a left join price b on a.id = b.MetaDataProductItemSelected and b.country = 45 join metadatamultiselect c on a.MetaDataMultiSelect = c.ID join metadataitem f on f.ID = c.metadataitem and f.selectfororder = 1 join country h on h.ID = 45 left join price g on a.id = g.MetaDataProductItemSelected and g.country = h.countrycurrencytouse where a.product = 586

It is OK except for the two bits:

1, join country h on h.ID = 45

and 2, left join price g on a.id = g.MetaDataProductItemSelected and g.country = h.countrycurrencytouse

as I am not sure how to make a relation without using entity relations and then to have an AND with another relation attached rather than a predicateexpression.

Hope someone can help?

Thanks, Mike

You may want to check out this section of the manual Generated code - Advanced filter usage, Custom filters for EntityRelations. This will help get you started with these two scenarios. If you have any problems then let us know, and if you can post some of the code that is giving you issues we can give a better example.

Hi,

I studied this section before I posted! I can see how to add an AND to the join, but not how to make this 'and' another relation rather than a predicate. The second problem is how to make a relation just a predicate, i know i can use CustomFilterReplacesOnClause but how do i make the relation in the first place?

For your info, here is the code before i changed it to include my problem sql!

ResultsetFields fields = new ResultsetFields(9);
fields.DefineField(MetaDataMultiSelectFieldIndex.ID, 0, "MetaDataMultiSelectID", "c");
fields.DefineField(MetaDataMultiSelectFieldIndex.Order, 1, "MetaDataMultiSelectOrder", "c");
fields.DefineField(MetaDataMultiSelectFieldIndex.Name, 2, "MetaDataMultiSelectName", "c");
fields.DefineField(MetaDataItemFieldIndex.Order, 3, "MetaDataItemOrder", "d");
fields.DefineField(MetaDataItemFieldIndex.Name, 4, "MetaDataItemName", "d");
fields.DefineField(MetaDataItemFieldIndex.ID, 6, "MetaDataItemID", "d");
fields.DefineField(PriceFieldIndex.PriceEach, 5, "PriceEach", "b");
fields.DefineField(PriceFieldIndex.PriceEach, 8, "PriceEachDefault", "f");
fields.DefineField(CountryFieldIndex.CountryCurrencyToUse, 7, "DefaultCountry", "e");


IPredicateExpression countryWhere = new PredicateExpression();
countryWhere.Add(PredicateFactory.CompareValue(PriceFieldIndex.Country,ComparisonOperator.Equal,countryID,"b"));

IPredicateExpression selectForOrderWhere = new PredicateExpression();
selectForOrderWhere.Add(PredicateFactory.CompareValue(MetaDataItemFieldIndex.SelectForOrder,ComparisonOperator.Equal,true,"d"));

IRelationCollection rels = new RelationCollection();
rels.Add(MetaDataProductItemSelectedEntity.Relations.PriceEntityUsingMetaDataProductItemSelected,"a","b",JoinHint.Left).CustomFilter = countryWhere;
rels.Add(MetaDataProductItemSelectedEntity.Relations.MetaDataMultiSelectEntityUsingMetaDataMultiSelect,"a","c",JoinHint.None);
rels.Add(MetaDataMultiSelectEntity.Relations.MetaDataItemEntityUsingMetaDataItem,"c","d",JoinHint.None).CustomFilter = selectForOrderWhere;     

IPredicateExpression where = new PredicateExpression();
where.Add(PredicateFactory.CompareValue(MetaDataProductItemSelectedFieldIndex.Product,ComparisonOperator.Equal,productID,"a"));

DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, where, rels, true, null, null, 0, 0);

1, join country h on h.ID = 45

Add a predicate using the CustomFilter property of the relation, this will add the AND h.ID = 45 predicate to the ON clause

and 2, left join price g on a.id = g.MetaDataProductItemSelected and g.country = h.countrycurrencytouse

Same story. g.country=h.countrycurrencytouse is a predicate you should add (fieldcompareexpression) through the CustomFilter property of the relation

However, you can also move these filters to the where clause.