JPA perform part of an query if a value is not null

Question

in Java the following is a common idiom:

if( null != obj && obj.getSomeNumber() > 0 ) { ... }

This will perform the length check only, if null != obj is true. However, in a JPA NamedQuery this does not work:

@NamedQuery(
name = "query"
query = "SELECT o FROM SomeObjectList o WHERE o.someObject is not null AND o.someObject.someNumber > 0")

(This is what I've expected, as this would also not work in SQL.)

o.someObject is either 'null' or an foreign key to a table where SomeObjects are stored. (Each column of the table corresponds to an attribute of SomeObject.)

-------------------------           ----------------------------
| Table: SomeObjectList |           |    Table: SomeObject     |
-------------------------           ----------------------------
|  id   |   someObject  |           |  id    |  number |  name |
-------------------------           ----------------------------
|   1   |        4      |           |   3    |    -4   | foo   |
-------------------------           ----------------------------
|   2   |       null    |           |   4    |     2   |  bar  |
-------------------------           ----------------------------

So I'd like to create a NamedQuery which will return all Objects from SomeObjectList which either have no object (someObject == null) or where SomeObject.number > 0. Currently, I obtain all objects and check myself if someObject is set.

But is there a way to get an similar behavior in JPA or does I have to perform the checks on the returned objects?

edit: Added graphics and clarified the problem. (Thanks to James.)

Answer 1

You need to use an outer join for this.

Note your query is wrong, you want seem to want an OR not an AND, otherwise just remove the null check.

SELECT o FROM SomeObjectList o left join o.someObject so WHERE o.someObject is null OR so.someNumber > 0