Yeah. That limitation plagued us for a while. I don't think the
separation lies in INNER vs. OUTER join, as both can have "split" or
"common" joins. Here is an example of a multiple criteria going over
a common join:
Expression: "toArtist+.artistName = null or toArtist+.artistName =
'artist6'"
Corresponding SQL that I tested on Postgres:
select t0.*, t1.artist_name from painting t0 left outer join artist t1
on t0.artist_id = t1.artist_id
where t1.artist_name is null or t1.artist_name = 'artist6'
BTW, there is task related to that: http://issues.apache.org/cayenne/
browse/CAY-514
Unfortunately, I don't see a backwards-compatible way to pass the
join semantics to this public method.
I think changing the translators is a fair game - the API is not
directly used by the applications, and major version number increase
means that we do it if we have too (as long as it is clearly
documented). But let's figure out how we want to address splits first...
Here is an idea:
JPA EJBQL has an explicit clause for joins, but it doesn't fit in the
Cayenne 1.2 expressions that represent a WHERE clause with implicitly
inferred joins (EJBQL joins go in the FROM clause). So let's go back
to your idea of setting join policy on a SelectQuery, separate from
expression, only in a slightly different and more targeted way that
would create aliases for expressions, thus allowing controlled "splits":
EXAMPLE 1: "Common" outer join example (join clause is EJBQL
compatible; root entity is implied; "x" is an alias for the join):
join: LEFT OUTER JOIN toArtist x
exp: x.artistName = null or x.artistName = 'artist6'
EXAMPLE 2: The same thing with split joins:
join: LEFT OUTER JOIN toArtist x, LEFT OUTER JOIN toArtist y
exp: x.artistName = null or y.artistName = 'artist6'
With this approach we can avoid using "+" in the path, preserving 1.2
expressions syntax intact and moving join description outside the
expression. This should solve CAY-514 as well.
Andrus
P.S. Full BNF of join per JPA spec:
join ::= join_spec join_association_path_expression [AS]
identification_variable
fetch_join ::= join_spec FETCH join_association_path_expression
join_association_path_expression ::=
join_collection_valued_path_expression |
join_single_valued_association_path_expression
join_spec::= [LEFT[OUTER]|INNER] JOIN
On Aug 17, 2006, at 10:13 PM, Mike Kienenberger wrote:
Ok. It looks like the problem is this.
SelectTranslator.dbRelationshipAdded only creates one table alias
(FROM table entry) for a particular relationship's target entity.
That works great for INNER joins, but for LEFT OUTER joins, I think we
need one per source/target pair. Unfortunately, I don't see a
backwards-compatible way to pass the join semantics to this public
method. Unless we can set join semantics directly on the
relationship itself. Maybe it should be an attribute of DbJoin and
we could pull it up that way. It's getting late and I'm not really
thinking clearly any more, but I'd appreciate any input for when I
start up again on it tomorrow.
public void SelectTranslator.dbRelationshipAdded(DbRelationship
rel) {
[...]
String existAlias = (String) aliasLookup.get(rel);
if (existAlias == null) {
dbRelList.add(rel);
// add alias for the destination table of the relationship
String newAlias = newAliasForTable((DbEntity)
rel.getTargetEntity());
aliasLookup.put(rel, newAlias);
}
}
When it's an outer join, then each outer join needs to be given a
unique alias entry, but if it's an inner join, we only want one entry.
On 8/17/06, Mike Kienenberger <[EMAIL PROTECTED]> wrote:
SELECT * FROM (SELECT [...] FROM
ENG_WORK_MGMT.FEE t0,
ENG_WORK_MGMT.FEE_TYPE t1,
ENG_WORK_MGMT.FEE_CYCLE
ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5
WHERE
t0.FEE_TYPE_ID = t1.FEE_TYPE_ID
AND t0.FEE_ID = t2.FEE_CYCLE_ID
AND t2.FEE_CYCLE_ID = t3.INITIAL_FEE_CYCLE_ID(+)
AND t3.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
AND t2.FEE_CYCLE_ID = t5.RECURRING_FEE_CYCLE_ID(+)
AND ((t1.DESCRIPTION = ?)
AND ((t4.AGENCY_ID = ?)
OR (t4.AGENCY_ID = ?))))
Ok. I appear to be having some kind of unwanted optimization
occurring.
There should be a line that says
AND t5.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
or maybe even
ENG_WORK_MGMT.PERMIT_DOCUMENT t6,
AND t5.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID(+)
with (t6.AGENCY_ID = ?)
On 8/17/06, Mike Kienenberger <[EMAIL PROTECTED]> wrote:
> Ok. I think I have Oracle8 style outer joins working. I'll
take a
> shot at the other ones tomorrow.
>
> On 8/17/06, Andrus Adamchik <[EMAIL PROTECTED]> wrote:
> > Yes, actually there was some discussion before to use such
syntax for
> > the inner joins as well. I am all for it (I guess we have to
preserve
> > a backdoor for the old syntax in case some db does not support
such
> > syntax).
> >
> > Andrus
> >
> > On Aug 17, 2006, at 5:17 PM, Mike Kienenberger wrote:
> >
> > > Even better link
> > >
> > > http://www.devx.com/dbzone/Article/17403/0/page/3
> > >
> > > Looks like we do away with WHERE clause joins altogether (at
least for
> > > Oracle) and explicly join everything with ON statements.
> > >
> > > On 8/17/06, Mike Kienenberger <[EMAIL PROTECTED]> wrote:
> > >> This is somewhat helpful for the various kinds of joins.
> > >>
> > >> http://www.praetoriate.com/oracle_tips_outer_joins.htm
> > >>
> > >> Still looking for complex examples.
> > >>
> > >> On 8/17/06, Mike Kienenberger <[EMAIL PROTECTED]> wrote:
> > >> > On 8/17/06, Andrus Adamchik <[EMAIL PROTECTED]> wrote:
> > >> > > It would be nice if we could implement the translator
using
> > >> standard
> > >> > > SQL syntax ("left outer join" instead of "(+)"), as it
will
> > >> work on
> > >> > > most DB's including Oracle (starting from 9i), while
the "(+)"
> > >> syntax
> > >> > > only works on Oracle (and is probably considered legacy
syntax by
> > >> > > Oracle too).
> > >> > >
> > >> > > select
> > >> > > name,
> > >> > > department_name
> > >> > > from
> > >> > > employees e
> > >> > > left outer join
> > >> > > departments d
> > >> > > on
> > >> > > e.department_id = d.department_id;
> > >> > >
> > >> > > It will be somewhat harder to implement, but will solve
the issue
> > >> > > once and for all.
> > >> >
> > >> > Well, sure, now you tell me :-)
> > >> >
> > >> > My Oracle Reference Book is Oracle8, so I didn't realize
we had a
> > >> > better choice :-)
> > >> >
> > >> > I guess I need to see if I can find some documentation on
this
> > >> format.
> > >> >
> > >> > The simple example is obvious, but what does it look like
with more
> > >> > tables involved, some with more outer joins and some
without?
> > >> >
> > >>
> > >
> >
> >
>
