Hi again, The problem is real but the patch I described (see original message) is flawed. So forget that patch.
I think the solution direction is probably something like: - pas the useralias to the ensurecolumns methods (they should pass it on, but now they do not have it and they pass null, so we lost the notion of the useralias processing the orderby columns) - following the flow of execution, I believe the inner class SingleAttributeInfo in class SqlQueryStatement should have a property to store the useralias (so the constructor should receive this as an argument) - again following the flow of execution, I believe somewhere in methods like the method protected void appendColumn(SingleAttributeInfo anAttrInfo, boolean translate, StringBuffer buf) some logic shopuld be added using the useralias and the tablealias to generate the proper SQL But like I said in earlier mail... the code is not that transparent and I have not yet figured out how to solve this issue. Hope you guys can. Greetings, Roger Janssen iBanx -----Original Message----- From: Janssen, Roger [mailto:[EMAIL PROTECTED] Sent: Thursday, January 31, 2008 9:39 AM To: OJB Users List Subject: BUGREPORT : OJB 1.0.5rc1 : UserAliases in OrderBy clauses are not replaced by the generated aliases for the joins resulting in illegal SQL statements 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); 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]
