Hi, All
I am confused with how view-entity works.
I want to do the following query:
select ss.id
from region r,regioncountry rc, countries c, customerinfo cu,
subscribedservice ss
where r.id=rc.regionid and rc.countryid=c.id and c.name=cu.country and
cu.id=ss.customerid and r.service=ss.service
and r.id=41 and r.service='Customer'
So I define a view-entity as follows:
<view-entity entity-name="RegionCustomerService"
package-name="com.aicent.ccb">
<member-entity entity-alias="R" entity-name="Region" />
<member-entity entity-alias="RC"
entity-name="Regioncountry" />
<member-entity entity-alias="C" entity-name="Countries"
/>
<member-entity entity-alias="CU"
entity-name="Customerinfo" />
<member-entity entity-alias="SS"
entity-name="Subscribedservice" />
<alias name="id" field="id" entity-alias="R" />
<alias name="service" field="service" entity-alias="R"
/>
<alias name="ssid" field="id" entity-alias="SS" />
<view-link entity-alias="R" rel-entity-alias="RC">
<key-map field-name="id"
rel-field-name="regionid" />
</view-link>
<view-link entity-alias="RC" rel-entity-alias="C">
<key-map field-name="countryid"
rel-field-name="id" />
</view-link>
<view-link entity-alias="C" rel-entity-alias="CU">
<key-map field-name="name"
rel-field-name="country" />
</view-link>
<view-link entity-alias="CU" rel-entity-alias="SS" >
<key-map field-name="id"
rel-field-name="customerid" />
</view-link>
<view-link entity-alias="R" rel-entity-alias="SS" >
<key-map field-name="service"
rel-field-name="service" />
</view-link>
</view-entity>
Then an error occurred:
Failure in findByCondition operation for entity [RegionCustomerService]:
org.ofbiz.entity.GenericDataSourceException: SQL Exception while
executing the following:SELECT R.ID, R.SERVICE, SS.ID FROM REGION R
INNER JOIN REGIONCOUNTRY RC ON R.ID = RC.REGIONID INNER JOIN COUNTRIES C
ON RC.COUNTRYID = C.ID INNER JOIN CUSTOMERINFO CU ON C.NAME = CU.COUNTRY
INNER JOIN SUBSCRIBEDSERVICE SS ON CU.ID = SS.CUSTOMERID INNER JOIN
SUBSCRIBEDSERVICE SS ON R.SERVICE = SS.SERVICE WHERE (R.ID = ? AND
R.SERVICE = ?) (Not unique table/alias: 'SS'). Rolling back transaction.
Exception: org.ofbiz.entity.GenericDataSourceException
Message: SQL Exception while executing the following:SELECT R.ID,
R.SERVICE, SS.ID FROM REGION R INNER JOIN REGIONCOUNTRY RC ON R.ID =
RC.REGIONID INNER JOIN COUNTRIES C ON RC.COUNTRYID = C.ID INNER JOIN
CUSTOMERINFO CU ON C.NAME = CU.COUNTRY INNER JOIN SUBSCRIBEDSERVICE SS
ON CU.ID = SS.CUSTOMERID INNER JOIN SUBSCRIBEDSERVICE SS ON R.SERVICE =
SS.SERVICE WHERE (R.ID = ? AND R.SERVICE = ?) (Not unique table/alias:
'SS')
---- cause
---------------------------------------------------------------------
Exception: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException
Message: Not unique table/alias: 'SS'
How should I define the view-entity to avoid the error according to my
query above?
thank you all.
Best Regards,
Jack Liu
