|
|
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:
14470 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!
|
|
|
|