nhibernate group by

Question

I'm hoping to display a list of all of the products ordered. I can display this using the following sql statement

select products.Id,count(products.Id), products.Name from Products
inner join orderitems on Products.Id = orderItems.ProductId
left join orders on orderItems.OrderId = orders.Id
where orders.CompanyId = 27
group by products.Id,products.Name

I have the following entities Order, OrderItem, Product which have foreign key relationships.

I have created the following projection class

public class OrderProduct
{
    public int Id { get; set; } // removed virtual
    public string Name { get; set; } // removed virtual
    public int Quantity { get; set; } // removed virtual
}

Anyway, just hoping someone could kick start how i should approach this query with criteria api or hql

update

following rafaels example i have simplified the hql to

 var hql = @"select p.Id, p.Name, count(p.Id) as Quantity 
            from OrderItem oi 
                inner join oi.Product p on p.Id = oi.Product.Id 
                group by p.Id, p.Name";

this returns the following sql

select product1_.Id as col_0_0_, product1_.Name as col_1_0_, count(product1_.Id) as col_2_0_ from OrderItems orderitem0_ inner join Products product1_ on orderitem0_.ProductId=product1_.Id

which is very close, however it seems to ignore the group by giving me the following error

Column 'Products.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

orderItem

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="EStore.Domain"
                   namespace="EStore.Domain.Model">

  <class name="OrderItem" table="OrderItems">
    <id name="Id" type="Int32" column="Id" unsaved-value="0">
      <generator class="identity"/>
    </id>
    <property name="Name" column="Name"/>
    <set name="OrderItemAddresses" table="OrderItemAddress" generic="true" cascade="all-delete-orphan"  inverse="true" >
      <key column="OrderItemId" not-null="true" />
      <one-to-many class="EStore.Domain.Model.OrderItemAddress, EStore.Domain" />
    </set>
    <many-to-one name="Product" column="ProductId"  not-null="true" class="EStore.Domain.Model.Product, EStore.Domain" />
    <many-to-one name="Order" column="OrderId"  not-null="true" class="EStore.Domain.Model.Order, EStore.Domain" />
  </class>
</hibernate-mapping>

## product ##

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="EStore.Domain"
                   namespace="EStore.Domain.Model">

  <class name="Product" table="Products">
    <id name="Id" type="Int32" column="Id" unsaved-value="0">
      <generator class="identity"/>
    </id>

    <property name="MinQuantity" column="MinQuantity" type="Int32" not-null="true" />
    <property name="MaxQuantity" column="MaxQuantity" type="Int32" not-null="true" />
    <property name="Name" column="Name" />

  </class>
</hibernate-mapping>

Answer 1

Try this:

var hql = @"select p.Id, p.Name, count(p.Id) as Quantity 
            from OrderItem oi 
               inner join oi.Products p where p.Id = oi.Product.Id 
               left  join oi.Order o where o.Id = oi.Order.Id 
                 with o.Company.Id = :companyId 
            group by p.Id, p.Name";


var result = session.CreateQuery(hql)
     .SetParameter("companyId", 27)
     .SetResultTransformer(Transformers.AliasToBean(typeof(OrderProduct)))
     .List<OrderProduct>();

Note: If the query fails, do try and instead of with o.Company...

---

• HQL: The Hibernate Query Language
• Ad-hock mapping