incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From McKinley <mckinley1...@gmail.com>
Subject Re: Rowlimits: OFFSET and LIMIT
Date Mon, 25 Jan 2010 22:14:24 GMT
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.

Mime
View raw message