cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Wernke zur Borg <wernke.zur.b...@esa.int>
Subject Syntax error and question about JOIN
Date Tue, 21 Jun 2011 08:02:53 GMT
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*.

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]

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 ?

Please let me know in case you need more detailed info. Thanks for any help.

Wernke



Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message