Querying localized values in NHibernate
Posted by Siim on March 30th, 2010In my previous post I talked about how to create model for localized values. Now it’s time to show how I’m querying against that model.
I display language selection to the user. So all localizable data is preferably shown in the language the user chose. When displaying persons list, for example, person name is shown in the language the user chose or the first available localized value if the localization for chosen language was not found. I’ll use HQL queries for that, which return a projection from entity to DTO to contain only data I need. HQL allows better (and easier) optimization than using criteria and it’s easier to write.
So HQL that returns persons list where first name and last name are localized, looks like this:
select distinct new PersonDataContract(person.Id, firstName.Value, lastName.Value, person.ContactCard.Phone, person.ContactCard.Mobile, person.ContactCard.Email.Address, l.id, firstName.Language.id, lastName.Language.id) from Language l, Person as person join person.FirstName tfname left outer join tfname.Localizations firstName join person.LastName tlname left outer join tlname.Localizations lastName where l.id = :langId and (firstName.Language.id = l.id or (index(firstName) = 0 and l.id not in (select loc.Language.id from Localization loc where loc.Translation = tfname))) and (lastName.Language.id = l.id or (index(lastName) = 0 and l.id not in (select loc.Language.id from Localization loc where loc.Translation = tlname))) order by lastName.Value, firstName.Value
I also fetch the language id for localized values because I want to show to the user if returned value was in language he requested.
And matching SQL from NHProf:
When an object has many localizable properties then such a query could become too complex, too many joins. In that case it may be better to use different strategy for fetching objects. We can use NHibernate’s multi queries or Futures. I’ll show you similar example, but in a bit different context.
In some situations it would be better to show to user all translations for selected language in a single list. For example when there is a language specialist to translate all values from one language to another. In that case we need to load all translations (for specified context, eg. person names) with localizations. In this case I need also to use paging to limit the result set.
I use multi queries approach here. First I fetch translations for the selected context and languages list. And for each language I load localizations for translations in separate queries. So I make total of two roundtrips to the database.
public IEnumerable<Translation> GetTranslationsBy(Type translationType, int startItemIndex, int numberOfItems)
{
// Select IDs first
var idQuery =
Session.CreateQuery("select t.id from Translation t where t.class = " + translationType.FullName)
.SetFirstResult(startItemIndex)
.SetMaxResults(numberOfItems);
var temporaryList = Session.CreateMultiQuery()
.Add(idQuery)
.Add("from Language").List();
const string queryFormat = "from Translation t left join fetch t.Localizations l where t.class = {0} and l.Language.id = :{1} and t.id in (:translationIds)";
var multiQuery = Session.CreateMultiQuery();
foreach (Language language in (ArrayList)temporaryList[1])
{
var langParamName = string.Format("lang{0}Id", language.Id);
var query = Session.CreateQuery(string.Format(queryFormat, translationType, langParamName))
.SetInt32(langParamName, language.Id);
multiQuery.Add(query);
}
multiQuery.SetParameterList("translationIds", (ArrayList) temporaryList[0]);
IEnumerable<Translation> result = new List<Translation>();
var list = multiQuery.List();
if (list.Count > 0)
{
result = (IEnumerable<Translation>)((ArrayList)list[0]).ToArray(translationType);
}
return result;
}
repository.GetTranslationsBy(typeof (PartnerNameTranslation), 0, 10);
With results:
The third query is similar to the first two, only for different language.
I’m using only the first result set from the second batch. Other queries are only for fetching all available localizations for translations and actual results they return, will be discarded. That way I pre-load all the values and therefore there is no need for lazy loading them which in turn would result in executing multiple queries to the database.
As I mentioned, the last approach may also be used when loading DTO’s, only in that case mapping to DTO should be done by the user (or use AutoMapper in some extent).
