I'll submit a patch for it. DBOrderByInfo works really well with it so far. Can you make it public and not protected? I am having you use my own copy of DBOrderByInfo for now.
Thanks, McKinley On Wed, Jan 27, 2010 at 12:11 AM, Rainer Döbele <[email protected]> wrote: > Hi McKinley, > > I have not analyzed your problem in detail but probably in this case > (multiple order by columns) DBFuncExpr is not suitable. > You really should consider creating a class derived from DBColumnExpr > specifically for ROW_NUMBER() OVER... > You may take the implementation of DBFuncExpr as a guideline. > > We may then be able to add this expression to the > org.apache.empire.db.expr.column package. > Good luck. > > Rainer > > > McKinley wrote: >> Re: Rowlimits: OFFSET and LIMIT >> >> Wow, I had to really hack to make DBFuncExpr work with multiple >> order-by columns. Take note of the SQL comment in my function >> template. This is because I could not cleanly use the question mark >> with a DBOrderByInfo (yes, I know, I know). Anyway, the Object[] >> params work great on the {#}, but the ? want the first argument to be >> a DBColumnExpr and not a DBExpr which DBOrderByInfo. I tried to have >> the first column hit the ? and direct the " DESC" or "" string to the >> first {0}. That got type checked to DATETIME (the type of the column) >> in this case. If I just ignore using ? every works great. >> >> Any thoughts on how this usage might be facilitated by changes to the >> function and abstract function arrangement? >> >> public void page(int limit, int offset, DBOrderByInfo[] orderBy) >> { >> this.limit = limit; >> this.offset = offset; >> this.orderBy = orderBy; >> if(rowNum != null) subCmd.removeSelect(rowNum); >> >> StringBuilder sql = new StringBuilder("ROW_NUMBER() OVER >> (ORDER BY {0}"); >> if(rowNumParams == null || rowNumParams.length != >> orderBy.length) >> rowNumParams = new Object[orderBy.length]; >> rowNumParams[0] = orderBy[0]; >> for(int i = 1; i < orderBy.length; i++){ >> rowNumParams[i] = orderBy[i]; >> sql.append(", {").append(i).append('}'); >> } >> sql.append(')').append("--remove ? \n"); // HACK!!! >> >> rowNum = new DBFuncExpr(orderBy[0].expr, sql.toString(), >> rowNumParams, null, false, DataType.INTEGER) >> .as("RowNum"); >> subCmd.select(rowNum); >> subQuery = new DBQuery(subCmd); >> this.select(subQuery.getQueryColumns()); >> this.select.remove(subQuery.findQueryColumn(etsr.UserID)); >> this.where(subQuery.findQueryColumn(rowNum).isBetween(offset, >> offset + limit)); >> } >> >> Thanks, >> >> McKinley >> >> >>>> Thanks for reporting back. Did you use DESC in your ORDER BY? If >> you >> >>>> don't have wiki access you might consider posting the wiki markup >> in >> >>>> the issue tracker. I registered for the wiki, but didn't now get >> >>>> access by default. >> >>>> >> >>>> I'll be testing ROW_NUMBER on SQL Server tomorrow and will let you >> >>>> know what I find. >
