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: [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]
--------------------------------------------------------------------- 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]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
