CODEWITHSUNDEEP
javahibernatepostgresql
Andrew Sikorsky
Andrew Sikorsky

Reputation: 55

Java \ Hibernate \ ManyToOne \ Ordering

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:

  1. FROM Pets pets ORDER BY pets.petTypes.name ASC NULLS LAST
  2. FROM Pets pets ORDER BY CASE WHEN pets.petTypes IS NULL THEN 0 ELSE 1 END, ORDER BY pets.petTypes.name ASC NULLS LAST
  3. 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
  4. FROM Pets pets LEFT JOIN pets.petTypes ORDER BY pets.petTypes.name ASC NULLS LAST
  5. FROM Pets pets ORDER BY pets.petTypes ASC NULLS LAST, pets.petTypes.names ASC
  6. 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

Upvotes: 3

Views: 199

Answers (1)

Rodrigo Menezes
Rodrigo Menezes

Reputation: 245

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]]

Upvotes: 1

Related Questions