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 Michael Hart <mi...@perceptek.com.au> on 2003/05/27 06:05:15 UTC
RE: Alias troubles (was: M to N alias/join troubles)
Hi Jakob,
> i did some tests with empty criteria and imo this works ok:
Oops... you're right - the example I gave was too minimal :-) Here's a
better one:
Criteria main_crit = new Criteria();
main_crit.setAlias("project");
Criteria persons_crit = new Criteria();
persons_crit.setAlias("persons");
persons_crit.addEqualTo("persons.id","2");
main_crit.addAndCriteria(persons_crit);
Query query = new QueryByCriteria(Project.class, main_crit);
Generates this:
SELECT A0.TITLE,A0.DESCRIPTION,A0.ID
FROM PROJECT A0
INNER JOIN PERSON_PROJECT A1 ON A0.ID=A1.PROJECT_ID
INNER JOIN PERSON A2 ON A1.PERSON_ID=A2.ID
WHERE A4.ID = 2
In the previous code snippet, if you don't set the alias for main_crit, then
it works fine. However, I got some weird results with other combinations and
it took me quite a while to realise that the name you give in the alias
makes quite a difference (as you may have hinted at in one of your previous
e-mails). For example:
Criteria main_crit = new Criteria();
Criteria roles_crit = new Criteria();
roles_crit.setAlias("roles");
roles_crit.addEqualTo("roles.roleName","whatever");
main_crit.addAndCriteria(roles_crit);
Query query = new QueryByCriteria(Person.class, main_crit);
Works fine, but changing the setAlias line to:
roles_crit.setAlias("roles1");
generates this bogus SQL:
SELECT A0.LASTNAME,A0.FIRSTNAME,A0.ID
FROM PERSON A0
INNER JOIN PERSON_PROJECT A1 ON A0.ID=A1.PERSON_ID
WHERE A2.ROLENAME = 'whatever'
But then adding another one (with a different alias) works (!):
Criteria main_crit = new Criteria();
Criteria roles_crit = new Criteria();
roles_crit.setAlias("roles");
roles_crit.addEqualTo("roles.roleName","whatever");
main_crit.addAndCriteria(roles_crit);
Criteria roles2_crit = new Criteria();
roles2_crit.setAlias("roles2");
roles2_crit.addEqualTo("roles.roleName","hello");
main_crit.addAndCriteria(roles2_crit);
Query query = new QueryByCriteria(Person.class, main_crit);
Note that if roles_crit is removed, or its setAlias changed, then it breaks
again. In this example, as long as you have the first Criteria with an alias
"roles", then you can add other Criteria with whatever alias you want.
This is also true for other aliases to other classes:
Criteria main_crit = new Criteria();
Criteria roles_crit = new Criteria();
roles_crit.setAlias("roles");
roles_crit.addEqualTo("roles.roleName","whatever");
main_crit.addAndCriteria(roles_crit);
Criteria proj_crit = new Criteria();
proj_crit.setAlias("projects1");
proj_crit.addEqualTo("projects.id","2");
main_crit.addAndCriteria(proj_crit);
Query query = new QueryByCriteria(Person.class, main_crit);
Works fine, and likewise so does:
Criteria main_crit = new Criteria();
Criteria proj_crit = new Criteria();
proj_crit.setAlias("projects");
proj_crit.addEqualTo("projects.id","2");
main_crit.addAndCriteria(proj_crit);
Criteria roles2_crit = new Criteria();
roles2_crit.setAlias("roles2");
roles2_crit.addEqualTo("roles.roleName","hello");
main_crit.addAndCriteria(roles2_crit);
Query query = new QueryByCriteria(Person.class, main_crit);
In any of the previous examples, if main_crit.setAlias is invoked, it breaks
again, unless of course you add a criteria to it, eg main_crit.addLike(...).
Very confused :-)
Michael
Re: Alias troubles
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi michael,
you should try harder, maybe ;)
jakob
Michael Hart wrote:
>>fixed the bogus sql bug.
>>please use the latest SqlQueryStatement from repository.
>>
>>
>
>Beautiful! It seems to work perfectly - I haven't been able to break it yet
>;-)
>
>
>
>>you should not use the name of a relationship in a user alias, in the
>>best case it will have no effect ;)
>>
>>
>
>Ah really? Well it was working :-) (and still does I think). Perhaps this
>should also be something to go in the docs.
>
>Anyway, thank-you for your patience and help.
>
>Cheers,
>
>Michael
>
>P.S. Thanks also to Armin for fixing up OJB.properties
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
>
RE: Alias troubles
Posted by Michael Hart <mi...@perceptek.com.au>.
> fixed the bogus sql bug.
> please use the latest SqlQueryStatement from repository.
Beautiful! It seems to work perfectly - I haven't been able to break it yet
;-)
> you should not use the name of a relationship in a user alias, in the
> best case it will have no effect ;)
Ah really? Well it was working :-) (and still does I think). Perhaps this
should also be something to go in the docs.
Anyway, thank-you for your patience and help.
Cheers,
Michael
P.S. Thanks also to Armin for fixing up OJB.properties
Re: Alias troubles
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi michael,
fixed the bogus sql bug.
please use the latest SqlQueryStatement from repository.
jakob
Michael Hart wrote:
>Hi Jakob,
>
>
>
>>i did some tests with empty criteria and imo this works ok:
>>
>>
>
>Oops... you're right - the example I gave was too minimal :-) Here's a
>better one:
>
>
>Criteria main_crit = new Criteria();
>main_crit.setAlias("project");
>
>Criteria persons_crit = new Criteria();
>persons_crit.setAlias("persons");
>persons_crit.addEqualTo("persons.id","2");
>
>main_crit.addAndCriteria(persons_crit);
>
>Query query = new QueryByCriteria(Project.class, main_crit);
>
>
>Generates this:
>
>SELECT A0.TITLE,A0.DESCRIPTION,A0.ID
>FROM PROJECT A0
>INNER JOIN PERSON_PROJECT A1 ON A0.ID=A1.PROJECT_ID
>INNER JOIN PERSON A2 ON A1.PERSON_ID=A2.ID
>WHERE A4.ID = 2
>
>In the previous code snippet, if you don't set the alias for main_crit, then
>it works fine. However, I got some weird results with other combinations and
>it took me quite a while to realise that the name you give in the alias
>makes quite a difference (as you may have hinted at in one of your previous
>e-mails). For example:
>
>
>Criteria main_crit = new Criteria();
>
>Criteria roles_crit = new Criteria();
>roles_crit.setAlias("roles");
>roles_crit.addEqualTo("roles.roleName","whatever");
>main_crit.addAndCriteria(roles_crit);
>Query query = new QueryByCriteria(Person.class, main_crit);
>
>
>Works fine, but changing the setAlias line to:
>
>roles_crit.setAlias("roles1");
>
>generates this bogus SQL:
>
>SELECT A0.LASTNAME,A0.FIRSTNAME,A0.ID
>FROM PERSON A0
>INNER JOIN PERSON_PROJECT A1 ON A0.ID=A1.PERSON_ID
>WHERE A2.ROLENAME = 'whatever'
>
>But then adding another one (with a different alias) works (!):
>
>
>Criteria main_crit = new Criteria();
>
>Criteria roles_crit = new Criteria();
>roles_crit.setAlias("roles");
>roles_crit.addEqualTo("roles.roleName","whatever");
>main_crit.addAndCriteria(roles_crit);
>
>Criteria roles2_crit = new Criteria();
>roles2_crit.setAlias("roles2");
>roles2_crit.addEqualTo("roles.roleName","hello");
>main_crit.addAndCriteria(roles2_crit);
>Query query = new QueryByCriteria(Person.class, main_crit);
>
>
>Note that if roles_crit is removed, or its setAlias changed, then it breaks
>again. In this example, as long as you have the first Criteria with an alias
>"roles", then you can add other Criteria with whatever alias you want.
>
>This is also true for other aliases to other classes:
>
>
>Criteria main_crit = new Criteria();
>
>Criteria roles_crit = new Criteria();
>roles_crit.setAlias("roles");
>roles_crit.addEqualTo("roles.roleName","whatever");
>main_crit.addAndCriteria(roles_crit);
>
>Criteria proj_crit = new Criteria();
>proj_crit.setAlias("projects1");
>proj_crit.addEqualTo("projects.id","2");
>main_crit.addAndCriteria(proj_crit);
>Query query = new QueryByCriteria(Person.class, main_crit);
>
>
>Works fine, and likewise so does:
>
>
>Criteria main_crit = new Criteria();
>
>Criteria proj_crit = new Criteria();
>proj_crit.setAlias("projects");
>proj_crit.addEqualTo("projects.id","2");
>main_crit.addAndCriteria(proj_crit);
>
>Criteria roles2_crit = new Criteria();
>roles2_crit.setAlias("roles2");
>roles2_crit.addEqualTo("roles.roleName","hello");
>main_crit.addAndCriteria(roles2_crit);
>Query query = new QueryByCriteria(Person.class, main_crit);
>
>
>In any of the previous examples, if main_crit.setAlias is invoked, it breaks
>again, unless of course you add a criteria to it, eg main_crit.addLike(...).
>
>Very confused :-)
>
>Michael
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
>
Re: Alias troubles (was: M to N alias/join troubles)
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi michael,
you should not use the name of a relationship in a user alias, in the
best case it will have no effect ;)
when you do not define your own alias ojb uses the name of the
relationship as 'internal' alias.
i'll have a look at your problem using an empty criteria.
hth
jakob
Michael Hart wrote:
>In response to myself:
>
>
>
>>Criteria main_crit = new Criteria();
>>main_crit.setAlias("project");
>>
>>Criteria persons_crit = new Criteria();
>>persons_crit.setAlias("persons");
>>persons_crit.addEqualTo("persons.id","2");
>>
>>main_crit.addAndCriteria(persons_crit);
>>
>>Query query = new QueryByCriteria(Project.class, main_crit);
>>
>>Generates this:
>>
>>SELECT A0.TITLE,A0.DESCRIPTION,A0.ID
>>FROM PROJECT A0
>>INNER JOIN PERSON_PROJECT A1 ON A0.ID=A1.PROJECT_ID
>>INNER JOIN PERSON A2 ON A1.PERSON_ID=A2.ID
>>WHERE A4.ID = 2
>>
>>
>
>I've just played some more, and putting the main_crit.setAlias at the end of
>the code block works fine (this is true for the other examples as well):
>
>Criteria main_crit = new Criteria();
>
>Criteria persons_crit = new Criteria();
>persons_crit.setAlias("persons");
>persons_crit.addEqualTo("persons.id","2");
>
>main_crit.addAndCriteria(persons_crit);
>
>main_crit.setAlias("project");
>
>
>All works...
>
>So this, plus the other examples, leads me to the following rule-of-thumb:
>
>*********
>If you are going to set aliases for a particular set of Criteria, you must
>ensure that the name of the first alias you set reflects the name of a
>property of the class you wish to query. After that, you may set alias names
>to be whatever you wish.
>*********
>
>Does that sound right to you, or have I just generated some really weird
>test cases?
>
>In any case, I still think it's slightly, slightly buggy... ? (But much
>better)
>
>Cheers,
>
>Michael
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
>
RE: Alias troubles (was: M to N alias/join troubles)
Posted by Michael Hart <mi...@perceptek.com.au>.
In response to myself:
> Criteria main_crit = new Criteria();
> main_crit.setAlias("project");
>
> Criteria persons_crit = new Criteria();
> persons_crit.setAlias("persons");
> persons_crit.addEqualTo("persons.id","2");
>
> main_crit.addAndCriteria(persons_crit);
>
> Query query = new QueryByCriteria(Project.class, main_crit);
>
> Generates this:
>
> SELECT A0.TITLE,A0.DESCRIPTION,A0.ID
> FROM PROJECT A0
> INNER JOIN PERSON_PROJECT A1 ON A0.ID=A1.PROJECT_ID
> INNER JOIN PERSON A2 ON A1.PERSON_ID=A2.ID
> WHERE A4.ID = 2
I've just played some more, and putting the main_crit.setAlias at the end of
the code block works fine (this is true for the other examples as well):
Criteria main_crit = new Criteria();
Criteria persons_crit = new Criteria();
persons_crit.setAlias("persons");
persons_crit.addEqualTo("persons.id","2");
main_crit.addAndCriteria(persons_crit);
main_crit.setAlias("project");
All works...
So this, plus the other examples, leads me to the following rule-of-thumb:
*********
If you are going to set aliases for a particular set of Criteria, you must
ensure that the name of the first alias you set reflects the name of a
property of the class you wish to query. After that, you may set alias names
to be whatever you wish.
*********
Does that sound right to you, or have I just generated some really weird
test cases?
In any case, I still think it's slightly, slightly buggy... ? (But much
better)
Cheers,
Michael