subquery and ordering

typofrank · **Joined:** Wed Feb 01, 2006 10:33 am **Posts:** 15

Hibernate version:3.2

Mapping documents:

<class name="Woman" table="woman">
<id name="womanID" column="womanid" type="java.lang.Integer">
<generator class="increment" />
</id>

<property name="name" column="name" type="string" />
<property name="husbandID" column="husbandid" type="integer" />
<one-to-one class="Husband" name="husband" column="husbandID" />
</class>

-----------------------------------------------------------
<class name="Husband" table="husband">
<id name="husbandID" column="husbandid" type="java.lang.Integer">
<generator class="increment" />
</id>

<property name="name" column="name" type="string" />
<property name="age" column="age" type="string" />
</class>

----------------------------------------------------------
<class name="Child" table="child">
<id name="childID" column="childid" type="java.lang.Integer">
<generator class="increment" />
</id>

<property name="name" column="name" type="string" />
<property name="womanID" column="womanid" type="integer" />
<many-to-one class="Woman" name="woman" update="false" insert="false" column="womanID" />
</class>
[/code]

Using postgresql v.8

TABLES:

Code:

TABLE: woman
womanid , name , husbandid 
1          Anna        1             
2          Carol        2
3          Sara         3
4          Ruth         4

TABLE: husband
husbandid , name ,    age
1                Buck      43
2                John       56
3                Kirk        24
4                Ronald    26

TABLE: child
childid , name       , womanID ,
1          Stewart            1
2          Carl                  1
3          Lisa                   1
4          John junior        2
5          Emma               2

Objects:

Code:

public Woman{
 private Integer womanID;
 private String    name;
 private Integer husbandID;
 private Husband husband;
+ setters and getters
}
public Husband{
 private Integer husbandID;
 private String    name;
 private int         age;
+ setters and getters
}
public Child{
 private Integer childID;
 private String    name;
 private Integer womanID;
 private Woman woman;
+ setters and getters
}

I have worked with hibernate a couple of months now and having some problems. I do not work with Woman,Husband and Child but I created this example to make it easier to understand my questions.

Question 1:

I want to get all the Woman and order them by who has the oldest man, with the oldestman on top, BUT if the man is 56 years old he shall be at the bottom.

This is what order i want it to list the woman by AGE

Code:

Woman  Husband    Husband.age
--------------------------------------------
Anna        Buck             43            
Ruth        Ronald           26
Sara         Kirk              24
Carol       John              56

In MySql its something like this

Code:

if age=56 case =1 else case=0
order by case asc, age desc

How do i do it in HQL??

Question 2:
I want to get all Child that has a Woman(mother) with the name "Anna"
This works:

Code:

List childList =  session.createQuery("from Child where woman.name='Anna'").list();

How can i make this work?

Code:

   
List childList = session.createCriteria(Child.class)
         .add(Expression.eq("woman.name", "Anna")).list();

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

Answer 1: Unfortunately case is supported only in the where clause (as far as I know), and union isn't supported in HQL, so I don't think that you can put the special case into an "order by". You could do this by writing a comparator and putting it in the collections sort="" attribute, but then you'd be stuck with it always, not just during this special query.

Answer 2:

Code:

Criteria cirt = session.createCriteria(Child.class);
crit.createAlias("woman", "w");
crit.add(Restrictions.eq("w.Name", "Anna");