Heh :-) In 3.1 we have a nice mechanism for creating simple extension points of Cayenne. I hope we'll start using that more to unwind these chains and allow for easier extensibility. I guess community input and contributions should become a driving force here.
Andrus On Jun 30, 2011, at 4:09 PM, Wernke zur Borg wrote: > 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. > > Wernke > > > > 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 I >>>>>> 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 (t0.pk = t1.col) >>>>>> WHERE t1.pk = ? [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 t0.pk = ? [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 here. >>>>> >>>>> >>>>>> 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 >>>>> >>> > >
