HQL Query Question

[Nick Parker]

We have an HQL query that is loading an object graph of Questions and
their associated answers, I am attempting to minimize the queries
issued
to the database and have run into an issue. The below query returns
26 records from our testing database, representing 26 valid questions
within our system:

from Question q left join fetch q.QuestionType left join fetch
q.Content qc left join fetch qc.Resources qcr
where qcr is null or qcr.Language.Id = :CurrentLanguageId or
qcr.Language.Id = :DefaultLanguageId

However, as soon as I attempt to eager fetch the
QuestionXRAnswerOptions, and it's associated content resource entries
(the associated entities that represent our answers in relation to a
question), I begin to get a cartesian result of duplicate questions
entities:

from Question q left join fetch q.QuestionType left join fetch
q.Content qc left join fetch qc.Resources qcr left join fetch
q.QuestionXRAnswerOptions qao left join fetch qao.AnswerOption ao left
join fetch ao.Content aoc left join fetch aoc.Resources aocr
where
(qcr.Language.Id = :CurrentLanguageId and aocr.Language.Id
= :CurrentLanguageId) or
(qcr.Language.Id = :DefaultLanguageId and aocr.Language.Id
= :DefaultLanguageId) or
(q is null) or
(qao is null)

The QuestionXRAnswerOptions is defined as a map, any suggestions with
the query to eliminate the duplicates? Thanks in advance.

[Ayende Rahien]

Use multi criteria or multi query instead:
http://www.ayende.com/Blog/archive/2007/05/20/NHibernate-Multi-Criteria.aspx
http://www.ayende.com/Blog/archive/2007/06/22/Syntax-Multi-Something.aspx
http://www.ayende.com/Blog/archive/2007/06/20/Efficently-loading-deep-object-graphs.aspx

[Tim Gifford]

Nick,

Have you tried using an inner join between the QuestionXRAnswerOption
and AnswerOption? The left join will return a question for each
answeroption in the relationship.

-Tim Gifford

[Nick Parker]

Ayende,

Thanks for the tip, it seems as if the CreateMultiQuery would be the
route to go, however when I separated the query into two pieces I just
end up with two resultsets, one with the 26 records I am expecting and
the other with the cartesian resultset that is invalid. It also
builds separate object graphs separately from each query, which is not
what I am looking for in this case, just one deeply nested object
graph collection.

On Mar 31, 3:02 pm, "Ayende Rahien" <aye...@ayende.com> wrote:
> Use multi criteria or multi query instead:http://www.ayende.com/Blog/archive/2007/05/20/NHibernate-Multi-Criter...http://www.ayende.com/Blog/archive/2007/06/22/Syntax-Multi-Something....http://www.ayende.com/Blog/archive/2007/06/20/Efficently-loading-deep...

[Ayende Rahien]

You use the first result to get what you want, the second to eager load everything else.
Can you show your multi query code?

[Nick Parker]

Gotcha, I spoke to early - that worked perfectly! Thanks again.

On Mar 31, 3:41 pm, "Ayende Rahien" <aye...@ayende.com> wrote:
> You use the first result to get what you want, the second to eager load
> everything else.
> Can you show your multi query code?
>
> On Mon, Mar 31, 2008 at 11:35 PM, Nick Parker <ni...@developernotes.com>
> wrote:
>
>
>
> > Ayende,
>
> > Thanks for the tip, it seems as if the CreateMultiQuery would be the
> > route to go, however when I separated the query into two pieces I just
> > end up with two resultsets, one with the 26 records I am expecting and
> > the other with the cartesian resultset that is invalid. It also
> > builds separate object graphs separately from each query, which is not
> > what I am looking for in this case, just one deeply nested object
> > graph collection.
>
> > On Mar 31, 3:02 pm, "Ayende Rahien" <aye...@ayende.com> wrote:
> > > Use multi criteria or multi query instead:

> >http://www.ayende.com/Blog/archive/2007/05/20/NHibernate-Multi-Criter....
> > ..

[Ayende Rahien]

Make _sure_ that you don't have a cartesian product on the second part. That can KILL you on production

[Ray Houston]

@Nick - any chance you can post your multi query that you came up with?

[Nick Parker]

Ray,

From memory it looks something like this:

IMultiQuery query // create multiquery from session management

query.Add("from Question q left join fetch q.QuestionType left join fetch q.Content qc left join fetch qc.Resources qcr where qcr.Language.Id = :CurrentLanguageId or qcr.Language.Id = :DefaultLanguageId");

query.Add("from Question q left join q.QuestionXRAnswerOptions qao left join fetch qao.CommentLabel qcl left join fetch qlc.Resources left join fetch qao.AnswerOptions ao left join fetch ao.Content aoc left join fetch aoc.Resources aocr where aocr.Language.Id = :CurrentLanguageId or aocr.Language.Id = :DefaultLanguageId");

query.SetParameter("LanguageId", languageId);

query.SetParameter("DefaultLanguageId", defaultLanguageId);

IList results = query.List();

Then of course I only hand back results[0] to the caller.  HTH.

--
Nick Parker
www.developernotes.com