You are viewing a plain text version of this content. The canonical link for it is here.

Posted to users@openjpa.apache.org by Michel Ganguin <mi...@ganguin.net> on 2009/10/26 18:46:22 UTC

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

Re: left join with filter on joined table

Posted by Miłosz Tylenda <mt...@o2.pl>.

Michel,

The CASE expression has been introduced in JPA 2. You need OpenJPA 2.0 then, for example the M3 release.

Cheers,
Milosz

> 
> Thanks
> This should solve at least a part of my problem, but a cannot make it work.
> I use opejpa 1.2.1 and it raises a parseexception on such a query. Is "case
> when" valid in jpql?
> 
> Michel
> 
> 
> Fay Wang wrote:
> > 
> > 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
> > 
> 
> -- 
> View this message in context: http://n2.nabble.com/left-join-with-filter-on-joined-table-tp3893996p3904795.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>

Re: left join with filter on joined table

Posted by Michel Ganguin <ga...@gmail.com>.

Thanks
This should solve at least a part of my problem, but a cannot make it work.
I use opejpa 1.2.1 and it raises a parseexception on such a query. Is "case
when" valid in jpql?

Michel


Fay Wang wrote:
> 
> 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
> 

-- 
View this message in context: http://n2.nabble.com/left-join-with-filter-on-joined-table-tp3893996p3904795.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: left join with filter on joined table

Posted by Fay Wang <fy...@yahoo.com>.

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 <mi...@ganguin.net>
To: users@openjpa.apache.org
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