Hi - I am trying to build a fairly complex query to achieve the following;

I have set of reference tables that define a building structure (building, floor, area, item). I have a *register* which has events that are linked to the lowest level in the building structure - i.e. items. The linking table is RegisterItem and the event table is RegisterItemEvent

A single register can contain 1000's of buildings and therefore many 1000's of register items/events. I want to add a row limiter to my query, since it can take a long time to return for lots of data. However this limit needs to apply to the buildings and not the RegisterItemEvents, which is the object the query is returning.

So I have tried to build an *in* clause into the query whose subquery only returns a limited number of buildings - using the *top* statement. See below for the query. This query works fine in native SQL but it seems HQL doesn't support *top* unless used via the SetMaxResults() method in IQuery.

Is there anyway to achieve what I am after here in a single HQL query?



Any help will be warmly appreciated!


NHibernate version: 1.2.1.4000

Full stack trace of any exception that occurs:

NHibernate.QueryException was unhandled
Message="undefined alias or unknown mapping: top [select rie.LevelOfIdentification, rie.RegisterItem.Item.Area.Floor.Building.Id, rie.RegisterItem.Item.Area.Floor.Building.Name, rie.RegisterItem.Item.Area.Floor.Id, rie.RegisterItem.Item.Area.Floor.Name, rie.RegisterItem.Item.Area.Id, rie.RegisterItem.Item.Area.Name, rie.RegisterItem.Item.Id, rie.RegisterItem.Item.Name from ngNINE.Amianto.Client.Domain.RegisterItemEvent rie where rie.RegisterItem.Register.Id = :registerId and rie.RegisterItem.DeleteFl = false and rie.Dttm = (select max(inrrie.Dttm) from ngNINE.Amianto.Client.Domain.RegisterItemEvent inrrie where inrrie.RegisterItem.Id = rie.RegisterItem.Id and inrrie.Dttm <= :dttm) and rie.RegisterItem.Item.Area.Floor.Building.Id in ( select distinct top 100 sub.RegisterItem.Item.Area.Floor.Building.Id from ngNINE.Amianto.Client.Domain.RegisterItemEvent sub where sub.RegisterItem.Register.Id = :registerId and sub.RegisterItem.DeleteFl = false and sub.Dttm = (select max(inrsub.Dttm) from ngNINE.Amianto.Client.Domain.RegisterItemEvent inrsub where inrsub.RegisterItem.Id = sub.RegisterItem.Id and inrsub.Dttm <= :dttm) ) order by rie.RegisterItem.Item.Area.Floor.Building.Id, rie.RegisterItem.Item.Area.Floor.Id, rie.RegisterItem.Item.Area.Id ]"
Source="NHibernate"
QueryString="select rie.LevelOfIdentification, rie.RegisterItem.Item.Area.Floor.Building.Id, rie.RegisterItem.Item.Area.Floor.Building.Name, rie.RegisterItem.Item.Area.Floor.Id, rie.RegisterItem.Item.Area.Floor.Name, rie.RegisterItem.Item.Area.Id, rie.RegisterItem.Item.Area.Name, rie.RegisterItem.Item.Id, rie.RegisterItem.Item.Name from ngNINE.Amianto.Client.Domain.RegisterItemEvent rie where rie.RegisterItem.Register.Id = :registerId and rie.RegisterItem.DeleteFl = false and rie.Dttm = (select max(inrrie.Dttm) from ngNINE.Amianto.Client.Domain.RegisterItemEvent inrrie where inrrie.RegisterItem.Id = rie.RegisterItem.Id and inrrie.Dttm <= :dttm) and rie.RegisterItem.Item.Area.Floor.Building.Id in ( select distinct top 100 sub.RegisterItem.Item.Area.Floor.Building.Id from ngNINE.Amianto.Client.Domain.RegisterItemEvent sub where sub.RegisterItem.Register.Id = :registerId and sub.RegisterItem.DeleteFl = false and sub.Dttm = (select max(inrsub.Dttm) from ngNINE.Amianto.Client.Domain.RegisterItemEvent inrsub where inrsub.RegisterItem.Id = sub.RegisterItem.Id and inrsub.Dttm <= :dttm) ) order by rie.RegisterItem.Item.Area.Floor.Building.Id, rie.RegisterItem.Item.Area.Floor.Id, rie.RegisterItem.Item.Area.Id "
StackTrace:
at NHibernate.Hql.Classic.PathExpressionParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.ParserHelper.Parse(IParser p, String text, String seperators, QueryTranslator q)
at NHibernate.Hql.Classic.SelectParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.ClauseParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.ClauseParser.End(QueryTranslator q)
at NHibernate.Hql.Classic.PreprocessingParser.End(QueryTranslator q)
at NHibernate.Hql.Classic.ParserHelper.Parse(IParser p, String text, String seperators, QueryTranslator q)
at NHibernate.Hql.Classic.QueryTranslator.Compile()
at NHibernate.Hql.Classic.QueryTranslator.Compile(QueryTranslator superquery)
at NHibernate.Hql.Classic.WhereParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.ClauseParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.PreprocessingParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.ParserHelper.Parse(IParser p, String text, String seperators, QueryTranslator q)
at NHibernate.Hql.Classic.QueryTranslator.Compile()
at NHibernate.Hql.Classic.QueryTranslator.Compile(IDictionary replacements, Boolean scalar)
at NHibernate.Impl.SessionFactoryImpl.GetQuery(String queryString, Boolean shallow, IDictionary enabledFilters)
at NHibernate.Impl.SessionImpl.GetQueries(String query, Boolean scalar)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters, IList results)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters)
at NHibernate.Impl.QueryImpl.List()
at ngNINE.Common.Domain.SessionWrapper.ExecuteHQL(IQuery query) in d:\Amianto\Common\src\ngNINE\Common\Domain\SessionWrapper.cs:line 217
at ngNINE.Amianto.Client.Controls.RegisterUI.RegisterSearchUI.ExecuteSearch() in D:\Amianto\Amianto\src\ngNINE\Amianto\Client\Controls\RegisterUI\RegisterSearchUI.cs:line 606
at ngNINE.Amianto.Client.Controls.RegisterUI.RegisterSearchUI.btnSearch_Click(Object sender, EventArgs e) in D:\Amianto\Amianto\src\ngNINE\Amianto\Client\Controls\RegisterUI\RegisterSearchUI.cs:line 1027
at System.Windows.Forms.Control.OnClick(EventArgs e)
at Janus.Windows.EditControls.UIButton.OnClick(EventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at ngNINE.Amianto.Client.Main.Program.Main() in D:\Amianto\Amianto\src\ngNINE\Amianto\Client\Main\Program.cs:line 34
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

Name and version of the database you are using:

Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86) Feb 26 2008 18:15:01 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

Criteria criteria=session.createCriteria(Item.class);
criteria.addOrder(Order.asc("name"));
criteria.setFirstResult(100);
criteria.setMaxResults(50);
List pageResults=criteria.list();

Thanks Gonzalo - but I am not quite sure what you mean. I have had a look at the docs and tried a few things but can't seem to see how it should work.

I think the ICriteria might be able to help - I am just unsure how to use it in this case.

Here is the raw SQL query I am trying to replicate...



Note: I have hardcoded the RegId and Dttm parameters to 1 and getdate() respectively.

Well, that is a sizable query with several subselects on the same tables.
I recommend that you read the chapter on the documentation about Criteria queries

http://www.hibernate.org/hib_docs/refer ... teria.html

especially the sections dealing with alias and subqueries, and start experimenting with them, first without the self - joins, then with them.

You will have first to get the mapping files for the tables you use right.

Then you will find out that working with Criteria queries is quite comfortable, especially since it saves you from writing all that join information repeatedly in SQL queries.

But no answer I could discuss here replaces the fact that you have to get a good understanding of what Criteria are, at least by reading the indicated chapter.

Good luck.