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
|