openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michel Ganguin <mic...@ganguin.net>
Subject left join with filter on joined table
Date Mon, 26 Oct 2009 17:46:22 GMT
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

Mime
View raw message