Sleep order for a specific state

Question

Hi In the database we have a table PRSN_ADDRESS, which has many addresses. The user is shown these addresses in the grid. The request first displays the addresses associated with this user state, and then displays all other states.

For example, let's say that in the table are 10 entries with 5 addresses that have the status of Maryland, 2 from PA and 3 from NJ. Now, if the user is connected to Maryland, we need to show all 10 addresses, but the addresses in Maryland should appear in the first five, and then in the remaining 5 in any order.

We use Hibernate criteria. I have not worked on formulas and am not sure if this will help solve the problem. It would be great if someone could point me in the right direction. I appreciate it.

thank

Harish

Answer 1

You can order a conditional value ...

(NHibernate, does anyone know the equivalent in Hibernate?)

.AddOrder(Order.Asc(
    Projections.Conditional(
         Restrictions.EqProperty("addr.state", "user.state"), Projections.Constant(0), Projections.Constant(1))))
.AddOrder(Order.Asc("addr.state"))

translates to ...

order by 
    case when addr.state = user.state then 0 else 1 end,
    addr.state

Answer 2

I'm sure there is a really great way to do this, but my initial thought is to just make two requests. Use the state == 'Maryland'second one first state != 'Maryland'. And combine the results with yourself.

Use of HQL is possible:

from Person p order by case when p.addr.state = 'Maryland' then '0' else '1' end asc, p.addr.state asc

I prepared an example on github

https://github.com/gid79/q4510810-hibernate-criteria-order-by-a-specific-state

Answer 3

,    , , , . List addresses = sess.createCriteria(PRSN_ADDRESS.class) .addOrder( Order.asc(user.state) ) .list();