Implement keyset pagination about data

[samdark]

Note that widgets are in a separate package: https://github.com/yiisoft/yii-dataview

[kamarton]

This is simple way of scanning from database. There are problematic cases that require special planning:

not has unique key or unique key is not sort key

Records:

date	color
2019-01-01	red
2019-01-02	green
2019-01-02	orange
2019-01-02	black
2019-01-03	white

Scan pages:

SELECT * FROM ... WHERE 1 ORDER BY date LIMIT 2
// result: red, green
SELECT * FROM ... WHERE date > '2019-01-02' ORDER BY date LIMIT 2
// result: white
// missed records: orange, black

For proper operation, you need an optional key that is unique with the sort key.

date	number	color
2019-01-01	1	red
2019-01-02	1	green
2019-01-02	2	orange
2019-01-02	3	black
2019-01-03	1	white

Scan pages:

SELECT * FROM ... WHERE 1 ORDER BY date, number LIMIT 2
// result: red, green
SELECT * FROM ... WHERE date > '2019-01-02' or (date = '2019-01-02' and number > 1) ORDER BY date, number LIMIT 2
// result: orange, black
SELECT * FROM ... WHERE date > '2019-01-02' or (date = '2019-01-02' and number > 3) ORDER BY date, number LIMIT 2
// result: white
// missed records: -

Scanning is slow (too many records, complicated filtering conditions)

Records

id	large_text	created_at
1	apple	2019-01-01 00:00:00
2	apple	2019-01-01 00:00:01
3	peach	2019-01-01 00:00:02
... 1000000000	not apple	2019-01-31 23:59:59
1000000001	apple	2019-02-01 00:00:00
...

Scan pages:

SELECT * FROM ... WHERE larege_text like `%apple%` order by created_at LIMIT 3
// after 10 mins
// result: 1, 2, 1000000001

With date restriction:

SELECT * FROM ... WHERE larege_text like '%apple%' and date <= '2019-01-01' order by created_at LIMIT 3
// result: 1, 2
SELECT * FROM ... WHERE larege_text like '%apple%' and date <= '2019-01-01' and id > 2 order by created_at LIMIT 3
// result: -
SELECT * FROM ... WHERE larege_text like '%apple%' and date >= '2019-01-02' and date < '2019-01-03' order by created_at LIMIT 3
// result: -
...
SELECT * FROM ... WHERE larege_text like '%apple%' and date >= '2019-02-01' and date < '2019-02-02' order by created_at LIMIT 3
// result: 1000000001

In this case, the web request may return to the user and display currently scanning: xxxx-xx-xx date. Because the user knows he is only interested in the previous week, he can interrupt the process because he no longer makes sense.

[samdark]

Here's draft implementation btw. (not finished): https://github.com/yiisoft/data/pull/3/files#diff-05676246c23f4c54406cfb8f18b388cbR1

[samdark]

Implemented.