CODEWITHSUNDEEP
javajpa
Luther Baker
Luther Baker

Reputation: 7341

How to write a JPA query with an "order by" clause against a property that may be null

I have defined a JPA entity "A" with a @ManyToOne relationship to another JPA entity "B".

@Entity
class A {

    String name;

    int sortOrder

    @ManyToOne
    B b;

}

This reference can be null or it can be populated. Now, I would like to query all the As and sort by B.name. Unfortunately, something like

find("order by B.name, sortOrder, name")

seems to drop any As such that (A.b = null). What is the best way to write this query?

I want all the As and I want them sorted by their B.name reference if it exists ... lumping all those with no B property together.

Upvotes: 2

Views: 3385

Answers (2)

Volt0
Volt0

Reputation: 101

There are several approaches … Sets/Comparators, HQL, Native Sql, @Sort annotations …

Upvotes: 0

digitaljoel
digitaljoel

Reputation: 26584

you need a left join.

select a from A a left join a.b b order by b.name, a.sortOder, a.name

or something like that.

Upvotes: 2

Related Questions