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.
