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)
{
