Pagination without offset

Yii 3.0 Design discussions
1

So i was browsing reddit and have found this https://www.reddit.com/r/PHP/comments/9s4zcu/any_orm_you_are_aware_of_which_does_pagination/
Can we have an alternative pagination in yii3 where offset isn’t used? I.e: Paginate just like reddit does.

2 Likes
2

Yes. I think it is a good to have feature.

3

it is easy enough to implement in the app, I rarely used the builtin paginator widget we use a similar cursor based technique but with bi-directional support

4

Can you share?

5

You do realize that this technique only works with Postgresql? With such a limitation, I doubt it qualifies for integration into the core of Yii.

The trick is to replace the offset with a comparison the the previous last result:

WHERE ... AND (name, category, last_update, id) < (?, ?, ?, ?)
ORDER BY name DESC, category DESC, last_update DESC, id DESC
LIMIT 100

This syntax is currently not available in MySQL/MariaDB.

It’s also far from easy to automate. How would you write this comparison for an ORDER BY name ASC, category DESC, last_update DESC, id ASC? In a portable library, I suspect it would be a nightmare.

6

I don’t think you are right.
The way i’m thinking this works is really simple, on first page, do smth like:

SELECT abc FROM abc WHERE abc > abc LIMIT 100;

Then you get ahold of last record from the previous result set, say it has the id 100, and you do:

SELECT abc FROM abc WHERE abc > abc AND id > 100 LIMIT 100;

this way skipping the first 100 results from the first page, and so on. Ordering works just fine, you can also peak ahead to see if you do have a next page.

7
  • Consider a simple ORDER BY name ASC, last_update DESC, id DESC on data with many homonyms. It is a total sort, which is requested for any serious pagination (i.e. there is no ambiguous sorting of elements as in a simple name sort).
  • The first page ends at a record ('abc', '2018-01-01 00:00:00', 10).
  • Suppose the next page should start with the record ('abc', '2017-01-01 00:00:00', 999) which has the same name but a smaller last_update value.

Now if you write WHERE name > 'abc' AND ... you will miss some results. And you can’t even write WHERE name >= 'abc' AND last_update <= '2018-01-01 00:00:00' AND id < 10. The right SQL would be WHERE name > 'abc' OR (name = 'abc' AND (last update < '2018-01-01 00:00:00' OR (last_update = '2018-01-01 00:00:00' AND id < 10))). The SQL-92 syntax that compares tuples is a simpler and much more performant way to write this.

8

My simple idea about how this would work, from above, was that regardless of your ordering clause, it would work like when you’d not apply a limit, and you’d just moving the cursor through those records which are already ordered properly.
I haven’t tested your example though, so i can’t really say if that applies or not.

9

If I understand your proposition, pagination would still use OFFSET except for cases where the data is sorted by a single unique key, in which cases OFFSET would be replaced by a comparison, in order to be more performant with large sets of results.
I’d rather not have that. Yii’s module should avoid extra complexity. Yii applications can still write their own pagination class if this corner case is important to them.

10

Here’s an article that explains it well: https://use-the-index-luke.com/no-offset

I fully agree that this is a good way to paginate and we need to support it. I’ll check what do we need for it.

2 Likes
11
2 Likes
12

I’m no fan of offset and I would use this if were available. But I do not want to write these queries mysqlf!

But I would be sad if this were only a Yii 3 feature.

13

Well, we’ve announced long time ago that we’re not taking features into Yii 2.0.

14

@Francois.Gannaz it works with all RDMS not only with relational database it works with nosql databases as well we have an API running in production node+mongo and uses cursor based pagination

@twisted1919 I will create a component and perhaps share it on github for everybody to use

cursor based pagination eliminates lot of problems and it is very efficient if you compare performance on a fairly large data set.

1 Like
15

By “cursor based” you mean the same “keyset pagination”?

16

I know. I’m just saying it’s sad I won’t be able to use that in my Yii 2.0 project.

That would be swell.

17

essentially it is the same thing but different terms

1 Like