Java \ Hibernate \ ManyToOne \ Ordering

Question

I have some problems with Hibernate (JPA), PostgreSQL and ordering.

For example, we have two tables (mapped to entities):

- Pets (id (id), PetTypes (types_id), description (description))

- PetTypes (id (id), name (name))

type_id field is not required (constraint to pet_types table).

For example, we have 10 lines at pets table (at 1, 2 and 3 lines type_id is null). I need to order pets table by pet_types.name using HQL or JPA criteria (but i think its not supported by JPA).

Problem: values from pets table are not selecting with ordering if type_id is null. I can try to use next solutions:

FROM Pets pets ORDER BY pets.petTypes.name ASC NULLS LAST

FROM Pets pets ORDER BY CASE WHEN pets.petTypes IS NULL THEN 0 ELSE 1 END, ORDER BY pets.petTypes.name ASC NULLS LAST

FROM Pets pets ORDER BY CASE WHEN pets.petTypes.name IS NULL THEN 0 ELSE 1 END, ORDER BY pets.petTypes.name ASC NULLS LAST

FROM Pets pets LEFT JOIN pets.petTypes ORDER BY pets.petTypes.name ASC NULLS LAST

FROM Pets pets ORDER BY pets.petTypes ASC NULLS LAST, pets.petTypes.names ASC

FROM Pets pets ORDER BY CASE WHEN pets.petTypes IS NULL OR pets.petTypes.name IS NULL THEN 0 ELSE 1 END, ORDER BY pets.petTypes.name ASC

But nothing works. After selecting we have 7 instead of 10 rows. Any ideas? I can't use UNION statement via HQL. It's open Hibernate bug since 2005.

EDIT

Thx to Rodrigo Menezes. This solution is works:

select p from Pets p left join p.petTypes pt order by case when pt is null then 0 else 1 end, pt.name

Can it be that you need two orders (not sure). Because "pets.petTypes" is null, not pets.petTypes.name or? something like ORDER BY pets.petTypes ASC NULLS LAST, pets.petTypes.names ASC — pL4Gu33
Yes, I tried to use this method. Nothing works. I update question above. — Andrew Sikorsky
The sort order should have no impact on the number of rows. Can you post the raw SQL dump? — dfb
Hibernate generate CROSS JOIN statement for Pets and PetTypes tables — Andrew Sikorsky

Rodrigo Menezes Rodrigo Menezes · Accepted Answer · 2015-03-03T20:30:59

Maybe your HQL is generating inner or cross join.

You can force left join:

select p from Pets p 
left join p.petTypes pt 
order by case when pt is null then 0 else 1 end, pt.name

I made a case test and worked:

public static void main(String[] args) {
    Session session = HibernateUtil.getSessionFactory().openSession();

    session.beginTransaction();

    PetTypes dog = new PetTypes();
    dog.setName("Dog");
    dog.setId(1);
    PetTypes cat = new PetTypes();
    cat.setName("Cat");
    cat.setId(2);

    session.save(dog);
    session.save(cat);

    int id = 1;
    Pets joe = new Pets();
    joe.setId(id++);
    joe.setDescription("Joe");

    Pets x = new Pets();
    x.setId(id++);
    x.setDescription("Sarah");    
    x.setPetTypes(dog);

    Pets y = new Pets();
    y.setId(id++);
    y.setDescription("Jakob");    
    y.setPetTypes(cat);  

    Pets z = new Pets();
    z.setId(id++);
    z.setDescription("Xena");    
    z.setPetTypes(cat);                

    session.save(joe);
    session.save(x);
    session.save(y);
    session.save(z);

    session.getTransaction().commit();

    Query q = session.createQuery("select p from Pets p "
            + "left join p.petTypes pt "
            + "order by case when pt is null then 0 else 1 end, pt.name ");

    List<Pets> resultList = q.list();
    System.out.println("num of employess:" + resultList.size());
    for (Pets next : resultList) {
        System.out.println("pet " + next);
    }
}

Result:

num of pets:4 
pet Pets [description=Joe, petTypes=null] 
pet Pets [description=Jakob, petTypes=PetTypes [name=Cat]] 
pet Pets [description=Xena, petTypes=PetTypes [name=Cat]] 
pet Pets [description=Sarah, petTypes=PetTypes [name=Dog]]

Java \ Hibernate \ ManyToOne \ Ordering

1 Answers