[eclipselink-users] Join Attributes for a cyclic ER diagram

[Rohit Banga <rohit.banga@xxxxxxxxxx>]

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