[2.0] left join create separate queries

oolon · **Posted:** Fri Jul 04, 2008 10:21 am

Hello,

I would like to know how is it possible to prevent the fact to create a new query for each left join in my query?

Code:

sess.CreateQuery(String.Format(@"select cust from TrasysGR.MDP.DomainModel.Customer cust
                        left outer join cust.ClassB
                        left outer join cust.ClassC
                        inner join fetch cust.Addresses ad
                        where cust.Id = {0}
                        and ad.Valid = 't'
                        ", id) ).List<Customer>()

For that NHibernate Create 3 queries. One for Address, one for ClassB and another one for ClassC.

That's unuseful. I want an only query.

And if I try to put left outer join, NHibernate says to me that not possible to put severals fetch in one query... Why?

Sorry to ask so many questions but I'm a beginner

Thank you[/quote]

wolli · **Posted:** Fri Jul 04, 2008 11:12 am

Try "fetch" on all joins:

Code:

sess.CreateQuery(String.Format(@"select cust from TrasysGR.MDP.DomainModel.Customer cust
                        left outer join fetch cust.ClassB
                        left outer join fetch cust.ClassC
                        inner join fetch cust.Addresses ad
                        where cust.Id = {0}
                        and ad.Valid = 't'
                        ", id) ).List<Customer>() 

But I found this in the docs:

Quote:

Note that, in the current implementation, only one collection role may be fetched in a query (everything else
would be non-performant).

So I assume, it's not possible to get this in a single select.

oolon · **Posted:** Fri Jul 04, 2008 11:17 am

Oki I understand now why there're saverals queries.

And I decided to use criterias because I couldn't use fetch in my CreateQuery

Code:

 Customer customerWithData = sess.CreateCriteria(typeof(Customer))
                        .SetFetchMode("ClassB", FetchMode.Eager)
                        .SetFetchMode("ClassC", FetchMode.Eager)
                        .SetFetchMode("Addresses", FetchMode.Eager)
                        // .CreateAlias("Addresses", "add")
                        .Add(Expression.Eq("Id", id))
                        //.Add(Expression.Eq("add.Valid", 't'))
                        .List<Customer>()[0];

But I don't know to load only a part of a collection. For exemple I want to only load valids addresses. I tried to use an alias and use the alias with the expression like I let in comment.

So for the moment I load every adresse. If someone can help it'll be great

Thank you

wolli · **Posted:** Fri Jul 04, 2008 11:25 am

Have a look at

Quote:

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/querycriteria.html#querycriteria-associations

especially the end of that paragraph:

Quote:

Note that the kittens collections held by the Cat instances returned by the previous two queries are not pre-filtered by the criteria! If you wish to retrieve just the kittens that match the criteria, you must use SetResultTransformer(CriteriaUtil.AliasToEntityMap).

Try and search the forum for AliasToEntityMap, I can't help you there.