Taking a closer look at my HQL query, I realized it's checking if a
category's ID is in the Categories collection. So I tried to instead
project the Categories collection into a collection of IDs:
"from Item i left join fetch o.Categories cats where :cid in (select
ID from elements(cats))"
but that threw an exception:
NHibernate.Hql.Ast.ANTLR.QuerySyntaxException occurred
Message=Exception of type 'Antlr.Runtime.MismatchedTokenException'
was thrown. near line 1, column 124 [from MyApp.DomainModel.Item i
left join fetch i.Categories cats where :cid in (select ID from
elements(cats))]
Source=NHibernate
QueryString=from MyApp.DomainModel.Item i left join fetch
i.Categories cats where :cid in (select ID from elements(cats))
StackTrace:
at NHibernate.Hql.Ast.ANTLR.ErrorCounter.ThrowQueryException()
at NHibernate.Hql.Ast.ANTLR.HqlParseEngine.Parse()
at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.Parse(Boolean
isFilter)
at
NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.DoCompile(IDictionary`2
replacements, Boolean shallow, String collectionRole)
at
NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.Compile(IDictionary`2
replacements, Boolean shallow)
at NHibernate.Engine.Query.HQLQueryPlan..ctor(String hql,
String collectionRole, Boolean shallow, IDictionary`2 enabledFilters,
ISessionFactoryImplementor factory)
at NHibernate.Engine.Query.HQLQueryPlan..ctor(String hql,
Boolean shallow, IDictionary`2 enabledFilters,
ISessionFactoryImplementor factory)
at
NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(String
queryString, Boolean shallow, IDictionary`2 enabledFilters)
at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(String
query, Boolean shallow)
at NHibernate.Impl.AbstractSessionImpl.CreateQuery(String
queryString)
In any case, I hope that someone can give me some tips on writing this
HQL query.
On Aug 8, 12:39 pm, HappyNomad <[email protected]> wrote:
> I have a bidirectional many-to-many relationship between Category and
> Item. I'm trying to write an efficient query that, given a category's
> ID, will initialize all items belonging to that category as well as
> initialize each of those item's Categories collections.
>
> The HQL query I wrote is:
> "from Item i left join fetch i.Categories cats where :cid in
> elements(cats)"
>
> I'm no HQL expert, so I should first ask: does this query looks okay?
>
> In any case, the resulting SQL is rather funky:
>
> select * where @p0 in (select category2_.CategoryID from eft outer
> join Category_Item categories1_ on item0_.ID=categories1_.ItemID left
> outer join Category category2_ on
> categories1_.CategoryID=category2_.ID where
>
> For readability, I replaced some details in the SELECT clause with a
> star. I don't see what comes after the last "where" since Visual
> Studio's exception dialog box (I think) cut it off. Anyway, notice
> that "left" is even spelled as "eft". What happened here?
