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.
