left joins, wheres and IN

[Pawel Veselov <pa...@gmail.com>]

Hi.

Is there really no way in JPQL to have a left join on a sub-query? It seems
that WHERE would always be applied to the product of the join, and never on
the joined table. And there doesn't seem to be a way to create a mapped
relationship that would be restricted by some sub-query. So the alternative
is to have tables for each possible variation, which is not always
possible. I found some 2009 threads on this, but they don't have much
resolution.

It's generally, IMHO, a quite common case when there are two entities, with
one-to-many relationship. If I wanted to find all ALPHA entities that do
not have a certain relationship with BETA, in SQL, I would do:

select alpha.* from alpha left join (select beta.alpha_id from beta where
<constraint on beta>) j on alpha.id = j.alpha_id where <constraing on alpha>

Attempting to move the <constraint on beta> outside of the join sub-query
will most likely miserably fail, because SQL engines, in general, seem to
throw away ALPHA entries that don't match anything in the joined table when
WHERE clause has references to BETA (even if you try using BETA.column is
null).

Thank you,
  Pawel.

P.S.
Sorry for off-topic.

P.P.S.
And what's really the deal with Collection Member Declaration? The Spec
basically says that it's "... For example, the [join] query .... can be
equivalently expressed as follows, using IN operator: ...". With no more
insight, is it just an ability to use an alternative syntax to inner joins?

Re: left joins, wheres and IN

[Pawel Veselov <pa...@gmail.com>]

Hi.

On Wed, Dec 28, 2011 at 7:57 AM, Pinaki Poddar <pp...@apache.org> wrote:

> How about
> select a from Alpha a
>            where {predicates on a}
>            and not exists (select b from Beta b where b.alpha = a and
> {other predicates on b})
>
>
That would of course give the results, but it's a nested loop, and over a
sub-query too. I don't believe OpenJPA (or any other JPA) would convert
that to a left join (though it should technically be possible to do so) in
any dialect.

My original concern was about true left joins.

Re: left joins, wheres and IN

[Pinaki Poddar <pp...@apache.org>]

How about
select a from Alpha a 
            where {predicates on a} 
            and not exists (select b from Beta b where b.alpha = a and
{other predicates on b}) 

-----
Pinaki Poddar
Chair, Apache OpenJPA Project
--
View this message in context: http://openjpa.208410.n2.nabble.com/left-joins-wheres-and-IN-tp7131885p7133073.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.