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 : Thu Aug 17 2006 - 23:27:56 EDT