public override ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize)
{
SelectStatement select = CheckAndAdjustExpression(sqlExp, orderby);
// Has paging ?
if (topPage != 1)
{
// paging
//SELECT TOP 100 PERCENT y.* FROM
//(
// SELECT TOP {TopMax} x.*
// FROM
// (
// SELECT TOP {TopFirstPlusMax} e1.Type, e1.Id
// {SortSelect}
// FROM ( {OPathQuery} ) e1
// {SortFrom}
// {SortOrderBy}
// )x
// {SortOrderBy_REV}
//)y
//{SortOrderBy}
SelectStatement selectY = new SelectStatement(null);
SelectStatement selectX = new SelectStatement(null);
// reverse sort order
OrderByClause sortOrderByRev = new OrderByClause(select);
foreach (OrderByClauseColumn col in select.OrderByClause)
sortOrderByRev.Add(new OrderByClauseColumn(col.ColumnName, !col.Desc));
select.Limit = topPage + pageSize - 1;
select.TableAlias = "x";
selectX.Limit = pageSize;
selectX.SelectedAllColumns = true;
selectX.FromClause.Add(select as Table);
selectX.OrderByClause = sortOrderByRev;
selectX.TableAlias = "y";
selectY.SelectedAllColumns = true;
selectY.FromClause.Add(selectX as Table);
selectY.OrderByClause = select.OrderByClause;
select = selectY;
select.Limit = -1;
}
else
{
// only sort, limit possible
//SELECT [TOP pageSize | TOP 100 PERCENT] XXX
//FROM XXX WHERE XXX
//ORDER BY [orderByClause]
select.Limit = pageSize;
}
return select;
}
public override ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize)
{
SelectStatement select = CheckAndAdjustExpression(sqlExp, orderby);
// Has paging ?
if (topPage != 1)
{
// paging
//SELECT TOP 100 PERCENT y.* FROM
//(
// SELECT TOP {TopMax} x.*
// FROM
// (
// SELECT TOP {TopFirstPlusMax} e1.Type, e1.Id, {SortSelect}
// FROM
// (
// SELECT Type, Id, {SortSelect}
// FROM {OPathQuery without order by}
// ) e1
// {SortOrderBy}
// )x
// {SortOrderBy_REV}
//)y
//{SortOrderBy}
SelectStatement selectY = new SelectStatement(null);
SelectStatement selectX = new SelectStatement(null);
// put in a subquery because access cannot order with alias in select
SelectStatement selectZ = new SelectStatement(null);
foreach (ISQLExpression exp in select.SelectList)
{
if (exp is Column)
selectZ.SelectList.Add(new SelectItem(null, ((Column)exp).Alias == String.Empty ? ((Column)exp).ColumnName : ((Column)exp).Alias, ""));
}
selectZ.SelectedAllColumns = select.SelectedAllColumns;
selectZ.FromClause.Add(select as Table);
selectZ.OrderByClause = select.OrderByClause;
select.OrderByClause = new OrderByClause(select);
select = selectZ;
// reverse sort order
OrderByClause sortOrderByRev = new OrderByClause(select);
foreach (OrderByClauseColumn col in select.OrderByClause)
sortOrderByRev.Add(new OrderByClauseColumn(col.ColumnName, !col.Desc));
select.Limit = topPage + pageSize - 1;
select.TableAlias = "x";
selectX.Limit = pageSize;
selectX.SelectedAllColumns = true;
selectX.FromClause.Add(select as Table);
selectX.OrderByClause = sortOrderByRev;
selectX.TableAlias = "y";
selectY.SelectedAllColumns = true;
selectY.FromClause.Add(selectX as Table);
selectY.OrderByClause = select.OrderByClause;
select = selectY;
}
else
{
// only sort, limit possible
//SELECT [TOP pageSize | TOP 100 PERCENT] XXX
//FROM (select x from y where z) as x WHERE XXX
//ORDER BY [orderByClause]
// put in a subquery because access cannot order with alias in select
SelectStatement selectX = new SelectStatement(null);
foreach (ISQLExpression exp in select.SelectList)
{
if (exp is Column)
selectX.SelectList.Add(new SelectItem(null, ((Column)exp).Alias == String.Empty ? ((Column)exp).ColumnName : ((Column)exp).Alias, ""));
}
if (select.SelectList.Count == 0)
selectX.SelectedAllColumns = true;
selectX.Limit = pageSize;
selectX.FromClause.Add(select as Table);
selectX.OrderByClause = select.OrderByClause;
select.OrderByClause = new OrderByClause(select);
select = selectX;
}
return select;
}