|
Pablo
User
Location: Sneek
Joined on: 21-Mar-2005 01:17:52
Posted: 81 posts
|
Hello, I'm suffering with a new dillema, hopefully someone can help me out. I have the following tables:
COUNTRY PROVINCE PLACE
The country table has a CountryId field as PK. The province table has a ProvinceId as PK, and a CountryId as FK. The place table has a PlaceId as PK, and a ProvinceId as FK.
What I'm lokking for to create is a table that contains the following:
CountryId, ProvinceCount, PlaceCount
Counting the provinces in a country, for each country I solved using one of the samples of a scalar query expression, and the AggregateFunction.Count, like this:
Code: |
ResultsetFields fields = new ResultsetFields(2); fields.DefineField(Geo_CountryFields.CountryId, 0); fields.DefineField(new EntityField("NumberOfProvinces", new scalarQueryExpression( Geo_ProvinceFields.CountryId.SetAggregateFunction(AggregateFunction.Count), (Geo_CountryFields.CountryId == Geo_ProvinceFields.CountryId))), 1);
DataTable results = new DataTable(); TypedListDAO dao = new TypedListDAO(); dao.GetMultiAsDataTable(fields, results, 0, null, null, null, true, null, null, 0, 0); |
This works fine for the province, but now I also want to include the place count. And now I'm stuck.
Any suggestions?
Regards, Paul
ps. Using SelfService and v2.6
|
|
|
Walaa
Support Team
Location:
Joined on: 21-Aug-2005 16:03:48
Posted: 14529 posts
|
Try adding the following:
Code: |
var relations = new RelationCollection(); relations.Add(Geo_PlaceEntity.Relations.ProvinceRelation...);
fields.DefineField(new EntityField("NumberOfPlaces", new scalarQueryExpression( Geo_Place.PlaceId.SetAggregateFunction(AggregateFunction.Count), (Geo_CountryFields.CountryId == Geo_ProvinceFields.CountryId), relations)), 2); |
|
|
|
Pablo
User
Location: Sneek
Joined on: 21-Mar-2005 01:17:52
Posted: 81 posts
|
hummm... sounds good. I just solved it slightly different, a bit more verbose... this propably just fixes it as well.. Thanx!
|
|
|
|