I'm using NHibernate 1.2.1.GA.

I have 2 simple mapped objects - countries and cities where a city has a countryId foreign key to countries.

The city object has an int property - numOfResidents.

now let's say that I would like to add calculated fields to the country object:
1. numOfCities
2. numOfSmallCities (up to 10000 residents)
3. numOfBigCities (100000+)

the question is - is it possible to add such calculated fields to my objects BUT give the developer the ability to decide which of these properties will return from the database (zero or more)?

Most of the times I would like to get only the basic country object while in other cases I will have to use the extended properties in order to get 1 or more calculated properties.

Thanks!

Hi,
If I understand correctly, these fields need to be updated each time a city is added/removed from a country (i.e. on insert/delete items in Cities table, a "update countries set numOfCities = (select count(*) from Cities where countryId = XXX) where id = XXX" SQL sentence needs to be executed.

I would typically use db triggers to do it since it's the most efficient way.
One should ask himself if storing these fields is actually required since they all can be calculated on demand using aggregate criteria
I would create a Property NumOfCities with a Getter that executes a HQL query on the cities table.

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/queryhql.html#queryhql-aggregation

You can use the following code & change the query as needed:


BR,
Shaul

[/url]

Thanks for the quick response.

Few remarks:
1. updating these calculated fields in update/delete is not relevant and I don't need this functionality.
2. Maybe the most important issue that I didn't mention is that I my result most of the time will be of type List<Country>.

For 1 country, I can get a mapped object of type Country, and then run the HQL query that you wrote below but my problem is that I need to get a List of dozens of countries and each one should have these calculated properties.

As I mentioned above, I am looking for a generic solution where I will be able to ask for list of countries and indicate which calculated properties I want to get in the returned collection (if any)...

Thanks!

Thanks for the quick response.

Few remarks:
1. updating these calculated fields in update/delete is not relevant and I don't need this functionality.
2. Maybe the most important issue that I didn't mention is that I my result most of the time will be of type List<Country>.

For 1 country, I can get a mapped object of type Country, and then run the HQL query that you wrote below but my problem is that I need to get a List of dozens of countries and each one should have these calculated properties.

As I mentioned above, I am looking for a generic solution where I will be able to ask for list of countries and indicate which calculated properties I want to get in the returned collection (if any)...

Thanks!

What difference does it make to have 1 or more objects of Country?
anyway its either you save these calculation in the DB or perform them on-the-fly.

I don't know if you can dynamically instruct NHibernate to return a property or not, but there are definitely 2 things you can do:
1) Map the Cities collection with lazy load so it's not fetched automatically.
2) Create a second diluted Country type and map only the relevant fields, without the cities collection.

Hope this helps,
Shaul