You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Phil Warrick <ph...@mcgill.ca> on 2004/03/15 16:03:16 UTC
query alias problem: 1:1
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: query alias problem: 1:1
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: query alias problem: 1:1
Posted by Phil Warrick <ph...@mcgill.ca>.
Jakob,
> hi phil,
>
> Phil Warrick wrote:
>
> > Hi Jakob,
> >
> > And I could try extending the current code to ?quickly? see whether
> > there are other issues, and perhaps later all this could be refactored.
> >
> > Often I have too much on my plate integrating components in my EJB
> > system to spend extra time with one component (i.e. ojb), but right now
> > is a good time for me because the system is relatively stable and the
> > users are happy. I'd like to give something back again to the ojb
> > project which has allowed me to concentrate on higher-level issues. And
> > this will in turn allow me to entend the query functionality that I need
>
> well this sounds great !
>
> imo refactoring SqlQueryStatement to pass the Criteria to the relevant
> methods
> start early in the call-tree:
>
> appendSQLClause knows the SelectionCriteria which has a link to its parent:
> getCriteria(). another crucial method is getAttributeInfo which
> currently only
> gets the attribute-name as parameter.
>
> if you then have the Criteria available in the relevant methods (don't
> know all
> of them right now) you'll need to extend Criteria to hold path-hints and
> path-segments for the alias. extending Criteria is imo the easier part,
> but it's
> useless without the changes in SqlQueryStatement. another issue is backward
> compatibility.
>
> > in my system. In the meantime, I am prototyping with SQL queries, as
> in:
>
> what are you prototyping using SQL ?
>
All the queries that I can't do at the moment with the PB-api, as we
have discussed, that is per-Criteria specification of
1) aliases
2) hints
I need to generate annual reports containing hundreds of queries each.
They operate on large tables: worst case table collects ~1M rows per year.
The problem domain is obstetrics: a statistical database of all mothers
and babies for births at a McGill University teaching hospital.
Phil
> jakob
>
> >
> > Query query = QueryFactory.newQuery(A.class, sqlString);
> >
> > The object model in small but complex and so these are very nasty
> > queries, and I literally need hundreds of them :(
> >
> > Phil
> >
> >> hi phil,
> >>
> >> the problem is that most of the methods in SqlQueryStatement do not
> >> know from which criteria they are called, so we'd have to add Criteria
> >> as additional parameter. actually it's almost the same problem as in
> >> my post regarding aliases.
> >> in the future (no one knows when...) i'd like to completely refactor
> >> SqlQueryStatement because it's getting larger and larger... actually
> >> i'd like to separate join-/alias-handling from building the
> >> sql-string. during this refactoring i could integrate criteria-base
> >> paths and hints.
> >>
> >> jakob
> >>
> >> Phil Warrick wrote:
> >>
> >>> Jakob,
> >>>
> >>> This looks good and I think it also applies to the other cases that I
> >>> mentioned. Is it a relatively simple change to SqlQueryStatement?
> >>>
> >>> Phil
> >>>
> >>> Jakob Braeuchi wrote:
> >>>
> >>>> hi phil,
> >>>>
> >>>> imo the best solution would be to declare for what segments of the
> >>>> path the alias is valid:
> >>>>
> >>>> crit1 = new Criteria();
> >>>> crit1.setAlias("bToC1","cs");
> >>>> crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
> >>>>
> >>>> crit2 = new Criteria();
> >>>> crit2.setAlias("bToC2","cs");
> >>>> crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
> >>>>
> >>>> crit1.addAndCriteria(crit2);
> >>>> query = new QueryByCriteria(A.class, crit1);
> >>>>
> >>>> this would result in A,B,C1 and C2.
> >>>>
> >>>> crit1 = new Criteria();
> >>>> crit1.setAlias("bToC1","bs.cs");
> >>>> crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
> >>>>
> >>>> crit2 = new Criteria();
> >>>> crit2.setAlias("bToC2","bs.cs");
> >>>> crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
> >>>>
> >>>> crit1.addAndCriteria(crit2);
> >>>> query = new QueryByCriteria(A.class, crit1);
> >>>>
> >>>> this would result in A,B1,B2,C1 and C2.
> >>>>
> >>>> it's not as elegant as an automatic solution, but i think i
> >>>> sufficiently flexible.
> >>>>
> >>>> jakob
> >>>>
> >>>> Phil Warrick wrote:
> >>>>
> >>>>> Hi Jakob,
> >>>>>
> >>>>> You're right, there's some more general rule at work here.
> >>>>>
> >>>>> I think that if the path is 1-M or 1-1 followed by another 1-1, the
> >>>>> alias should affect all segments of the path.
> >>>>>
> >>>>> If the path is 1-M followed by 1-M, there should be a choice.
> >>>>> Consider the following:
> >>>>>
> >>>>> X-1--------M-Y-1--------M-Z
> >>>>>
> >>>>> At least two different query are possible:
> >>>>>
> >>>>> 1) Find all X's having a Y with one Z with z.zAttrib = 'foo' AND
> >>>>> another Z with z.zAttrib = 'bar'
> >>>>>
> >>>>> (i.e. the same Y instance)
> >>>>>
> >>>>> 2) Find all X's having a Y with z.zAttrib = 'foo' AND
> >>>>> having another Y with z.zAttrub = 'bar'
> >>>>>
> >>>>> (i.e. two Y instances)
> >>>>>
> >>>>> In case 1) only Z needs 2 SQL aliases.
> >>>>> In case 2) Y and Z both need 2 SQL aliases.
> >>>>>
> >>>>> (I just saw your response...)
> >>>>>
> >>>>> Phil
> >>>>>
> >>>>>> 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: ojb-user-unsubscribe@db.apache.org
> >>>>>> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >>>>>> >
> >>>>>> >
> >>>>>>
> >>>>>>
> ---------------------------------------------------------------------
> >>>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >>>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
> >>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> ---------------------------------------------------------------------
> >>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
> >>>>>
> >>>>>
> >>>>
> >>>> ---------------------------------------------------------------------
> >>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >>>> For additional commands, e-mail: ojb-user-help@db.apache.org
> >>>>
> >>>
> >>>
> >>>
> >>> ---------------------------------------------------------------------
> >>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >>> For additional commands, e-mail: ojb-user-help@db.apache.org
> >>>
> >>>
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >> For additional commands, e-mail: ojb-user-help@db.apache.org
> >>
> >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: query alias problem: 1:1
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi phil,
Phil Warrick wrote:
> Hi Jakob,
>
> And I could try extending the current code to ?quickly? see whether
> there are other issues, and perhaps later all this could be refactored.
>
> Often I have too much on my plate integrating components in my EJB
> system to spend extra time with one component (i.e. ojb), but right now
> is a good time for me because the system is relatively stable and the
> users are happy. I'd like to give something back again to the ojb
> project which has allowed me to concentrate on higher-level issues. And
> this will in turn allow me to entend the query functionality that I need
well this sounds great !
imo refactoring SqlQueryStatement to pass the Criteria to the relevant methods
start early in the call-tree:
appendSQLClause knows the SelectionCriteria which has a link to its parent:
getCriteria(). another crucial method is getAttributeInfo which currently only
gets the attribute-name as parameter.
if you then have the Criteria available in the relevant methods (don't know all
of them right now) you'll need to extend Criteria to hold path-hints and
path-segments for the alias. extending Criteria is imo the easier part, but it's
useless without the changes in SqlQueryStatement. another issue is backward
compatibility.
> in my system. In the meantime, I am prototyping with SQL queries, as in:
what are you prototyping using SQL ?
jakob
>
> Query query = QueryFactory.newQuery(A.class, sqlString);
>
> The object model in small but complex and so these are very nasty
> queries, and I literally need hundreds of them :(
>
> Phil
>
>> hi phil,
>>
>> the problem is that most of the methods in SqlQueryStatement do not
>> know from which criteria they are called, so we'd have to add Criteria
>> as additional parameter. actually it's almost the same problem as in
>> my post regarding aliases.
>> in the future (no one knows when...) i'd like to completely refactor
>> SqlQueryStatement because it's getting larger and larger... actually
>> i'd like to separate join-/alias-handling from building the
>> sql-string. during this refactoring i could integrate criteria-base
>> paths and hints.
>>
>> jakob
>>
>> Phil Warrick wrote:
>>
>>> Jakob,
>>>
>>> This looks good and I think it also applies to the other cases that I
>>> mentioned. Is it a relatively simple change to SqlQueryStatement?
>>>
>>> Phil
>>>
>>> Jakob Braeuchi wrote:
>>>
>>>> hi phil,
>>>>
>>>> imo the best solution would be to declare for what segments of the
>>>> path the alias is valid:
>>>>
>>>> crit1 = new Criteria();
>>>> crit1.setAlias("bToC1","cs");
>>>> crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
>>>>
>>>> crit2 = new Criteria();
>>>> crit2.setAlias("bToC2","cs");
>>>> crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
>>>>
>>>> crit1.addAndCriteria(crit2);
>>>> query = new QueryByCriteria(A.class, crit1);
>>>>
>>>> this would result in A,B,C1 and C2.
>>>>
>>>> crit1 = new Criteria();
>>>> crit1.setAlias("bToC1","bs.cs");
>>>> crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
>>>>
>>>> crit2 = new Criteria();
>>>> crit2.setAlias("bToC2","bs.cs");
>>>> crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
>>>>
>>>> crit1.addAndCriteria(crit2);
>>>> query = new QueryByCriteria(A.class, crit1);
>>>>
>>>> this would result in A,B1,B2,C1 and C2.
>>>>
>>>> it's not as elegant as an automatic solution, but i think i
>>>> sufficiently flexible.
>>>>
>>>> jakob
>>>>
>>>> Phil Warrick wrote:
>>>>
>>>>> Hi Jakob,
>>>>>
>>>>> You're right, there's some more general rule at work here.
>>>>>
>>>>> I think that if the path is 1-M or 1-1 followed by another 1-1, the
>>>>> alias should affect all segments of the path.
>>>>>
>>>>> If the path is 1-M followed by 1-M, there should be a choice.
>>>>> Consider the following:
>>>>>
>>>>> X-1--------M-Y-1--------M-Z
>>>>>
>>>>> At least two different query are possible:
>>>>>
>>>>> 1) Find all X's having a Y with one Z with z.zAttrib = 'foo' AND
>>>>> another Z with z.zAttrib = 'bar'
>>>>>
>>>>> (i.e. the same Y instance)
>>>>>
>>>>> 2) Find all X's having a Y with z.zAttrib = 'foo' AND
>>>>> having another Y with z.zAttrub = 'bar'
>>>>>
>>>>> (i.e. two Y instances)
>>>>>
>>>>> In case 1) only Z needs 2 SQL aliases.
>>>>> In case 2) Y and Z both need 2 SQL aliases.
>>>>>
>>>>> (I just saw your response...)
>>>>>
>>>>> Phil
>>>>>
>>>>>> 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: ojb-user-unsubscribe@db.apache.org
>>>>>> > For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>>> >
>>>>>> >
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>>
>>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: query alias problem: 1:1
Posted by Phil Warrick <ph...@mcgill.ca>.
Hi Jakob,
And I could try extending the current code to ?quickly? see whether
there are other issues, and perhaps later all this could be refactored.
Often I have too much on my plate integrating components in my EJB
system to spend extra time with one component (i.e. ojb), but right now
is a good time for me because the system is relatively stable and the
users are happy. I'd like to give something back again to the ojb
project which has allowed me to concentrate on higher-level issues. And
this will in turn allow me to entend the query functionality that I need
in my system. In the meantime, I am prototyping with SQL queries, as in:
Query query = QueryFactory.newQuery(A.class, sqlString);
The object model in small but complex and so these are very nasty
queries, and I literally need hundreds of them :(
Phil
> hi phil,
>
> the problem is that most of the methods in SqlQueryStatement do not know
> from which criteria they are called, so we'd have to add Criteria as
> additional parameter. actually it's almost the same problem as in my
> post regarding aliases.
> in the future (no one knows when...) i'd like to completely refactor
> SqlQueryStatement because it's getting larger and larger... actually i'd
> like to separate join-/alias-handling from building the sql-string.
> during this refactoring i could integrate criteria-base paths and hints.
>
> jakob
>
> Phil Warrick wrote:
>
>> Jakob,
>>
>> This looks good and I think it also applies to the other cases that I
>> mentioned. Is it a relatively simple change to SqlQueryStatement?
>>
>> Phil
>>
>> Jakob Braeuchi wrote:
>>
>>> hi phil,
>>>
>>> imo the best solution would be to declare for what segments of the
>>> path the alias is valid:
>>>
>>> crit1 = new Criteria();
>>> crit1.setAlias("bToC1","cs");
>>> crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
>>>
>>> crit2 = new Criteria();
>>> crit2.setAlias("bToC2","cs");
>>> crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
>>>
>>> crit1.addAndCriteria(crit2);
>>> query = new QueryByCriteria(A.class, crit1);
>>>
>>> this would result in A,B,C1 and C2.
>>>
>>> crit1 = new Criteria();
>>> crit1.setAlias("bToC1","bs.cs");
>>> crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
>>>
>>> crit2 = new Criteria();
>>> crit2.setAlias("bToC2","bs.cs");
>>> crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
>>>
>>> crit1.addAndCriteria(crit2);
>>> query = new QueryByCriteria(A.class, crit1);
>>>
>>> this would result in A,B1,B2,C1 and C2.
>>>
>>> it's not as elegant as an automatic solution, but i think i
>>> sufficiently flexible.
>>>
>>> jakob
>>>
>>> Phil Warrick wrote:
>>>
>>>> Hi Jakob,
>>>>
>>>> You're right, there's some more general rule at work here.
>>>>
>>>> I think that if the path is 1-M or 1-1 followed by another 1-1, the
>>>> alias should affect all segments of the path.
>>>>
>>>> If the path is 1-M followed by 1-M, there should be a choice.
>>>> Consider the following:
>>>>
>>>> X-1--------M-Y-1--------M-Z
>>>>
>>>> At least two different query are possible:
>>>>
>>>> 1) Find all X's having a Y with one Z with z.zAttrib = 'foo' AND
>>>> another Z with z.zAttrib = 'bar'
>>>>
>>>> (i.e. the same Y instance)
>>>>
>>>> 2) Find all X's having a Y with z.zAttrib = 'foo' AND
>>>> having another Y with z.zAttrub = 'bar'
>>>>
>>>> (i.e. two Y instances)
>>>>
>>>> In case 1) only Z needs 2 SQL aliases.
>>>> In case 2) Y and Z both need 2 SQL aliases.
>>>>
>>>> (I just saw your response...)
>>>>
>>>> Phil
>>>>
>>>>> 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: ojb-user-unsubscribe@db.apache.org
>>>>> > For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>> >
>>>>> >
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>>
>>>>
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: query alias problem: 1:1
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi phil,
the problem is that most of the methods in SqlQueryStatement do not know from
which criteria they are called, so we'd have to add Criteria as additional
parameter. actually it's almost the same problem as in my post regarding aliases.
in the future (no one knows when...) i'd like to completely refactor
SqlQueryStatement because it's getting larger and larger... actually i'd like to
separate join-/alias-handling from building the sql-string. during this
refactoring i could integrate criteria-base paths and hints.
jakob
Phil Warrick wrote:
> Jakob,
>
> This looks good and I think it also applies to the other cases that I
> mentioned. Is it a relatively simple change to SqlQueryStatement?
>
> Phil
>
> Jakob Braeuchi wrote:
>
>> hi phil,
>>
>> imo the best solution would be to declare for what segments of the
>> path the alias is valid:
>>
>> crit1 = new Criteria();
>> crit1.setAlias("bToC1","cs");
>> crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
>>
>> crit2 = new Criteria();
>> crit2.setAlias("bToC2","cs");
>> crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
>>
>> crit1.addAndCriteria(crit2);
>> query = new QueryByCriteria(A.class, crit1);
>>
>> this would result in A,B,C1 and C2.
>>
>> crit1 = new Criteria();
>> crit1.setAlias("bToC1","bs.cs");
>> crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
>>
>> crit2 = new Criteria();
>> crit2.setAlias("bToC2","bs.cs");
>> crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
>>
>> crit1.addAndCriteria(crit2);
>> query = new QueryByCriteria(A.class, crit1);
>>
>> this would result in A,B1,B2,C1 and C2.
>>
>> it's not as elegant as an automatic solution, but i think i
>> sufficiently flexible.
>>
>> jakob
>>
>> Phil Warrick wrote:
>>
>>> Hi Jakob,
>>>
>>> You're right, there's some more general rule at work here.
>>>
>>> I think that if the path is 1-M or 1-1 followed by another 1-1, the
>>> alias should affect all segments of the path.
>>>
>>> If the path is 1-M followed by 1-M, there should be a choice.
>>> Consider the following:
>>>
>>> X-1--------M-Y-1--------M-Z
>>>
>>> At least two different query are possible:
>>>
>>> 1) Find all X's having a Y with one Z with z.zAttrib = 'foo' AND
>>> another Z with z.zAttrib = 'bar'
>>>
>>> (i.e. the same Y instance)
>>>
>>> 2) Find all X's having a Y with z.zAttrib = 'foo' AND
>>> having another Y with z.zAttrub = 'bar'
>>>
>>> (i.e. two Y instances)
>>>
>>> In case 1) only Z needs 2 SQL aliases.
>>> In case 2) Y and Z both need 2 SQL aliases.
>>>
>>> (I just saw your response...)
>>>
>>> Phil
>>>
>>>> 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: ojb-user-unsubscribe@db.apache.org
>>>> > For additional commands, e-mail: ojb-user-help@db.apache.org
>>>> >
>>>> >
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: query alias problem: 1:1
Posted by Phil Warrick <ph...@mcgill.ca>.
Jakob,
This looks good and I think it also applies to the other cases that I
mentioned. Is it a relatively simple change to SqlQueryStatement?
Phil
Jakob Braeuchi wrote:
> hi phil,
>
> imo the best solution would be to declare for what segments of the path
> the alias is valid:
>
> crit1 = new Criteria();
> crit1.setAlias("bToC1","cs");
> crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
>
> crit2 = new Criteria();
> crit2.setAlias("bToC2","cs");
> crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
>
> crit1.addAndCriteria(crit2);
> query = new QueryByCriteria(A.class, crit1);
>
> this would result in A,B,C1 and C2.
>
> crit1 = new Criteria();
> crit1.setAlias("bToC1","bs.cs");
> crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
>
> crit2 = new Criteria();
> crit2.setAlias("bToC2","bs.cs");
> crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
>
> crit1.addAndCriteria(crit2);
> query = new QueryByCriteria(A.class, crit1);
>
> this would result in A,B1,B2,C1 and C2.
>
> it's not as elegant as an automatic solution, but i think i sufficiently
> flexible.
>
> jakob
>
> Phil Warrick wrote:
>
>> Hi Jakob,
>>
>> You're right, there's some more general rule at work here.
>>
>> I think that if the path is 1-M or 1-1 followed by another 1-1, the
>> alias should affect all segments of the path.
>>
>> If the path is 1-M followed by 1-M, there should be a choice.
>> Consider the following:
>>
>> X-1--------M-Y-1--------M-Z
>>
>> At least two different query are possible:
>>
>> 1) Find all X's having a Y with one Z with z.zAttrib = 'foo' AND
>> another Z with z.zAttrib = 'bar'
>>
>> (i.e. the same Y instance)
>>
>> 2) Find all X's having a Y with z.zAttrib = 'foo' AND
>> having another Y with z.zAttrub = 'bar'
>>
>> (i.e. two Y instances)
>>
>> In case 1) only Z needs 2 SQL aliases.
>> In case 2) Y and Z both need 2 SQL aliases.
>>
>> (I just saw your response...)
>>
>> Phil
>>
>>> 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: ojb-user-unsubscribe@db.apache.org
>>> > For additional commands, e-mail: ojb-user-help@db.apache.org
>>> >
>>> >
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: query alias problem: 1:1
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi phil,
imo the best solution would be to declare for what segments of the path the
alias is valid:
crit1 = new Criteria();
crit1.setAlias("bToC1","cs");
crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
crit2 = new Criteria();
crit2.setAlias("bToC2","cs");
crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
crit1.addAndCriteria(crit2);
query = new QueryByCriteria(A.class, crit1);
this would result in A,B,C1 and C2.
crit1 = new Criteria();
crit1.setAlias("bToC1","bs.cs");
crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
crit2 = new Criteria();
crit2.setAlias("bToC2","bs.cs");
crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
crit1.addAndCriteria(crit2);
query = new QueryByCriteria(A.class, crit1);
this would result in A,B1,B2,C1 and C2.
it's not as elegant as an automatic solution, but i think i sufficiently flexible.
jakob
Phil Warrick wrote:
> Hi Jakob,
>
> You're right, there's some more general rule at work here.
>
> I think that if the path is 1-M or 1-1 followed by another 1-1, the
> alias should affect all segments of the path.
>
> If the path is 1-M followed by 1-M, there should be a choice. Consider
> the following:
>
> X-1--------M-Y-1--------M-Z
>
> At least two different query are possible:
>
> 1) Find all X's having a Y with one Z with z.zAttrib = 'foo' AND
> another Z with z.zAttrib = 'bar'
>
> (i.e. the same Y instance)
>
> 2) Find all X's having a Y with z.zAttrib = 'foo' AND
> having another Y with z.zAttrub = 'bar'
>
> (i.e. two Y instances)
>
> In case 1) only Z needs 2 SQL aliases.
> In case 2) Y and Z both need 2 SQL aliases.
>
> (I just saw your response...)
>
> Phil
>
>> 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: ojb-user-unsubscribe@db.apache.org
>> > For additional commands, e-mail: ojb-user-help@db.apache.org
>> >
>> >
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: query alias problem: 1:1
Posted by Phil Warrick <ph...@mcgill.ca>.
Hi Jakob,
You're right, there's some more general rule at work here.
I think that if the path is 1-M or 1-1 followed by another 1-1, the
alias should affect all segments of the path.
If the path is 1-M followed by 1-M, there should be a choice. Consider
the following:
X-1--------M-Y-1--------M-Z
At least two different query are possible:
1) Find all X's having a Y with one Z with z.zAttrib = 'foo' AND
another Z with z.zAttrib = 'bar'
(i.e. the same Y instance)
2) Find all X's having a Y with z.zAttrib = 'foo' AND
having another Y with z.zAttrub = 'bar'
(i.e. two Y instances)
In case 1) only Z needs 2 SQL aliases.
In case 2) Y and Z both need 2 SQL aliases.
(I just saw your response...)
Phil
> 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: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: query alias problem: 1:1
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org