Order by Many-To-One

vadimv · **Joined:** Wed Oct 19, 2005 8:04 am **Posts:** 19

Let's say i have entity "Terminal" that has many-to-one relation to entity "TerminalStatus". My colleague wrote following code for sorting and filtering terminals by Terminal entity fields(like terminal number, terminal name, etc):

...
criteria.setFirstResult(first);
criteria.setMaxResults(max);
criteria.addOrder(Order.desc(order)); // order - properties of Terminal
Collection terminals = criteria.list();
...

But it turned out i also need to sort em by TerminalStatus.code, another words i need to get list of terminals sorted by code (TerminalStatus.code) taken from TerminalStatus table. How can i do it?

I have following part in mapping in Terminal.hbm.xml

<many-to-one name="terminalStatus" lazy="false"
class="TerminalStatus"
column="terminal_status_id"
cascade="none"/>

i tried to do it this way:
criteria.addOrder(Order.desc("terminalStatus.code"));

but i get:
org.hibernate.QueryException: could not resolve property: terminalStatus.code of: Terminal

How can i do it?

And one more, i must use criteria...

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Add an alias first:

Code:

criteria.createAlias("terminalStatus", "ts");
criteria.addOrder(Order.desc("ts.code"));

This injects the necessary join into the query, allowing you to reference the joined properties for ordering.

vadimv · **Joined:** Wed Oct 19, 2005 8:04 am **Posts:** 19

Thnx, it works (Y)

AleBrozzo · **Posted:** Tue Jul 25, 2006 6:49 pm

I tried this but it's not working. I get the error when trying to order a collection of "Post"s, which has a property "PostHighligth" (class: Highlight), which has a property "Price"

could not resolve property:_ALIASORDER_PostHighlight.Price of :Rules.Post

This is the code I use, It's a helper that takes care of handling filtering

Code:

const string prefixAlias = "_ALIASORDER_";
if (itemOrder.Property.Contains("."))
{
    string aliasForOrdering = prefixAlias + itemOrder.Property.Substring(0, itemOrder.Property.IndexOf('.')); //get the alias as _ALIASORDER_nameOfProperty
    criteria = criteria.CreateAlias(itemOrder.Property.Substring(0, itemOrder.Property.IndexOf('.')), aliasForOrdering); //set the alias 
    itemOrden.Property = aliasForOrdering + "." + itemOrder.Property.Substring(itemOrder.Property.IndexOf('.') + 1); //replace the propery name with the replacing alias 
}
switch (itemOrder.Order)
{
    //set the desired using an alias if needed
    case Utils.Extras.Orders.Asc:
        criteria.AddOrder(new NHibernate.Expression.Order(itemOrder.Property, true));
        break;
    case Utils.Extras.Orders.Desc:
        criteria.AddOrder(new NHibernate.Expression.Order(itemOrder.Property, false));
        break;
}

Help me please!
Alejandro.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Don't put that _ALIASORDER_ thing in the associationPath parameter. Maybe in the alias parameter, if you have to.

AleBrozzo · **Posted:** Wed Jul 26, 2006 8:51 am

Thanks for your answer, but I'm not puting the "_ALIASORDER_" prefix in the associationPath parameter...

When I do this:

Code:

criteria.CreateAlias(itemOrder.Property.Substring(0, itemOrder.Property.IndexOf('.')), aliasForOrdering); 

is the same as doing

Code:

criteria.CreateAlias("PostHighlight", "_ALIASORDER_PostHighlight");

(this emulates the above example:

Quote:

Code:

criteria.createAlias("terminalStatus", "ts");

)
And then I replace the name of the property with the new alias...
First I replace the name:

Code:

itemOrden.Property = aliasForOrdering + "." + itemOrder.Property.Substring(itemOrder.Property.IndexOf('.') + 1); 

This is the same as doing

Code:

itemOrden.Property = "_ALIASORDER_PostHighlight.Price";

Second and last, I add the order criterion:

Code:

criteria.AddOrder(new NHibernate.Expression.Order(itemOrder.Property, true));

where of course itemOrder.Property 's value is "_ALIASORDER_PostHighlight.Price"

So, where's the error?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Where is "Rules.Post" coming from? If the effective result of your call to CreateAlias is CreateAlias("PostHighlight", "_ALIASORDER_PostHighlight"), then why is Rules.Post in the error message? Should there be another join (another CreateAlias) in the criteria, Rules to Post? It looks to me like you're hoping that Rules.Post is an implicit join from Rules to Post, like it would be in HQL, but criteria don't handle implicit joins, so you have to have a CreateAlias (or CreateCriteria) to explicitly join Post to Rules.

Of course I'm just guessing here, I don't know where Rules.Post came from.