Done. DBOrderByInfo is now DBOrderByExpr and public.
Rainer P.S. You may now write cmd.orderBy(C1.asc(), C2.desc(), C3.asc(), ...) Rainer Döbele wrote: > making DBOrderByInfo public > > 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. > > >
