[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
[eclipselink-users] Join Attributes for a cyclic ER diagram

Hello All

I have the following tables in my database:
(primary keys are underlined)

1. Customer
ÂÂÂ custid - int
ÂÂÂ name - varchar

2. Order
ÂÂÂ orderid - int
ÂÂÂ date - date
ÂÂÂ custid - int (foreign key reference) many-to-one (order - customer) customer who ordered
ÂÂÂ invoiceid - int (foreign key reference) one-to-one (order - invoice)

3. Invoice
ÂÂÂ invoiceid - int
ÂÂÂ date - date
ÂÂÂ custid - int (foreign key reference) many-to-one (order - customer) receiver relationship
ÂÂÂ orderid - int (foreign key reference) one-to-one (order - invoice)

I am using Dynamic Persistence in Eclipselink. I have created dynamic types corresponding to each of these tables with the relevant relationships.
I am using a ReadAllQuery to query the Customer dynamic type.

This is how I have established the mappings:

ÂÂÂ ÂÂÂ custBuilder.addOneToManyMapping("cust9.order9.custid", orderBuilder.getType(), "order9.custid");
ÂÂÂ ÂÂÂ orderBuilder.addOneToOneMapping("cust9.order9.custid", custBuilder.getType(), "order9.custid");

ÂÂÂ ÂÂÂ custBuilder.addOneToManyMapping("cust9.invoice9.custid", invoiceBuilder.getType(), "invoice9.custid");
ÂÂÂ ÂÂÂ invoiceBuilder.addOneToOneMapping("cust9.invoice9.custid", invoiceBuilder.getType(), "invoice9.custid");

ÂÂÂ ÂÂÂ orderBuilder.addOneToOneMapping("invoice9.order9.invoiceid", invoiceBuilder.getType(), "order9.invoiceid");
ÂÂÂ ÂÂÂ invoiceBuilder.addOneToOneMapping("invoice9.order9.invoiceid", orderBuilder.getType(), "invoice9.orderid");



For a particular customer I wish to find out all orders placed by the customer and the details of customers who received the invoice corresponding to each order.

I am generating the join attribute _expression_ as follows:

ÂÂÂ ÂÂÂ query.getExpressionBuilder().anyOf("cust9.order9.custid").get("invoice9.order9.invoiceid").get("cust9.invoice9.custid");
ÂÂÂ ÂÂ Â// Note that there is a cyclic relationship between the customer, order and invoice entities

However this generates the following incorrect SQL query:

SELECT DISTINCT t1.custid, t1.name, t0.orderid, t0.qty, t0.custid, t0.invoiceid, t2.invoiceid, t2.value, t2.custid, t2.orderid, t3.invoiceid, t3.value, t3.custid, t3.orderid
FROM invoice9 t3, invoice9 t2, cust9 t1, order9 t0
WHERE t0.custid = t1.custid AND t2.invoiceid = t0.invoiceid AND t3.invoice = t2.custid


My understanding is that the correct SQL query should be:

SELECT DISTINCT t1.custid, t1.name, t0.orderid, t0.qty, t0.custid, t0.invoiceid, t2.invoiceid, t2.value, t2.custid, t2.orderid, t3.invoiceid, t3.value, t3.custid, t3.orderid
FROM invoice9 t3, invoice9 t2, cust9 t1, order9 t0
WHERE t0.custid = t1.custid AND t2.invoiceid = t0.invoiceid AND t3.custid = t2.custid


What am I doing wrong here? Could you please tell me what is wrong in the way I am expecting the _expression_ builder or Eclipselink mappings to behave?

Thanks in Advance


--
Thanks and Regards
Rohit Banga
Member Technical Staff
Oracle Server Technologies