what version of ojb do you use ?
jakob
Jo�o Luz wrote:
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]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
