Re: query alias problem: 1:1

[Jakob Braeuchi]

hi phil,

try to modify the method

    private TableAlias getTableAliasForPath(String aPath, String aUserAlias, 
String anOriginalPath)
    {
        // if (aUserAlias == null || !anOriginalPath.equals(aPath))
        if (aUserAlias == null)
        {
            return getTableAliasForPath(aPath);
        }
        else
        {
            return getTableAliasForPath(aUserAlias);
        }
    }

of class SqlQueryStatement. i did a small test and it seems to work:

before:

SELECT A0.idInternal,A0.name FROM A A0
INNER JOIN AB A1 ON A0.idInternal=A1.keyA
INNER JOIN B A2 ON A1.keyB=A2.idInternal
INNER JOIN C A3 ON A2.idInternal=A3.keyB
INNER JOIN C A4 ON A2.idInternal=A4.keyB
WHERE ( A3.idInternal = '101') AND  (A4.idInternal = '202')
after:

SELECT A0.idInternal,A0.name FROM A A0
INNER JOIN AB A1 ON A0.idInternal=A1.keyA
INNER JOIN B A2 ON A1.keyB=A2.idInternal
INNER JOIN C A3 ON A2.idInternal=A3.keyB
INNER JOIN AB A4 ON A0.idInternal=A4.keyA
INNER JOIN B A5 ON A4.keyB=A5.idInternal
INNER JOIN C A6 ON A5.idInternal=A6.keyB WHERE ( A3.idInternal = '101') AND 
(A6.idInternal = '202')

i haven't done any further testing !

jakob

Jakob Braeuchi wrote:

hi phil,

you're right, both B and C must have in your case.
so the alias should affect all segements of the path, not only the last 
as it does now. but i'm not sure if this is always the case ?

jakob

Phil Warrick wrote:

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]

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

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