Offset/limit to page/size conversion
As pointed out by @usr, in most variants of this problem (whether querying a database, API, or some other entity) it is preferable to reduce the number of calls as much as possible, since returning a few extra rows is almost always cheaper than issuing a separate call. The following PageSizeConversion algorithm will always find a single call that returns the fewest records possible (which is exactly how it performs the search). There may be some extra records returned at the beginning (headWaste) or end (tailWaste) of the data set, required to fit the data set within a single page. The algorithm is implemented in Javascript here, but it is easy to port to any language.
function PageSizeConversion(offset, limit) { var window, leftShift; for (window = limit; window <= offset + limit; window++) { for (leftShift = 0; leftShift <= window - limit; leftShift++) { if ((offset - leftShift) % window == 0) { this.pageSize = window; this.page = (offset - leftShift) / this.pageSize; this.headWaste = leftShift; this.tailWaste = ((this.page + 1) * this.pageSize) - (offset + limit); return; } } }}var testData = [ {"offset": 0,"limit": 10,"expectedPage": 0,"expectedSize": 10,"expectedHeadWaste": 0,"expectedTailWaste": 0}, {"offset": 2,"limit": 1,"expectedPage": 2,"expectedSize": 1,"expectedHeadWaste": 0,"expectedTailWaste": 0}, {"offset": 2,"limit": 2,"expectedPage": 1,"expectedSize": 2,"expectedHeadWaste": 0,"expectedTailWaste": 0}, {"offset": 5,"limit": 3,"expectedPage": 1,"expectedSize": 4,"expectedHeadWaste": 1,"expectedTailWaste": 0}, {"offset": 3,"limit": 5,"expectedPage": 0,"expectedSize": 8,"expectedHeadWaste": 3,"expectedTailWaste": 0}, {"offset": 7,"limit": 3,"expectedPage": 1,"expectedSize": 5,"expectedHeadWaste": 2,"expectedTailWaste": 0}, {"offset": 1030,"limit": 135,"expectedPage": 7,"expectedSize": 146,"expectedHeadWaste": 8,"expectedTailWaste": 3},];describe("PageSizeConversion Tests", function() { testData.forEach(function(testItem) { it("should return correct conversion for offset " + testItem.offset + " limit " + testItem.limit, function() { conversion = new PageSizeConversion(testItem.offset, testItem.limit); expect(conversion.page).toEqual(testItem.expectedPage); expect(conversion.pageSize).toEqual(testItem.expectedSize); expect(conversion.headWaste).toEqual(testItem.expectedHeadWaste); expect(conversion.tailWaste).toEqual(testItem.expectedTailWaste); }); });});// load jasmine htmlReporter(function() { var env = jasmine.getEnv(); env.addReporter(new jasmine.HtmlReporter()); env.execute();}());<script src="https://cdn.jsdelivr.net/jasmine/1.3.1/jasmine.js"></script><script src="https://cdn.jsdelivr.net/jasmine/1.3.1/jasmine-html.js"></script><link href="https://cdn.jsdelivr.net/jasmine/1.3.1/jasmine.css" rel="stylesheet" /><title>Jasmine Spec Runner</title>This is perhaps not exactly what @Martijn is looking for, since it occasionally produces a result with a lot of waste. But in most cases it seems like a good solution to the general problem.
Principles of Offset/Limit To Page/Page-Size Pagination (Precise, No Waste)
If page-size == limit or (offset % limit) == 0, page == (whole number)else page == (float).
To run type 'node convertOffsetToPage.js ' at the command line
function convertOffsetToPage(offset, limit) { const precision = 1000000; const pageSize = limit; let page = (offset + limit) / limit; page = Math.round(page * precision) / precision; return { page, pageSize };}function dbServiceSimulation(page, pageSize, items) { const start = Math.round((page - 1) * pageSize); const end = Math.round(page * pageSize); return items.slice(start, end);}function getDataItems(itemCount) { return Array.from(Array(itemCount), (_, x) => x);}const dataItems = getDataItems(1000000);console.log('\ndata items: ', dataItems);let offset = parseInt(process.argv[2], 10) || 0;let limit = parseInt(process.argv[3], 10) || 1;console.log('\ninput offset: ', offset);console.log('\ninput limit: ', limit);const { page, pageSize } = convertOffsetToPage(offset, limit);console.log('\npage = ', page);console.log('\npageSize = ', pageSize);const result = dbServiceSimulation(page, pageSize, dataItems);console.log('\nresult after core Service call');console.log('\nresult: ', result)console.log('\n');
I also ran into this problem. My solution is (in java) in short below. First the trivial cases are closed out and then comes the tricky part :). It tries to find the optimal page size, page number and calculates the new offset from the beginning of the page. The original limit preserved:
public static PageSizeOffsetLimit toPageSize(int offset, int limit) { if (offset < limit) { return new PageSizeOffsetLimit(0, offset + limit, offset, limit); } if (offset == limit) { return new PageSizeOffsetLimit(1, limit, 0, limit); } for (int size = limit; size < 2 * limit; size++) { int newOffset = offset % size; if ((size - limit) >= newOffset) { return new PageSizeOffsetLimit(offset / size, size, newOffset, limit); } } throw new RuntimeException(String.format( "Cannot determinate page and size from offset and limit (offset: %s, limit: %s)", offset, limit));}Have fun :)