Sorting on child object property

jason · **Joined:** Mon Aug 29, 2005 7:04 pm **Posts:** 1

I'm trying to display records in a DataGrid sorted by a joined property.

My objects are Account and AccountType

I'd like to do the equivilant of:

select accounts.*
from accounts, account_types
where account.typeid = account_types.id
order by account_types.name

first i tried using a criteria

Code:

criteria.AddOrder(Order.Asc("Account.Type.Name"));

this crashes at runtime

Next I tried this HQL

Code:

session.CreateQuery("from Account a order by a.Type.Name")

this actually returns the list just fine, but the results are not sorted.

Next I tried the following

Code:

session.CreateQuery("from Accounts as a join a.Type as t order by t.Name")

This actually runs and returns an array of System.Object.  
I'm not sure if this is working - I can't see what the objects contain.

Any help with this would be appreciated.

simoncropp · **Joined:** Sun Aug 28, 2005 12:00 am **Posts:** 2

I have posted a similar question in "ICriteria.AddOrder for a linked class".
Unfortunately have no responses yet.

I still have not worked out if this is not possible, is a bug or (the most likely) i just dont know what i am doing :)

Will watch your post and let u know if i find a way of doing this.

My post was as follows.

Quote:

I have two classes ProjectData and MasterProjectData

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="ProjectData" table="project">
         <key column="projectId" />
         <many-to-one name="IMasterProject" class="CCAdminData.MasterProjectData, CCAdminData" column="masterGuid" />
   </class>
</hibernate-mapping>

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="MasterProjectData" table="MasterProjects">
      <id name="Guid" type="System.Guid" column="guid" unsaved-value="00000000-0000-0000-0000-000000000000">
         <generator class="guid" />
      </id>
      <property name="Name"/>
      <bag name="Projects" inverse="true">
         <key column="masterGuid" />
         <one-to-many class="CCAdminData.ProjectData, CCAdminData" />
      </bag>
   </class>
</hibernate-mapping>

I would like to use a Criteria to search for ProjectsData(s) but i would like to order by MasterProjectData.Name
So my createCriteria is
ICriteria projectCriteria = Db.Session.CreateCriteria(typeof(ProjectData));

To add the order i have tried

Code:

-projectCriteria.AddOrder(Order.Desc("IMasterProject.Name"));
-projectCriteria.AddOrder(Order.Desc("ProjectData.IMasterProject.Name"));      
-projectCriteria.AddOrder(Order.Desc("$this.IMasterProject.Name"));

None of which works

which all give a NullReferenceException on line 1544 of normalizedentitypersister

Does anyone know how to do this?
The reason i want to use a criteria instead of just writing a query is that i would like to use the SetFirstResult and SetMaxResults of the criteria.

KPixel · **Posted:** Tue Aug 30, 2005 10:30 am

Does it help : 6.6. Sorted Collections ?

tinomen · **Joined:** Tue May 24, 2005 12:55 pm **Posts:** 56

Quote:

session.CreateQuery("from Accounts as a join a.Type as t order by t.Name")

This actually runs and returns an array of System.Object.
I'm not sure if this is working - I can't see what the objects contain.

Try

Code:

IList results = session.CreateQuery("from Accounts as a JOIN FETCH a.Type as t order by t.Name")
foreach(Account account in results) {
//account.Type.Name
}

jasonh · **Joined:** Fri Sep 02, 2005 1:11 am **Posts:** 4

Awesome! Thanks! That worked. One minor point, I think you meant to write:

Code:

IQuery query = session.CreateQuery("from Accounts as a JOIN FETCH a.Type as t order by t.Name")
IList result = query.List();