nhibernate criteria for selecting from different tables

I have following table model:

I want following SQL command as nhibernate criteria:

SELECT * FROM Units WHERE OID IN (SELECT OID FROM Orders WHERE PONumber <> 0 ORDER BY PONumber LIMIT 5)

-> in other words: the last 5 orders

EDIT:

my mappings

Unit.hbm.xml

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                  assembly="DCGOrderSystem"
                  namespace="DCGOrderSystem.Model">

  <class name="Unit" table="Units">
    <id name="ID">
      <column name="UID" />
      <generator class="native" />
    </id>

    <property name="Division" />
    <property name="Date" />
    <property name="ItemOrderNr" />
    <property name="Description" />
    <property name="Amount" />
    <property name="Price" />
    <property name="CostCenter" />
    <property name="Location" />
    <property name="DeviceGroup" />
    <property name="Comment" />
    <property name="Distributor" />

    <!-- Many-to-one mapping: Employee -->
    <many-to-one name="EmployeeRef" column="EID" cascade="all" />

    <!-- Many-to-one mapping: Order -->
    <many-to-one name="OrderRef" column="OID" cascade="all" />
  </class>

</hibernate-mapping>

Order.hbm.xml

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

  <class name="Order" table="Orders">
    <id name="ID">
      <column name="OID" />
      <generator class="native" />
    </id>

    <property name="Locked" />
    <property name="PONumber" />

    <!-- One-to-many mapping: Units -->
    <bag name="Units" cascade="all" lazy="true">
      <key column="OID" />
      <one-to-many class="Unit" />
    </bag>
  </class>

</hibernate-mapping>

Employee.hbm.xml

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

  <class name="Employee" table="Employees">
    <id name="ID">
      <column name="EID" />
      <generator class="native" />
    </id>

    <property name="Name" />   
  </class>

</hibernate-mapping>

EDIT2:

Here my query command:

var query = m_hibernateSession.QueryOver<Model.Order>(() => orderAlias)
                    .JoinAlias(() => orderAlias.ID, () => unitAlias, JoinType.InnerJoin)
                    .TransformUsing(Transformers.DistinctRootEntity)
                    .OrderBy(x => x.PONumber).Desc.Take(5);

Thx

Answers


If you want the last five orders I would approach the problem with a slightly different query

Select * 
From order o join Units U on O.OID = U.OID
Order by O.PONumber limit 5

in nhibernate would be something like that (not tested)

Order orderAlias = null;
Unit unitAlias = null;
var query = session.QueryOver<Order>(() => orderAlias)
   .JoinAlias(() => orderAlias.Units, () => unitAlias, JoinType.InnerJoin)
   //.TransformUsing(Transformers.DistinctRootEntity) if you have duplicates
   .OrderBy(x => x.PONumber).Desc.Take(5);

UPDATE

You could also just load Order entity. NHibernate will load automatically all units rows, based on your mapping info (lazy/eager loading or joins).

var query = session.QueryOver<Order>().OrderBy(x => x.PONumber).Desc.Take(5);

Need Your Help

DJango: redirect form data, or pass form data to another view

python django forms django-forms

I have a form that needs to process some data in my django app, before the form data is passed to another app.