Re: Alias troubles

[Jakob Braeuchi]

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



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