Hi,
Look to this simple case:
A --- AB --- B ---- C
|
D
1- A got a M:N relation with B
2 - B got a 1:M to C and D
The M:N relationship is implemented with collections (indirection-table AB).
If I want to get "All A's that have B's associated that have C's and D's
with some property equals to something", i.e, if I do this OQL query:
select all from A.class.getName() where bs.cs.idInternal = $1 and
bs.ds.idInternal = $2
The execution result of this query is this:
SELECT A0.name,A0.idInternal
FROM A A0 INNER JOIN AB A1 ON A0.idInternal=A1.keyA
INNER JOIN B A2 ON A1.keyB=A2.idInternal
INNER JOIN AB A1 ON A0.idInternal=A1.keyA
INNER JOIN B A2 ON A1.keyB=A2.idInternal
WHERE ( A2.idInternal = ? ) AND (A2.idInternal = ? )
Well this don't works because A1 alias is duplicated. The error tells me
that:
ERROR [main] (?:?) - SQLException during the execution of the query (for a
A): Invalid argument value: Not unique table/alias: 'A1'
java.sql.SQLException: Invalid argument value: Not unique table/alias: 'A1'
at com.mysql.jdbc.MysqlIO.sendCommand(Unknown Source)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(Unknown Source)
at com.mysql.jdbc.Connection.execSQL(Unknown Source)
at com.mysql.jdbc.PreparedStatement.executeQuery(Unknown Source)
at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown
Source)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(Unknown Source)
at
org.apache.ojb.broker.singlevm.RsIteratorFactoryImpl.createRsIterator(Unknow
n Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getRsIteratorFromQuery(
Unknown Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getIteratorFromQuery(Un
known Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
known Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
known Source)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Un
known Source)
at
org.apache.ojb.broker.singlevm.DelegatingPersistenceBroker.getCollectionByQu
ery(Unknown Source)
at org.apache.ojb.odmg.oql.OQLQueryImpl.execute(Unknown Source)
at Main.main(Main.java:54)
Well, my question is: Am I doing something wrong or sqlGenerator got a
bug/feature? :)
Thanks in advance
Jo�o
PS: Mapping is below:
<class-descriptor class="A" table="A">
<field-descriptor
name="idInternal"
column="idInternal"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
id="1"
/>
<field-descriptor
name="name"
column="name"
jdbc-type="VARCHAR"/>
<collection-descriptor name="bs" element-class-ref="B"
indirection-table="AB">
<fk-pointing-to-this-class column="keyA" />
<fk-pointing-to-element-class column="keyB" />
</collection-descriptor>
</class-descriptor>
<class-descriptor class="B" table="B">
<field-descriptor name="idInternal" column="idInternal"
jdbc-type="INTEGER" primarykey="true" autoincrement="true"/>
<field-descriptor name="name" column="name" jdbc-type="VARCHAR"/>
<collection-descriptor name="cs" element-class-ref="C" proxy="true">
<inverse-foreignkey field-ref="idB"/>
</collection-descriptor>
<collection-descriptor name="ds" element-class-ref="D" proxy="true">
<inverse-foreignkey field-ref="idB"/>
</collection-descriptor>
<collection-descriptor name="as" element-class-ref="A"
indirection-table="AB">
<fk-pointing-to-this-class column="keyB" />
<fk-pointing-to-element-class column="keyA" />
</collection-descriptor>
</class-descriptor>
<class-descriptor class="C"
table="C">
<field-descriptor
name="idInternal"
column="idInternal"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor name="idB" column="keyB" jdbc-type="INTEGER"/>
<reference-descriptor name="b" class-ref="B">
<foreignkey field-ref="keyB"/>
</reference-descriptor>
</class-descriptor>
<class-descriptor class="D" table="D">
<field-descriptor
name="idInternal"
column="idInternal"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor name="idB" column="keyB" jdbc-type="INTEGER"/>
<reference-descriptor name="b" class-ref="B">
<foreignkey field-ref="idB"/>
</reference-descriptor>
</class-descriptor>
PS2:Now the database sql:
/*
Mascon Dump
Source Host: localhost
Source Server Version: 3.23.53-max-nt
Source Database: testDB
Date: 2003-04-01 11:35:58
*/
use testDB ;
#----------------------------
# Table structure for A
#----------------------------
drop table if exists A;
create table A (
idInternal int(11) not null default '0',
name varchar(50) not null,
primary key (idInternal))
type=MyISAM;
#----------------------------
# Table structure for AB
#----------------------------
drop table if exists AB;
create table AB (
keyA int(11) not null default '0',
keyB int(11) not null default '0')
type=MyISAM;
#----------------------------
# Table structure for B
#----------------------------
drop table if exists B;
create table B (
idInternal int(11) not null default '0',
keyA int(11) not null default '0',
name varchar(50) not null,
primary key (idInternal))
type=MyISAM;
#----------------------------
# Table structure for C
#----------------------------
drop table if exists C;
create table C (
idInternal int(11) not null default '0',
keyB int(11) not null default '0',
primary key (idInternal))
type=MyISAM;
#----------------------------
# Table structure for D
#----------------------------
drop table if exists D;
create table D (
idInternal int(11) not null default '0',
keyB int(11) not null default '0',
primary key (idInternal))
type=MyISAM;
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
