HQL nullable property should not be ignored, help?

Question

Hey All,

I have:

Class Foo
{
String name;
String value;
Foo parent; //Foo.parent is OneToOne and nullable
}

I have the following HQL:

FROM Foo f WHERE 
(lower(f.name) like concat( lower('string') , '%' )) or
(lower(f.value) like concat( lower('string') , '%' )) or
(lower(f.parent.name) like concat( lower('string') , '%' ))

The query works great until f.parent is null. Even if the f.name or f.value matches, when f.parent is null, the result is thrown out.

So say I have:

Foo a = new Foo();
a.name = "bob";
a.value = "chris";
a.parent = null;

Foo b = new Foo();
b.name = "Bob";
b.value="Chris";
b.parent = a;

When I search for "b" only b is returned. I would like it so a and b are returned.

Any tips?

Thank you!

Answer 1

Referencing f.parent.name in your query creates an implicit inner join on the parent property. To include rows which can not be joined because they have no parent, you'll have to explicitly use a left outer join instead:

from Foo f
left outer join f.parent as p
where
(lower(f.name) like concat( lower('string') , '%' )) or
(lower(f.value) like concat( lower('string') , '%' )) or
(lower(p.name) like concat( lower('string') , '%' ))

Related HQL documentation