I have an PHP (5.5) webapp with sqlite 3. It has an logs page (possibly 10k to 100k logs in the future). I am using this way of paging for those logs. The query for sqlite I am using:
SELECT *
FROM Logs
WHERE date > [input epoch long]
ORDER BY date DESC
LIMIT 100;
The logs model is like:
"id": [int increased by sqlite]
"date":[entry date, epoch long like: "1533595828"]
+ other not relevant data for this issue
I ideally want to have an page Like: Previous | 1 | 2 | 3 | 4 | Next, this seams pretty hard. So an Previous and Next page handling is acceptable.
For the Next page handling, I am using: date > [the last record it returned at init]. This works. Bud to go back I need to keep track of the first date. After I go back multiple times, this gets messy.
What would be the best way to solve this problem?
Nobody will want to page through thousands of pages; your webapp could be improved with better searching and filtering.
Anyway, if you want to know how many pages there are, you have to count all rows:
SELECT count(*) FROM Logs;
And if you want to know on which page you are, you have to count how many rows there are before the current one:
SELECT count(*) FROM Logs WHERE date < [first on current page];
The "next" and "previous" functions work in exactly the opposite way of each other: you have to keep track of the last/first row of the current page, and you have to fetch the next 100 rows with a larger/smaller value:
SELECT * SELECT *
FROM Logs FROM Logs
WHERE date > [last on current page] WHERE date < [first on current page]
ORDER BY date DESC ORDER BY date ASC
LIMIT 100; LIMIT 100;
(The second query has the interesting wrinkle of returning the rows in backward order, but that should not be a problem.)
And if you want to jump to a specific page, the easiest way to find out the first date value on that page is with OFFSET:
SELECT date
FROM Logs
ORDER BY date ASC
OFFSET [page*100]
LIMIT 1;
(This query will need to go through these rows, but if the column is indexed, at least it does not have to read any table data.)