Hi there, I have a problem with a many to many relationship.
Company has users and users can be part of many companies.
Using these queries i get the collections of company and users:
oql = db.getOQLQuery("SELECT u FROM com.imaxin.dxcm.bean.User u WHERE
u.companies.idcompany=101 AND u.iduser>100");
oql = db.getOQLQuery("SELECT c FROM com.imaxin.dxcm.bean.Company c WHERE
c.users.iduser>100 AND c.idcompany=101");
using this mapping:
<mapping>
.
.
<class name="com.imaxin.dxcm.bean.Company" identity="idcompany">
<description>Company</description>
<cache-type type="none"/>
<map-to table="company" xml="company"/>
<field name="idcompany" type="integer" required="true">
<sql name="company_id" type="integer" read-only="true"/>
<bind-xml node="element"/>
</field>
.
.
<field name="users" type="com.imaxin.dxcm.bean.User"
collection="arraylist" lazy="true">
<sql name="id_user" many-table="user_company"
many-key="id_company"/>
</field>
</class>
.
.
.
<class name="com.imaxin.dxcm.bean.User" identity="iduser">
<description>User</description>
<cache-type type="none" />
<map-to table="user" xml="user"/>
<field name="iduser" type="integer" required="true">
<sql name="user_id" type="integer"/>
<bind-xml node="element"/>
</field>
.
.
<field name="companies" type="com.imaxin.dxcm.bean.Company"
collection="arraylist" lazy="true">
<sql name="id_company" many-table="user_company"
many-key="id_user"/>
</field>
</class>
.
.
</mapping>
The problem arises when i try to db.load a specific user.
User u = (User) db.loab(User.class,new Integer(101)); throws this exception:
A fatal error occurred while loading com.imaxin.dxcm.bean.User using SQL:
SELECT
"USER"."USER_ID","USER_COMPANY"."ID_COMPANY","USER"."SENDPASSWD","USER"."ACTIVE","USER"."DATECHANGE","USER"."DATECREATION","USER"."DATEDELETE","USER"."LASTUSER"
FROM "USER_COMPANY","USER" WHERE
"USER"."USER_ID"="USER_COMPANY"."ID_USER"(+) AND "USER"."USER_ID"=?
java.sql.SQLException: Invalid column index
This problem doesnt arise if i do a OQL query first before doing the db.load
and i iterate the collection of users untill i get the one i want.
oql = db.getOQLQuery("SELECT u FROM com.imaxin.dxcm.bean.User u WHERE
u.companies.idcompany=101 AND u.iduser>100");
User u = (User) db.loab(User.class,new Integer(101));
Thank you in advance.
---------------------------------------------------------------------
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
