Hi Roger,
Janssen, Roger wrote:
Hi,
When you have two object classes, Permit and AbstractAttribute and
Permit holds a collection of AbstractAttributes (using a collection
descriptor for the abstractattribute collection on the Permit in the
mapping file), we can create a query like below:
Criteria crit1 = new
Criteria().addEqualTo("allAbstractAttributes.name", "aaTest");
UserAlias ua = new UserAlias("myAlias");
crit1.setAlias(ua);
Criteria crit2 = new
Criteria().addLike("allAbstractAttributes.value", "*");
crit2.setAlias(ua);
crit1.addAndCriteria(crit2);
QueryByCriteria query = QueryFactory.newQuery(Permit.class,
crit1, true);
query.addOrderBy("myAlias.value", true);
Collection c = pb.getCollectionByQuery(query);
You are right, this doesn't work with 1.0.5rc1 (with 1.0.4 it doesn't
work too). Is this the correct code? AFAIK this query work without an
alias (my local test show the same generated sql with and without alias):
Criteria crit1 = new Criteria().addEqualTo("allAbstractAttributes.name",
"aaTest");
Criteria crit2 = new Criteria().addLike("allAbstractAttributes.value",
"*").addAndCriteria(crit2);
QueryByCriteria query = QueryFactory.newQuery(Permit.class,crit1, true);
query.addOrderBy("allAbstractAttributes.value", true);
I setup some new query tests with order by clause but I can't think out
a useful test using an alias in the order by clause. Fixing this "bug"
won't be easy (you already notice that ;-)), so I want be sure that it
is important to support alias-names in oder by and having clause.
regards,
Armin
In this query I use a useralias to reference a specific join in my
orderby clause. However the generated SQL looks something like:
SELECT .... FROM PERMIT A0 INNER JOIN ABSTRACTATTRIBUTE A1
WHERE A0.ID == A1.OID AND A1.NAME = ? AND A1.VALUE LIKE '%'
ORDER BY myAlias.value
So we observe that the defined useralias on the join is not processed
within the orderby clause, and I get a wrong SQL statement throwing an
SQL exception. I would have expected a query like:
SELECT .... FROM PERMIT A0 INNER JOIN ABSTRACTATTRIBUTE A1
WHERE A0.ID == A1.OID AND A1.NAME = ? AND A1.VALUE LIKE '%'
ORDER BY A1.VALUE
Again, I did some OJB code hacking. I came up with a patch, but.... I
have the feeling it might be done better/smarter/more efficient and
maybe even implemented in some other location in the code.... But the
SQL generation code was not quite that transparent regarding this
aspect, so just analyse my patch and see what you do with it.
In the class SqlSelectStatement, in the method protected String
buildSqlString(), there is the following code:
...
...
groupByFields = query.getGroupBy();
ensureColumnsGroupBy(groupByFields, columnList);
orderByFields = query.getOrderBy();
columnList = ensureColumnsOrderBy(orderByFields, columnList, stmt);
...
...
In here the groupByFields and the orderByFields contain the lists of
attributes like the user specified them, so with the useraliases. These
need to be replaced with the generated alias before the
ensurecolumn-methods are called. So I implemented a method
replaceUserAlias doing exactly that and I call this method before the
call to the ensurecolumns-methods.
The code then becomes:
...
...
groupByFields = query.getGroupBy();
// start - iBanx patch
replaceUserAlias(groupByFields, whereCrit);
// end - iBanx patch
ensureColumnsGroupBy(groupByFields, columnList);
orderByFields = query.getOrderBy();
// start - iBanx patch
replaceUserAlias(orderByFields, whereCrit);
// end - iBanx patch
columnList = ensureColumnsOrderBy(orderByFields, columnList, stmt);
...
...
The method implementation is:
// start - iBanx patch
/**
* Replaces any useralias reference in the fieldlist with the
corresponding generated alias of the join releated to the
* whereCrit clause.
*
* @param fields
* @param whereCrit
*/
private void replaceUserAlias(List fields, Criteria whereCrit)
{
// defensive programming preventing NPE's
if((getRoot()!=null) && (whereCrit!=null) &&
(whereCrit.getUserAlias()!=null) && (fields!=null))
{
// continue when we have a join
// -- test it like this because the iterateJoins() method
throes NPE when joins equals null
if(getRoot().joins != null)
{
// iterate over all the joins to check for useraliases
Iterator theJoins = getRoot().iterateJoins();
if(theJoins!=null)
{
while(theJoins.hasNext())
{
Join j = (Join)theJoins.next();
if(j.right!=null)
{
// iterate over all the fields in the list
that might contain useraliases
for(int i = 0;i < fields.size();i++)
{
FieldHelper fh =
(FieldHelper)fields.get(i);
// if the field has a useralias....
replecae it with the generated alias
if(fh.name.startsWith(whereCrit.getUserAlias().getName() + "."))
{
// generate new fieldname with the
generated alias
String fhname = j.right.alias +
fh.name.substring(whereCrit.getUserAlias().getName().length());
// remove the 'old' field from the
list
fields.remove(i);
// instantiate a new fieldhelper
with the new proper name
FieldHelper nfh = new
FieldHelper(fhname, fh.isAscending);
// insert it into the list at the
same location
fields.add(i, nfh);
}
}
}
}
}
}
}
}
// end - iBanx patch
I have tested this patch for the given orderby-scenario, not with a
useralias in the groupby clause. This patch seems to work for me in this
case, but like I said... Some of you guys probably have a better fix for
it.
Greetings,
Roger Janssen
iBanx
*************************************************************************
The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed.You should not copy, disclose or distribute this communication without the authority of iBanx bv. iBanx bv is neither liable for the proper and complete transmission of the information has been maintained nor that the communication is free of viruses, interceptions or interference.
If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies.
---------------------------------------------------------------------
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]
