As criteria does not support explicit joins (joins other than the ones in the mapping (one to one, one to many etc)
Tuna Toksöz Eternal sunshine of the open source mind. http://devlicio.us/blogs/tuna_toksoz http://tunatoksoz.com http://twitter.com/tehlike On Fri, Jun 5, 2009 at 3:50 PM, Tuna Toksoz <[email protected]> wrote: > You cannot do the with criteria > > > > Tuna Toksöz > Eternal sunshine of the open source mind. > > http://devlicio.us/blogs/tuna_toksoz > http://tunatoksoz.com > http://twitter.com/tehlike > > > > > > On Fri, Jun 5, 2009 at 2:58 PM, jcarlos <[email protected]>wrote: > >> >> Hello, >> >> I know you’re a very busy person but thanks for taking time to read my >> email. Here’s my problem. I can’t transform this SQL query into its >> DetachedCriteria equivalent. >> >> select m.Id as MovementId, m.CountryCode, m.ModuleFlag, m.Gateway, >> m.MawbNumber as TDocNumber, m.MovementNumber, m.DepartureDateTime, >> m.ArrivalDateTime, m.TransportMode, m.ManifestStatus, COUNT(s.Id) as >> TotalShipments, ISNULL(CS.CScount, 0) as CSCount >> from dbo.Movement as m >> LEFT OUTER JOIN dbo.Shipment as s on s.MovementId = m.Id >> LEFT OUTER JOIN dbo.ShipmentStatus as st on s.ShipmentStatusId = >> st.Id >> LEFT OUTER JOIN ( >> select COUNT(q.Id) as CScount, m.id as CSmid >> from dbo.Movement as m >> LEFT OUTER JOIN dbo.Shipment as s on s.MovementId >> = m.Id >> LEFT OUTER JOIN dbo.ShipmentStatus as st on >> s.ShipmentStatusId = st.Id >> LEFT OUTER JOIN dbo.Queue as q on q.Id = >> st.QueueId >> where (q.QueueType = 2) >> group by m.id >> ) as CS on CS.CSmid = m.id >> group by >> m.Id, >> m.CountryCode, >> m.ModuleFlag, >> m.Gateway, >> m.MawbNumber, >> m.MovementNumber, >> m.DepartureDateTime, >> m.ArrivalDateTime, >> CS.CScount, >> m.TransportMode, >> m.ManifestStatus >> >> I have created two separate DetachedCriteria object, one for each >> select statement. How am I going to attach the nested select statement >> from the third LEFT OUTER JOIN of the parent select statement. I can’t >> seem to find a way to do that. Is this possible in NHibernate? >> >> Regards, >> Jonathan >> >> >> >> > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
