Ok. I remember CAY-514 now. I don't think I understood it as well
then, but it's clearer to me now :-)
Now I was all ready to keep joins associated with Expressions. Why
switch them back? It looks like each join in the JPA spec is
associated with an expression as well.
If we are going to put the join on the query, (or even if we are not),
how do you see the actual usage looking from the cayenne side?
Will expressions now have table alias prefixes which are matched up to
the aliases specified in the join parameters?
Or do we want to switch entirely to the JPA format of LEFT OUTER JOIN
AS x FETCH exp? with the join type defaulting to inner if not
specified.
Finally, is there a difference between LEFT OUTER JOIN and LEFT JOIN
in the JPA specs?
It seems like no matter how we approach it, there will be a different
syntax going forward.
If that's the case, maybe we should plan on using the JPA syntax where possible.
On 8/17/06, Andrus Adamchik <andru..bjectstyle.org> wrote:
> 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 <mkienen..mail.com> 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 <mkienen..mail.com> 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 <andru..bjectstyle.org> 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 <mkienen..mail.com> 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 <mkienen..mail.com> wrote:
> >> > > >> > On 8/17/06, Andrus Adamchik <andru..bjectstyle.org> 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?
> >> > > >> >
> >> > > >>
> >> > > >
> >> > >
> >> > >
> >> >
> >>
> >
>
>
This archive was generated by hypermail 2.0.0 : Fri Aug 18 2006 - 15:31:01 EDT