Lorna --
Looks like a problem with case sensitivity.
Change
crit.addEqualTo("ExternalEntity.database_id", databaseId );
to
crit.addEqualTo("externalEntity.database_id", databaseId );
and things will probably work better.
Ron Gallagher
Atlanta, GA
[EMAIL PROTECTED]
>
> From: Lorna Morris <[EMAIL PROTECTED]>
> Date: 2003/03/12 Wed AM 11:37:08 EST
> To: [EMAIL PROTECTED]
> Subject: problem with join across 1:n relationships
>
> Hi,
>
> I am having problems doing queries with joins. Maybe it's because I'm
> using ojb0.9.2. I really must upgrade soon. I have been reading the
> section on joins in the documentation here:
> http://db.apache.org/ojb/query.html, but this may not apply to ojb0.9.2
> and I can't find anything in the mailing list archives.
>
> The problem is when I use QueryByCriteria to perform a query that joins
> 2 tables, I get some unexpected sql generated.
>
> I am doing a query by crieria, selecting objects from one table
> (reference) by a single value in a column of an associated table
> (external_entity). There is a 1-M association between
> external_entity:reference. Here is the snippet of java that performs the
> query and the class descriptors from the repository.xml for the two
> relevant classes:
>
>
> String databaseId = "A1234";
> Criteria crit = new Criteria();
> crit.addEqualTo("ExternalEntity.database_id", databaseId );
> Query query = new QueryByCriteria( Reference.class, crit );
> Collection col = broker.getCollectionByQuery(query);
>
>
> <!-- Definitions for uk.ac.ebi.integr8.data.Reference-->
> <class-descriptor
> class="uk.ac.ebi.integr8.data.Reference"
> table="REFERENCE"
> >
> <field-descriptor id="1"
> name="referenceId"
> column="REFERENCE_ID"
> jdbc-type="BIGINT"
> primarykey="true"
> autoincrement="true"
> />
> <field-descriptor id="2"
> name="externalEntityId"
> column="EXTERNAL_ENTITY_ID"
> jdbc-type="BIGINT"
> />
> <reference-descriptor
> name="externalEntity"
> class-ref="uk.ac.ebi.integr8.data.ExternalEntity"
> >
> <foreignkey field-id-ref="2"/>
> </reference-descriptor>
> </class-descriptor>
>
>
> <!-- Definitions for uk.ac.ebi.integr8.data.ExternalEntity -->
> <class-descriptor
> class="uk.ac.ebi.integr8.data.ExternalEntity"
> table="EXTERNAL_ENTITY"
> >
> <field-descriptor id="1"
> name="externalEntityId"
> column="EXTERNAL_ENTITY_ID"
> jdbc-type="BIGINT"
> primarykey="true"
> autoincrement="true"
> />
> <field-descriptor id="2"
> name="db_id"
> column="DATABASE_ID"
> jdbc-type="VARCHAR"
> />
> <collection-descriptor
> name="references"
> element-class-ref="uk.ac.ebi.integr8.data.Reference"
> auto-retrieve="false"
> auto-update="false"
> auto-delete="false"
> proxy="false"
> >
> <inverse-foreignkey field-id-ref="2"/>
> </collection-descriptor>
> </class-descriptor>
>
>
> The query that is generated by ojb is:
>
> SELECT A0.REFERENCE_ID,A0.EXTERNAL_ENTITY_ID FROM REFERENCE A0 WHERE
> ExternalEntity.database_id = ?
>
>
> But I was hoping for a query of this form to be generated, i.e. ojb
> would perform the join based on the foreign key information in the
> repository.xml:
>
> SELECT A0.REFERENCE_ID,A0.EXTERNAL_ENTITY_ID FROM REFERENCE A0,
> EXTERNAL_ENTITY EE WHERE EE.EXTERNAL_ENTITY_ID = AO.EXTERNAL_ENTITY_ID
> AND EE.database_id = ?
>
>
>
> Any help would be greatly appreciated,
>
> Many thanks
>
> Lorna
>
>
>
>
> ---------------------------------------------------------------------
> 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]
