Hi McKinley, I had to have a look at it first.
Before I make DBOrderByInfo public, I am going to rename it. It's proper name should be DBOrderByExpr. And there should be overrides on DBColumnExpr for Asc and Desc. Might do that tonight. I'll let you know when I am done. Regards Rainer McKinley wrote: > Re: Rowlimits: OFFSET and LIMIT > > 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. > >
