query alias problem: 1:1

Phil Warrick Mon, 15 Mar 2004 07:03:26 -0800

Hi Jakob,

I've run into a difficulty using aliases on a model with a path involving a 1-M followed by a 1-1 association, as in:

A-1------M-B-1------1-C

I want to find all A's having a B with c.cAttrib = 'foo1' AND another B with c.cAttrib = 'foo2'

I use the following code:

Criteria crit1 = new Criteria();
crit1.setAlias("alias1");
crit1.addEqualTo("allBs.c.cAttrib", new String("foo1"));

Criteria crit2 = new Criteria();
crit2.setAlias("alias2");
crit1.addEqualTo("allBs.c.cAttrib", new String("foo2"));

crit1.addAndCriteria(crit2);
                        
Query query = new QueryByCriteria(A.class, crit1);

The following SQL is generated by ojb (assume that the class 'X' corresponds to table 'XTABLE')

SELECT A0.ID FROM ATABLE A0,BTABLE A1,CTABLE A2,CTABLE A3 WHERE
        A1.C_ID=A2.ID AND A1.C_ID=A3.ID AND
        A0.ID=A1.A_ID AND
        (( A2.CODE =  ? ) AND  (A3.CODE =  ? ))

I believe that both the B _and_ C tables must have two SQL aliases each to correctly complete the query, as in:

SELECT A0.ID FROM ATABLE A0,BTABLE A1,CTABLE A2,CTABLE A3,BTABLE A4 WHERE
        A1.C_ID=A2.ID AND A4.C_ID=A3.ID AND
        A0.ID=A1.A_ID AND A4.ID=A1.A_ID
        (( A2.CODE =  ? ) AND  (A3.CODE =  ? ))

(Perhaps there is better SQL possible than this?)

I've been looking carefully at org.apache.ojr.broker.accesslayer.sql.SqlQueryStatement.java

and was wondering if you could suggest the best approach to correctly addressing this limitation.

Thanks,

Phil

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

query alias problem: 1:1

Reply via email to