Hello,
And thanks in advance to every one helping me. I am still an hibernate novice, so please forgive my errors. My question is the following:
i have a 4 tables in pseudo code:
hotel (long id_hotel, varchar name, etc..) primary key(id_hotel)

amenities_by_hotel(long id_hotel_ref, long id_amenity_ref) primary key(id_hotel_ref, id_amenity_ref)

amenity(long id_amenity, varchar img_path, long note) primary key(id_amenity)

amenity_details(long id_amenity_ref, int id_lang_ref, varchar name, text description) primary key (id_amenity_ref, id_lang_ref)

lang (varchar id_lang, varchar name) primary key(id_lang)

now i have the hibernate objects:
hotel.hbm.xml this one has a Set of amenity
amenity.hbm.xml this one has a Set of amenityDetails
lang.hbm.xml
amenityDetails.hbm.xml

Now my goal if to display hotels with their amenity information in the proper language, therefore i do the following query:

List hotels = session.find("from Hotel as h where h.amenitiesArray @@ '108&109' and h.city ~* '^MAR' ");

if (hotels.size() > 0) {
Iterator it = hotels.iterator();
while (it.hasNext()) {
Hotel h = (Hotel)it.next();
System.out.println("++++++ hotel selected " + h.getName() );
Set amenities = h.getAmenities();
Iterator it2 = amenities.iterator();
while (it2.hasNext()) {
Amenity am = (Amenity)it2.next();

Set amenitiesDetails = am.getAmenitiesDetails();
Iterator it3 = amenitiesDetails.iterator();
while (it3.hasNext()) {
//here we should filter by language to display the correct language
AmenitiesDetail ad = (AmenitiesDetail)it3.next();
System.out.println("Amenitiy " + ad.getName() + "/ lang ");
}
}
}
}

But this is a huge penalty, because hibernates makes a first query to get the list of hotels, then for each hotel it makes a query to get the list of amenities and then for each amenity it makes a query to get the amenity details and then i still need to filter to get the language.
As you see if i retrieve 500 hotels and each hotel has 100 amenities and every amenity is translated in 5 language, this makes 500*100+500*2 = 51 000 queries!!!!!!!!!
I mean that's huge and not optimized at all.
What i would like is:

List hotels = session.find("from Hotel as h where h.amenitiesArray @@ '108&109' and h.city ~* '^MAR' ");

if (hotels.size() > 0) {
Iterator it = hotels.iterator();
while (it.hasNext()) {
Hotel h = (Hotel)it.next();
System.out.println("++++++ hotel selected " + h.getName() );
Set amenities = h.getAmenitiesDetails();
Iterator it2 = amenities.iterator();
while (it2.hasNext()) {
FullAmenity am = (FullAmenity)it2.next();

}
}
}

In this case i have 1 query to fetch the 500 hotels and 500 queries to fetch the full amenity description for each hotel (which is a join of amenity and amenity_details), and then i could filter by language, it would be even better if i could have a map indexed by locals or language. Or another solution that would fit me would be:
h.getAmenitiesDetails(language), and this will get the correct language in one query, and fall to the default one if no result is found...


I don't know if i was very clear, but i heavily use multi language database and i couldn't figure how to do correctly this kind of request.
I need an efficient solution to handle this case, or i have to use something else as hibernate, like jdbc, in this case i will do one query for the searching and for each result one query joining three tables... But hibernate looks really nice and i would like to take advantage of it.

For information i use Postgres has database, and hibernate 2.1, i have slightly modified hibernate to handle GIST operators "@@" of postgres, but i don't if i sould submit that ...

Thanks in advance for your help

Numito

Read the reference docs about join fetching.

with fetch feature, you'll be able to load amenity set in your Hotel but not amenityDetails, not in one HQL query.

I think the best is:
1st query: something like "select hotel from Hotel as hotel fetch join hotel.amenities "
then: for each elements of amenities call
"select amenity from Amenity as amenity fetch join amenity.amenityDetails"

with this you'll have 1+n queries
n = number of amenities

the rule is: you can fetch many "to-one" associated objects but ONLY one to-many associated collection per query.

The goal is to fetch all collections, then you'll be able to work with the root hotels, their graph will be loaded.

You'll have to be an expert of "select elements(...)" and of "fetch" keyword

Thank you for your response Anthony, and Michael i have read the docs many time and go through the forum ... and sadly haven't found an acceptable solution.
Anthony i think your solution is the best i have seen so far. My question is how Hibernate user deal with multi-language database? I mean this is a common design pattern to have table "Items" that hold the id and unique information about an item, and a table ItemDetail that holds all the localized informations.
I really don't know how to find a nice solution, could i adapt the design of my databse to hold i18n data easily accessible from hibernate.
I mean i have no choice a hotel has many amenities, and an amenity has an amenity detail; and each amenity can be common to more then one hotel.
I was thinking, would it be possible to design an object that maps to two tables?
I have to find an elegant solution.

Thanks

Numa

Hi Numa,

Did you ever come up with a cunning way of handling multi-lingual objects. Surely this is a common problem and has been solved by somebody much more clever than me?!

Simon