server side pagination

[fazr]

hi, currently the pagination is requesting from API for every row in DB and then paginate it. for example, i have 1000 rows in my DB. every time i click on next page it request through API and the API returns the same 1000 rows.

I might have do something wrong here. is there any way to send current page and limit through the API to reduce the loading time of the table ?

[mazdik]

hi, In your service, the RequestMetadata is sent to the server?

export class YourService implements DataSource {
}

interface DataSource {
  getItems(requestMeta: RequestMetadata): Promise<PagedResult>;
  getItem(row: any): Promise<any>;
  post(row: any): Promise<any>;
  put(row: any): Promise<any>;
  delete(row: any): Promise<any>;
  getOptions?(url: string, parentId: any): Promise<any>;
}
export interface RequestMetadata {
  pageMeta: PageMetadata;
  sortMeta: SortMetadata[];
  filters: FilterMetadata;
  globalFilterValue?: string;
}
export interface PagedResult {
  items: any[];
  _meta: PageMetadata;
}
export interface PageMetadata {
  currentPage: number;
  perPage: number;
  totalCount?: number;
  pageCount?: number;
  maxRowCount?: number;
}

[mazdik]

sample in C#

        private static string PaginationSorting(string query, int limit, int page, SortMetadata[] sortMeta)
        {
            var rowOffset = (page - 1) * limit;
            var rowCount = page * limit;
            rowOffset = rowOffset + 1;

            var orderClause = CreateOrderClause(sortMeta);

            if (dialect == Dialect.Oracle)
            {
                orderClause = "where rownum <=" + maxRowCount + " " + orderClause;
            }
            else
            {
                orderClause = orderClause + " limit " + maxRowCount;
            }
            var sql = @"select V4.* from (select V3.*, row_number() over(order by 1) as RN 
             from (select V2.*, count(*) over() as ROW_CNT 
             from (select V1.* from ({0}) V1) V2 {1}) V3) V4 
             where V4.RN between {2} and {3}";
            query = string.Format(sql, query, orderClause, rowOffset, rowCount);
            return query;
        }

sql in oracle

select V4.*
  from (select V3.*, row_number() over(order by 1) as RN
          from (select V2.*, count(*) over() as ROW_CNT
                  from (select V1.* from (select * from all_users) V1) V2
                 where rownum <= 500000
                 order by 1) V3) V4
 where V4.RN between 1 and 20

sql in postgresl

select V4.*
  from (select V3.*, row_number() over(order by 1) as RN
          from (select V2.*, count(*) over() as ROW_CNT from (select V1.* from (select * from information_schema.tables) V1) V2 order by 1 limit 500000) V3) V4
 where V4.RN between 1 and 20