ORDER BY column in child table

amulroy · **Joined:** Fri Jul 07, 2006 9:26 am **Posts:** 1

I have an object: Inquiry
Inquiries have, among other things, a user and a status (which are objects themselves).

I'm displaying a list of inquiries in a GridView and I have sorting enabled on the columns. I've run into a problem though with sorting on user and status because I would need to sort on User.Name and InquiryStatus.Description.

Is it possible to order by a column of a child table? If so, how is it done?

Kintar · **Joined:** Tue Apr 19, 2005 3:16 pm **Posts:** 18

Hi, amulroy. If I understand your question, wouldn't it simply be easier to sort the GridView's model, rather than attempting to bring your results back from the database in a sorted order?

If you must do the sort in the query, for some reason, Hibernate should be smart enough to let you drop in any ORDER BY clause you need. For example:

Code:

FROM Inquiry as i
ORDER BY i.user.name

I'm not 100% sure on that, but since it works with Criteria queries, I don't see why it wouldn't work with HQL.

From the reference manual:

Code:

List results = session.createCriteria(Domestic.class, "cat")
   .createAlias("kittens", "kit")
   .setProjection( Projections.projectionList()
      .add( Projections.property("cat.name"), "catName" )
      .add( Projections.property("kit.name"), "kitName" )
   )
   .addOrder( Order.asc("catName") )
   .addOrder( Order.asc("kitName") )
   .list();

I hope that helps!

-- Kin

bonnyr · **Posted:** Fri Jul 14, 2006 3:51 am

Kin,

Kintar wrote:

Hi, amulroy. If I understand your question, wouldn't it simply be easier to sort the GridView's model, rather than attempting to bring your results back from the database in a sorted order?

If you must do the sort in the query, for some reason, Hibernate should be smart enough to let you drop in any ORDER BY clause you need. For example:

Code:

FROM Inquiry as i
ORDER BY i.user.name

I'm not 100% sure on that, but since it works with Criteria queries, I don't see why it wouldn't work with HQL.

From the reference manual:

Code:

List results = session.createCriteria(Domestic.class, "cat")
   .createAlias("kittens", "kit")
   .setProjection( Projections.projectionList()
      .add( Projections.property("cat.name"), "catName" )
      .add( Projections.property("kit.name"), "kitName" )
   )
   .addOrder( Order.asc("catName") )
   .addOrder( Order.asc("kitName") )
   .list();

I hope that helps!

-- Kin

When I try this code from the manual, I keep getting errors - the referenced
property is unknown. My schema is very similar to that of amulroy, with
the names of classes are NetObj and NE where NetObj has a reference
to an instance of NE which has a property, name, which I wish to order by.

Basically I'm doing the equivalent of:

Code:

List results = session.createCriteria(NetObj.class)
   .setProjection( Projections.projectionList()
      .add( Projections.property("ne.name"))
   )
   .addOrder( Order.asc("ne.name") )
   .list();


or 


List results = session.createCriteria(NetObj.class)
   .setProjection( Projections.projectionList()
      .add( Projections.property("ne"))
   )
   .addOrder( Order.asc("ne.name") )
   .list();


or 

List results = session.createCriteria(NetObj.class)
   .addOrder( Order.asc("ne.name") )
   .list();

I get the same exception in both cases. Tracing into the Hibernate code,
it seems that H is not attempting to follow a path through the "ne" reference. Also, this happens whether or not I use the aliased version
for the specified entities (NetObj and NE).

Any help here would be greatly appreciated.

Kintar · **Joined:** Tue Apr 19, 2005 3:16 pm **Posts:** 18

Hey there, Bonnyr. The reason you're getting your exception is that you're using an alias for your NetObj.class without first defining it. You need to create your criteria as:

Code:

session.createCriteria(NetObj.class, "ne")

Otherwise, Hibernate doesn't know that "ne" refers to your NetObj class.

-- Kin

AleBrozzo · **Posted:** Wed Jul 26, 2006 4:17 pm

Kintar: I believe what you ment to write is

Code:

session.createAlias(NE, "neAlias")

AleBrozzo · **Posted:** Wed Jul 26, 2006 4:21 pm

by the way, how can I use Projections? Do I have to reference some new dll or download something? I'm using NHibernate 1.2 or 1.0.2

bajulio88 · **Joined:** Tue Jul 25, 2006 2:50 am **Posts:** 11

AleBrozzo wrote:

by the way, how can I use Projections? Do I have to reference some new dll or download something? I'm using NHibernate 1.2 or 1.0.2

I'm in the same situation, I would like to use Projections to select a fews fields of a criteria, and I don´t know how I can do It.
Please help us.

sergey · **Posted:** Thu Jul 27, 2006 5:51 am

Projections are not supported yet in NHibernate.

bajulio88 · **Joined:** Tue Jul 25, 2006 2:50 am **Posts:** 11

then, if it´s posible to know,... when this operation will be enable?
thanks and excuse me.

AleBrozzo · **Posted:** Thu Jul 27, 2006 9:38 am

sergey wrote:

Projections are not supported yet in NHibernate.

Then how come Kintar is using them?

sergey · **Posted:** Thu Jul 27, 2006 9:44 am

He uses Java, not .NET.

bajulio88 · **Joined:** Tue Jul 25, 2006 2:50 am **Posts:** 11

sergey wrote:

Projections are not supported yet in NHibernate.

then, if it´s posible to know,... when this operation will be enable?
thanks and excuse me.