Hi,
You might want to try:
Select p.firstname,
Case v.type WHEN 'v1' THEN v.type
WHEN 'v2' THEN 'null'
ELSE 'null' END " +
from Patient p left join p.vaccinCollection v
However, this approach still requires post-processing of 'null' string to null value.
Fay
----- Original Message ----
From: Michel Ganguin <
[hidden email]>
To:
[hidden email]
Sent: Mon, October 26, 2009 10:46:22 AM
Subject: left join with filter on joined table
Hi,
I have two tables:
patient
-------
id
firstname
lastname
vaccine
-------
id
patientid
date
type
With an ejbqlquery i want to retrieve all patients that have 0 to n vaccines
Select p.firstname, v.type from Patient p left join p.vaccinCollection v
result:
p1, v1
p1, v2
p2, v2
p3, null
Now I want to filter by vaccine type (all patients that have 0 to n
vaccines of type v2):
Select p.firstname, v.type from Patient p left join p.vaccinCollection v
where v.type = 'v2'
result:
p1, v2
p2, v2
But this filtered out patients without vaccine, so i tried:
Select p.firstname, v.type from Patient p left join p.vaccinCollection v
where v.type = 'v2' OR v.type is null
result:
p1, v2
p2, v2
p3, null
This looks like what I want but doesn't work for v1
Select p.firstname, v.type from Patient p left join p.vaccinCollection v
where v.type = 'v1' OR v.type is null
result:
p1, v1
p3, null
I want to have "p2, null" in the result. How can I do that? I'm not
against getting all result (first query) and filter out the unwanted
data but this vaccine table becomes very huge and it sould be a dramatic
performance loss!
Is there a way to avoid post-join-filtering by adding a query hint or
something like this. Or to use parameters in entity relations? If there
is no solution I would use native queries but how can I map join queries
to entities?
Thanks in advance.
Michel Ganguin
