let me state that im no saint, i know i'm doing stuff that NHibernate is
only doing me a favor by working with.
*the code*:
return Session.CreateSQLQuery(
@"
with Hierachy(Id, Title,
Summary,Purpose,IsDeleted,CreatedAt,Owner,Parent, Level)
as
(
select Id, Title,
Summary,Purpose,IsDeleted,CreatedAt,Owner,Parent,
item0_1_.Type as Type4_, item0_2_.Type as Type5_,
item0_2_.Basis as Basis5_,
item0_2_.NeededForConversion as NeededFo4_5_,
case when item0_1_.ItemId is not null then 1 when
item0_2_.ItemId is not null then 2 when item0_.Id is not null then 0 end as
clazz_,
0 as Level
from Items e left outer join ArguableItems item0_1_ on
e.Id=item0_1_.ItemId left outer join NonArguableItems item0_2_ on
e.Id=item0_2_.ItemId
where e.Id = :rootid
union all
select e.Id, e.Title,
e.Summary,e.Purpose,e.IsDeleted,e.CreatedAt,e.Owner,e.Parent,
item0_1_.Type as Type4_, item0_2_.Type as Type5_,
item0_2_.Basis as Basis5_,
item0_2_.NeededForConversion as NeededFo4_5_,
case when item0_1_.ItemId is not null then 1 when
item0_2_.ItemId is not null then 2 when item0_.Id is not null then 0 end as
clazz_,
eh.Level + 1
from Items e left outer join ArguableItems item0_1_ on
e.Id=item0_1_.ItemId left outer join NonArguableItems item0_2_ on
e.Id=item0_2_.ItemId
inner join Hierachy eh
on e.Parent = eh.Id
)
select Id, Title,
Summary,Purpose,IsDeleted,CreatedAt,Owner,Parent,Type4_,Type5_, Basis5_,
NeededFo4_5_, clazz_
from Hierachy
where Level > 0 AND Title like :searchtext
").AddEntity(typeof(Item)).SetInt32("rootid",rootItemId).SetString("searchtext",
"%"+text+"%").List<Item>();
*about the mapping*
i have 1 abstract class (Item), and 2 subclasses to that. and i'm guessing
the inheritance is the problem.
i'm trying to do an sql recursive call, and i've tried plugging the
NHibernate inheritance SQL code into it (after i found out that clazz_ is a
field responsible for that)
eventually i've hit a wall by the fact that SQL server doesnt allow Left
Outer Join :(
On Fri, May 22, 2009 at 10:44 PM, Ayende Rahien <[email protected]> wrote:
>
> Can you show what the mapping and code are?
>
> On May 22, 6:10 pm, "Dotan N." <[email protected]> wrote:
> > hey all, i think i'm not doing something correctly. excuse me for the
> trace
> > but does anyone have an idea where i should start looking?
> >
> > System.IndexOutOfRangeException: clazz_
> > at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
> > at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
> > at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name)
> > at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
> > at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name,
> > ISessionImplementor session, Object owner)
> > at NHibernate.Loader.Loader.GetInstanceClass(IDataReader rs, Int32 i,
> > ILoadable persister, Object id, ISessionImplementor session)
> > at NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i,
> > ILoadable persister, EntityKey key, LockMode lockMode, EntityKey
> > optionalObjectKey, Object optionalObject, IList hydratedObjects,
> > ISessionImplementor session)
> > at NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[]
> persisters,
> > EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey,
> > LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session)
> > at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet,
> > ISessionImplementor session, QueryParameters queryParameters, LockMode[]
> > lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects,
> EntityKey
> > [] keys, Boolean returnProxies)
> > at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session,
> > QueryParameters queryParameters, Boolean returnProxies)
> > at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(
> > ISessionImplementor session, QueryParameters queryParameters, Boolean
> > returnProxies)
> > at NHibernate.Loader.Loader.DoList(ISessionImplementor session,
> > QueryParameters queryParameters)
> > NHibernate.ADOException: could not execute query
> > [
> > with Hierachy(Id, Title,
> > Summary,Purpose,IsDeleted,CreatedAt,Owner,Parent, Level)
> > as
> > (
> > select Id, Title,
> > Summary,Purpose,IsDeleted,CreatedAt,Owner,Parent, 0 as Level
> > from Items e
> > where e.Id = ?
> > union all
> > select e.Id, e.Title,
> > e.Summary,e.Purpose,e.IsDeleted,e.CreatedAt,e.Owner,e.Parent, eh.Level
> > + 1
> > from Items e
> > inner join Hierachy eh
> > on e.Parent = eh.Id
> > )
> > select Id, Title,
> > Summary,Purpose,IsDeleted,CreatedAt,Owner,Parent
> > from Hierachy
> > where Level > 0 AND Title like ?
> > ]
> > Name:rootid - Value:57 Name:searchtext - Value:%Title%
> > [SQL:
> > with Hierachy(Id, Title,
> > Summary,Purpose,IsDeleted,CreatedAt,Owner,Parent, Level)
> > as
> > (
> > select Id, Title,
> > Summary,Purpose,IsDeleted,CreatedAt,Owner,Parent, 0 as Level
> > from Items e
> > where e.Id = ?
> > union all
> > select e.Id, e.Title,
> > e.Summary,e.Purpose,e.IsDeleted,e.CreatedAt,e.Owner,e.Parent, eh.Level
> > + 1
> > from Items e
> > inner join Hierachy eh
> > on e.Parent = eh.Id
> > )
> > select Id, Title,
> > Summary,Purpose,IsDeleted,CreatedAt,Owner,Parent
> > from Hierachy
> > where Level > 0 AND Title like ?
> > ]
> > at NHibernate.Loader.Loader.DoList(ISessionImplementor session,
> > QueryParameters queryParameters)
> > at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor
> session
> > , QueryParameters queryParameters)
> > at NHibernate.Loader.Loader.List(ISessionImplementor session,
> > QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes)
> > at NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor
> session,
> > QueryParameters queryParameters)
> > at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery,
> > QueryParameters queryParameters, IList results)
> > at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec,
> > QueryParameters queryParameters, IList results)
> > at NHibernate.Impl.SessionImpl.List<T>(NativeSQLQuerySpecification spec,
> > QueryParameters queryParameters)
> > at NHibernate.Impl.SqlQueryImpl.List<T>()
> >
> > thanks.
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
