I have had to resort to SqlQuery for such as simple thing, why????
<resultset name="HierarchyItem">
<return alias="h" class="ItemLink" />
<return-join alias="i" property="h.Item" />
<return-join alias="p" property="h.ParentItem" />
<return-scalar column="count" type="long" />
</resultset>
<sql-query name="HierarchyChildrenWithCount" resultset-
ref="HierarchyItem">
<![CDATA[select iteminhier0_.Id as {h.Id},
itemlookup1_.Id as {i.Id},
itemlookup2_.Id as {p.Id},
iteminhier0_.version as {h.Version},
iteminhier0_.parent_id as {h.ParentItem},
iteminhier0_.item_id as {h.Item},
iteminhier0_.expand as {h.Expand},
iteminhier0_.not_fitted as {h.NotFitted},
iteminhier0_.qty_in_parent as {h.QtyInParent},
iteminhier0_.sequence as {h.Sequence},
iteminhier0_.reference as {h.Reference},
iteminhier0_.created_on as {h.CreatedOn},
iteminhier0_.created_by as {h.CreatedBy},
iteminhier0_.modified_on as {h.ModifiedOn},
iteminhier0_.modified_by as {h.ModifiedBy},
itemlookup1_.id_prefix as {i.IdPrefix},
itemlookup1_.id_number as {i.IdNumber},
itemlookup1_.id_site as {i.IdSite},
itemlookup1_.name as {i.Name},
itemlookup1_.description as {i.Description},
itemlookup1_.mfg_no as {i.MfgNo},
itemlookup1_.toplevel as {i.MajorItem},
itemlookup2_.id_prefix as {p.IdPrefix},
itemlookup2_.id_number as {p.IdNumber},
itemlookup2_.id_site as {p.IdSite},
itemlookup2_.name as {p.Name},
itemlookup2_.description as {p.Description},
itemlookup2_.mfg_no as {p.MfgNo},
itemlookup2_.toplevel as {p.MajorItem},
count_big(childlinks3_.Id) as count
from
ItemLink iteminhier0_ inner join Item itemlookup1_
on iteminhier0_.item_id=itemlookup1_.Id
inner join Item itemlookup2_ on
iteminhier0_.parent_id=itemlookup2_.Id
left outer join ItemLink childlinks3_ on
iteminhier0_.item_id=childlinks3_.parent_id
where (itemlookup2_.Id
= :parent )
group by
iteminhier0_.Id ,
iteminhier0_.version ,
iteminhier0_.expand ,
iteminhier0_.not_fitted ,
iteminhier0_.qty_in_parent ,
iteminhier0_.sequence ,
iteminhier0_.reference ,
iteminhier0_.created_on ,
iteminhier0_.created_by ,
iteminhier0_.modified_on ,
iteminhier0_.modified_by ,
itemlookup2_.Id ,
itemlookup2_.id_prefix ,
itemlookup2_.id_number ,
itemlookup2_.id_site ,
itemlookup2_.name ,
itemlookup2_.description ,
itemlookup2_.mfg_no ,
itemlookup2_.toplevel ,
itemlookup1_.Id ,
itemlookup1_.id_prefix ,
itemlookup1_.id_number ,
itemlookup1_.id_site ,
itemlookup1_.name ,
itemlookup1_.description ,
itemlookup1_.mfg_no ,
itemlookup1_.toplevel,
iteminhier0_.parent_id,
iteminhier0_.item_id
order by iteminhier0_.sequence
]]>
</sql-query>
On Feb 9, 5:23 pm, Kevin Fairclough <[email protected]>
wrote:
> This query is bringing data back (although the count is wrong):
>
> IList listy = Session.CreateQuery(@"select item, count(children) from
> ItemLink item left outer join item.ChildLinks children
> where item.ParentItem = '" + id + @"'
> group by item.Id, item.Version, item.Expand,
> item.NotFitted, item.QtyInParent, item.Sequence, item.Reference,
> item.CreatedOn, item.CreatedBy, item.ModifiedOn,
> item.ModifiedBy,
> item.ParentItem, item.ParentItem.IdPrefix,
> item.ParentItem.IdNumber, item.ParentItem.IdSite,
> item.ParentItem.Name, item.ParentItem.Description,
> item.ParentItem.MfgNo, item.ParentItem.MajorItem,
> item.Item, item.Item.IdPrefix, item.Item.IdNumber,
> item.Item.IdSite, item.Item.Name, item.Item.Description,
> item.Item.MfgNo, item.Item.MajorItem").List();
>
> The following query trying to fetch the associations doesn't:
>
> IList listy = Session.CreateQuery(@"select item, count(children) from
> ItemLink item join fetch item.ParentItem join fetch item.Item left
> outer join item.ChildLinks children
> where item.ParentItem = '" + id + @"'
> group by item.Id, item.Version, item.Expand,
> item.NotFitted, item.QtyInParent, item.Sequence, item.Reference,
> item.CreatedOn, item.CreatedBy, item.ModifiedOn,
> item.ModifiedBy,
> item.ParentItem, item.ParentItem.IdPrefix,
> item.ParentItem.IdNumber, item.ParentItem.IdSite,
> item.ParentItem.Name, item.ParentItem.Description,
> item.ParentItem.MfgNo, item.ParentItem.MajorItem,
> item.Item, item.Item.IdPrefix, item.Item.IdNumber,
> item.Item.IdSite, item.Item.Name, item.Item.Description,
> item.Item.MfgNo, item.Item.MajorItem").List();
>
> It is complaining about an ItemLink.parent_id not being in the group
> by, yet I cannot seem to find the equivalent property to add to my
> group by list.
>
> Also the SQL generated for the left outer join is joining on the wrong
> columns:
> "...left outer join ItemLink childlinks3_ on
> iteminhier0_.Id=childlinks3_.item_id..."
> it should be:
> "...left outer join ItemLink childlinks3_ on
> iteminhier0_.item_id=childlinks3_.parent_id..."
>
> Here is my mappings
>
> <?xml version="1.0" encoding="utf-8" ?>
> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
> namespace="Domain" assembly="Domain" >
> <class name="ItemLink" table="ItemLink" mutable="false" >
> <id name="Id">
> <generator class="guid.comb" />
> </id>
> <property name="Version" column="version" />
>
> <many-to-one name="ParentItem" class="ItemLookup"
> column="parent_id" fetch="join" />
> <many-to-one name="Item" class="ItemLookup" column="item_id"
> fetch="join" />
>
> <property name="Expand" column="expand"/>
> <property name="NotFitted" column="not_fitted" />
> <property name="QtyInParent" column="qty_in_parent" />
> <property name="Sequence" column="sequence" />
> <property name="Reference" column="reference" />
> <property name="CreatedOn" column="created_on" />
> <property name="CreatedBy" column="created_by" />
> <property name="ModifiedOn" column="modified_on" />
> <property name="ModifiedBy" column="modified_by" />
>
> <bag name="ChildLinks" table="ItemLink" inverse="true" lazy="true"
>
> <key column="item_id" />
> <one-to-many class="ItemInHierarchy"/>
> </bag>
>
> <bag name="ParentLinksBag" table="ItemLink" inverse="true"
> lazy="true" >
> <key column="parent_id" />
> <one-to-many class="ItemInHierarchy"/>
> </bag>
>
> </class>
> </hibernate-mapping>
> <?xml version="1.0" encoding="utf-8" ?>
> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
> namespace="Domain" assembly="Domain" default-lazy="false">
> <class name="ItemLookup" table="Item" mutable="false" batch-
> size="10" >
> <id name="Id" column="Id">
> <generator class="guid.comb" />
> </id>
>
> <property name="IdPrefix" column="id_prefix"/>
> <property name="IdNumber" column="id_number" />
> <property name="IdSite" column="id_site" />
> <property name="Name" column="name" />
> <property name="Description" column="description" />
> <property name="MfgNo" column="mfg_no" />
> <property name="MajorItem" column="toplevel" />
>
> </class>
> </hibernate-mapping>
>
> Any help would be great, I'm completely baffled!
>
> On Feb 9, 11:23 am, Kevin Fairclough <[email protected]>
> wrote:
>
> > Thanks, will give it a go, I guess for associations on the link (I
> > have two one-to-ones) I need to group by those properties also.
>
> > On Feb 5, 5:40 pm, Diego Mijelshon <[email protected]> wrote:
>
> > > Just like with SQL, you need to specify all of the properties in the group
> > > by clause.
> > > Yes, it would be nice for NH to do it. Yes, it's a leaky abstraction. But
> > > that's how it is...
> > > "group by entity" is translated as "group by entity.id".
>
> > > This query should work:
>
> > > select item, count(children)
> > > from ItemLink item
> > > left join item.Children children
> > > group by item.Id, {all other properties in ItemLink}
>
> > > Diego
>
> > > On Thu, Feb 4, 2010 at 12:52, Kevin Fairclough <
>
> > > [email protected]> wrote:
> > > > Hi
>
> > > > This should be easy to do, but I cannot figure it out.
> > > > I have a recursive object ItemLink object which contains a list of
> > > > itself ChildLinks
>
> > > > I want to get the next level of links with a count of links at the
> > > > following level, i.e. the child count.
>
> > > > Session.CreateQuery("select i, count(children) from ItemLink i left
> > > > outer join i.ChildLinks children group by i.id").List()
>
> > > > The above query doesn't work, but this query does:
> > > > Session.CreateQuery("select i.id, count(children) from ItemLink i left
> > > > outer join i.ChildLinks children group by i.id").List()
>
> > > > how can I fetch the whole ItemLink object and also the Count in one
> > > > query?
>
> > > > TIA
> > > > Kevin
>
> > > > --
> > > > 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]<nhusers%[email protected]>
> > > > .
> > > > For more options, visit this group at
> > > >http://groups.google.com/group/nhusers?hl=en.
>
>
--
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.
