cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Wernke zur Borg <>
Subject Re: Syntax error and question about JOIN
Date Thu, 30 Jun 2011 13:09:07 GMT

Thanks Andrus, that got me going. I had to create six classes to add a
single word to a query! ;)

And fortunately that single word is the first one to be output, so in my
appendJoinSubtree() function I am simply outputting " INNER" and then
call super.appendJoinSubtree() for the rest. Would have been more
tedious if I had to insert something in the middle, because then I would
have had to copy the whole method plus two private data members and an
inner class.

Anyway, it is working now, thanks again.


Am 29.06.2011 10:03, schrieb Andrus Adamchik:
> There's a tree of related objects involved. Check Oracle8Adapter and related classes.
It actually switches Cayenne back to joins in WHERE clause. The chain of overridden objects
goes like this:
> Oracle8Adapter.getAction(..) 
>   Oracle8ActionBuilder
>      Oracle8SelectAction
>        Oracle8JoinStack
> Oracle8JoinStack is where the magic happens. BTW, you can create a simpler subclass of
JoinStack, just overriding 'appendJoinSubtree' to change " JOIN" to " INNER JOIN".
> Andrus
> On Jun 28, 2011, at 10:52 AM, Wernke zur Borg wrote:
>> Thanks for your answers. I would like to stick to version 3.1, so could
>> somebody give me a hint as to where to modify the JOIN syntax in a
>> custom DB adapter? I looked at all the existing DB adapters and I guess
>> I would subclass JdbcAdapter but I could not find a suitable method to
>> override.
>> Thanks, Wernke
>> Am 21.06.2011 15:41, schrieb Andrus Adamchik:
>>> Past versions of Cayenne used joins in WHERE clause, that were likely compatible
with MS Access. So older Cayenne 2.0.x should probably work. 
>>> Also writing a custom Access DbAdapter that adds INNER should be possible. 
>>> Andrus
>>> On Jun 21, 2011, at 11:36 AM, Aristedes Maniatis wrote:
>>>> On 21/06/11 6:02 PM, Wernke zur Borg wrote:
>>>>> Hi,
>>>>> I am having a few problems with queries on an MS Access database when
>>>>> use object relationships to access related tables. I am using Cayenne
>>>>> 3.1 M2.
>>>>> My case is very simple. Let's say I have two tables A and B where A has
>>>>> a column col containing a primary key of B. Given an object a from table
>>>>> A with colValue, I want to access the related record of B using a.getB().
>>>>> Cayenne creates a select query that joins A and B like this:
>>>>> SELECT DISTINCT t0.columns... FROM B t0 *JOIN* A t1 ON ( = t1.col)
>>>>> WHERE = ? [bind: 1->  a.pkValue]
>>>>> The first thing is that this produces a syntax error as the simple *JOIN
>>>>> *keyword is not valid in MS Access. They want an explicit *INNER JOIN*.
>>>> I've never heard of anyone using MS Access before as an SQL engine, so this
might be one reason why. I would run, not walk, to another db if you can at all manage that
within your environment.
>>>>> But secondly I wonder why Cayenne needs a JOIN in this case anyway,
>>>>> since it is of no use at all. Why does it not use something like:
>>>>> SELECT t0.columns... FROM B t0 WHERE = ? [bind 1->  a.colValue]
>>>> I guess since any SQL engine will optimise that out, the fact that Cayenne
does it in the most generic way usually doesn't matter. I haven't seen the code in question
specifically, but Cayenne performs JOINs very often and has a bunch of code which knows how
to do that (multiple keys, qualifiers, inheritance, etc) and that code is just being reused
>>>>> My workaround is to manually create a SELECT query to access B without
>>>>> joining it A:
>>>>> Expression e = ExpressionFactory.matchExp(B.primaryKeyProperty, A.colValue);
>>>>> SelectQuery q = new SelectQuery(B.class, e);
>>>>> This works but of course it would be nicer to use the getter function
>>>>> A.getB().
>>>>> Is there a possibility to either avoid the JOIN or tell Cayenne to use
>>>>> INNER JOIN instead of JOIN ?
>>>> Yes, you can write specific db adapters for databases of your choice. You'll
need to get your hands into Cayenne itself, but the classes in question aren't too hard to
understand. Let us know if you need more pointers to do that. I am guessing that none of the
development team will be easily able to test against Access, and we don't currently run any
unit tests against Access. But if you can help make it work, then so much the better.
>>>> Cheers
>>>> Ari
>>>> -- 
>>>> -------------------------->
>>>> Aristedes Maniatis
>>>> GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A

View raw message