How to implement robust pagination using the RESTful API when changing a result set?

Question

I implement a RESTful API that provides Orders as a resource and supports pagination through a result set:

GET /orders?start=1&end=30 

where orders in paginate are sorted by ordered_at timestamp, descending. This is basically the # 1 approach from the SO Pagination question in a REST web application .

If the user requests a second page of orders ( GET /orders?start=31&end=60 ), the server simply re-requests the table of orders, sorts by ordered_at DESC again and returns the entries at positions 31-60.

I have a problem: what happens if the result set changes (for example, a new order is added) when the user views the records? In the case of adding a new order, the user will see the old order # 30 in the first position on the second page of results (because the same order is now No. 31). Even worse, if deleted, the user sees the old order # 32 in the first position on the second page (No. 31) and does not see the old order # 31 (now # 30).

I don’t see a solution for this without any change in the status of the RESTful server (urgent) or the creation of some smart code in each client ... What are some established methods to deal with this?

For completeness: my back-end is implemented in Scala / Spray / Squeryl / Postgres; I create two front-end clients, one in backbone.js and the other in Python Django.

Answer 1

The way I do this is to make indexes from old to new. Therefore, they never change. And then when prompted without any initial parameter, return the newest page. Also, the answer should contain an index indicating which elements are contained, so you can calculate the indexes that need to be requested for the next older page. Although this is not exactly what you want, it seems to be the easiest and cleanest solution for me.

Original request : GET /orders?count=30 returns:

 { "start"=1039; "count"=30; ...//data } 

From this, the consumer expects that he wants to request:

The following queries: GET /orders?start=1009&count=30 , which then returns:

 { "start"=1009; "count"=30; ...//data } 

Instead of raw indexes, you can also return a link to the following page:

 { "next"="/orders?start=1009&count=30"; } 

---

This approach breaks if elements are inserted or removed in the middle. In this case, instead of the index, you need to use some additional constant value that increases the value.

Answer 2

The sad truth is that all the sites that I see are paginated in this sense, so there can be no easy way to achieve this.

A quick workaround can change the order, so the item position is absolute and unchanged with new additions. On the first page, you can specify the latest indexes to ensure constant navigation from there.

• Pros: the same url gives the same results
• Cons: There is no obvious way to get the last elements ... Perhaps you could use negative indexes and redirect the results page to absolute indexes.

Answer 3

With the RESTFUL API, the state of the application must be in the client. Here, the state of the application should indicate some time stamp or version number when you started looking at the data. On the server side, you will need some form of audit trail, which is the proper server data, since it does not depend on whether the clients were and what they did. At least he should know when the data was changed. There is no conflict with REST.

You can add a version parameter to your get. When a client first requires a page, it usually does not submit a version. Server responses contain one. For example, if there are links in the response to the following / other pages, these links contain & version = ... The client must send a version requesting another page.

When the server receives any request with the version, it should at least know if the data has changed since the client began to search, and depending on what type of checks you have, how they changed. If they did not, it responds normally, passing the same version number. If they are, he can at least tell the client. And depending on how much he knows how the data has changed, he may respond appropriately to the answer.

As an example, suppose you get a request with a start, shutdown, version, and you know that since the version was updated, the three lines that were before the start were deleted. You can send a redirect using start-3, end-3, new version.

Answer 4

WebSockets can do this. You can use something like pusher.com to capture changes in your database in real time and push the changes to the client. Then you can bind different push events to work with models and collections.

Answer 5

Just go there. Please feel free to tell me if this is completely wrong and why so.

This approach attempts to use the left_off variable to sort without using offsets.

Consider what you need to do your result. Sorted by timestamp order_at DESC. So when I request the first result set it

 SELECT * FROM Orders ORDER BY order_at DESC LIMIT 25; 

right? This is the case when you request the first page (in terms of URL, it’s probably a request that doesn’t have

?

yoursomething.com/orders limit = 25 & left_off = $ timestamp

Then upon receipt of your dataset. just take the timestamp of the last viewed item. 2015-12-21 13:00:49

Now, to request the following 25 points, follow the link: yoursomething.com/orders?limit=25&left_off=2015-12-21 13:00:49 (to view the time stamp viewed)

In Sql, you just make the same request and say where the timestamp is equal to or less than $ left_off

 SELECT * FROM (SELECT * FROM Orders ORDER BY order_at DESC) as a WHERE a.order_at < '2015-12-21 13:00:49' LIMIT 25; 

You should get the following 25 items from the last seen.

For those who see this answer. Please comment on whether this approach is suitable or even possible in the first place. Thanks.