Wrong SQL for uni-directional one-2-many-relationship

[jcordes]

Hi !

Im running into problems when trying to invoke a finder-method on a unidirectional one-many-relationship (Customer-Address). I defined a table-mapping and addresses get inserted accordingly. The query looks like this

SELECT OBJECT(a) FROM Customer c, IN (c.addresses) AS a WHERE c.id = ?1 AND a.type='shipping'

where the generated SQL is

SELECT t2_a.id FROM Customer t1_c, Address t2_a, Customer_Address t3_t1_c_to_addresses WHERE (t1_c.id = ? AND t2_a.type = 'shipping') AND t1_c.id=t4_t1_c_to_addresses.customer_id AND t2_a.id=t4_t
1_c_to_addresses.address_id

So I get SQL-errors because t4 should be t3. Is anybody experiencing the same problems or has a clue ?

Bye,

Jochen.

[pazu]

Make sure the section on your ejb-jar is correct.

[jcordes]

Hm ...

I'm quite sure that they are correct. But nevertheless, here are my configurations (BTW I'm using the recent CVS-Version for Jboss-3.0.0alpha):

ejb-jar:

<ejb-relation>
<ejb-relation-name>Customer-Address</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>CustomerHasAddress</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>CustomerEJB</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>addresses</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>AddressHasCustomer</ejb-relationship-role-name>
Many
<relationship-role-source>
<ejb-name>AddressEJB</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>

jbosscmp-jdbc:

<ejb-relation>
<ejb-relation-name>Customer-Address</ejb-relation-name>
<table-mapping>
<table-name>Customer_Address</table-name>
<create-table>true</create-table>
<remove-table>false</remove-table>
<ejb-relationship-role>
<ejb-relationship-role-name>CustomerHasAddress</ejb-relationship-role-name>
<table-key-fields>
<table-key-field>
<field-name>id</field-name>
<column-name>customer_id</column-name>
</table-key-field>
</table-key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>AddressHasCustomer</ejb-relationship-role-name>
<table-key-fields>
<table-key-field>
<field-name>id</field-name>
<column-name>address_id</column-name>
</table-key-field>
</table-key-fields>
</ejb-relationship-role>
</table-mapping>

I've got a lot more of these kind of relationships, so any help would be appreciated.

Bye,

Jochen.

[kingkong]

I don't know about table-mapping,

But if you'd use foreign-key-mapping, according to your ejb-jar,
your should look like this... I think :

<ejb-relation>
<ejb-relation-name>Customer-Address</ejb-relation-name>
<foreign-key-mapping>
<ejb-relationship-role>
<ejb-relationship-role-name>CustomerHasAddress</ejb-relationship-role-name>
<foreign-key-fields/> <!- the one side does not hold the foreign key -->
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>AddressHasCustomer</ejb-relationship-role-name>
<foreign-key-fields>
<foreign-key-field>
<field-name>id</field-name>
<column-name>address_id</column-name>
</foreign-key-field>
</foreign-key-fields>
</ejb-relationship-role>
</foreign-key-mapping>
</ejb-relation>

Hope it helps

dk.

[jcordes]

Hi !

I need a table-mapping because its a uni-directional relationship (a customer knows about his addresses, not the other way round). Therefore I can't use a foreign-key mapping without a third table. But I think the problem is not related (or at least not directly) to the mapping, because normal operations on the beans work perfectly, but rather could be a problem with the ejb-ql parser and the IN clause. Is somebody out there who got a query similar to mine up and running ? Im desperate in need of these kind of queries, otherwise i would have to do some workarounds (not talking about changing relationships, additional entities etc.) !!!

Jochen.

[dsundstrom]

This isn't true. The directionality of a relationship at the object level has no bearing on the database mapping. You can have a fk even if you have no accessor. Of course, you may want to restrict this in your database, and in that case you would have to use a relation table.

If your are using the 3.0 alpha, there was a bug in the parsing of IN clauses. You may want to build from source.

[jcordes]

Hi !

Off course you're right, but maybe I forgot to mention that my intention was to reuse the address-entity in different contexts. I always build Jboss 3.0 alpha from source (latest was 18.12.2001). Could you give me a hint when this issue with the IN clause has been solved ?

Jochen.

[dsundstrom]

Ok, I didn't closely read your message. This is a new bug. The bug I was referring to was fixed about a week after the alpha was released.

Unfortunately, I'm in the middle of rewriting the read ahead cache to support on-find and relationships, so it will be a week or more before I get to this.

Will you post a bug report at source forge?