Ability to read previous and next row based on column id

[Mrawesomes]

The ability to be able to easily pull the previous or next row based on the column id allows for simple per row pagination.

For example if someone is creating a blog and they want to show the previous and next blog post as links there is currently no easy way to do that.

I propose that in addition to the getFirst() there is a getPrevious() and getAfter() method. Each method would take at most three parameters (one requires, two optional).

1. identifier (this can be the id, date, etc...)
2. column (default to the first column of the table, generally id, but can also be different columns so people can choose for their circumstances)
3. size/limit/amount (default to 1 with an option to choose more if needed)

While you could potentially do this with pagination while keeping a state of all ids and offsets or raw sql it's not intuitive.

[SferaDev]

Hey!

We already have support for cursor based pagination, you can find more information and examples on https://xata.io/docs/sdk/get#the-typescript-sdk-functions-for-querying. The page object returns helper methods to navigate to previous and next pages already.

[Mrawesomes]

You might be misunderstanding me. There are many cases where pagination is far too bloated, and unusable, for the kinds of requests needed.

For example, if you are building a gallery application where you want a previous and next button for the photos. What you are saying to me is the way to move back and forward over each photo is to request ALL the photos and then find the photo that the user has landed on in the list, compute where in the order it is in, to then have the information to render the page properly?

If the gallery app has direct individual pages for each photo. At like /image/[imageid] for example. All the information the developer has is the [imageid] (which isn't just a incrementing number). How would any developer scale that properly? They wouldn't use pagination. They would make a simple sql request which is like select id from images where id > [imageid] limit 1 for moving up (>) and down (<) the table when needed. Pagination is not an adequate solution for those cases.

[SferaDev]

In those scenarios you can use the new SQL endpoint, for other feature requests please send them over to https://feedback.xata.io/feature-requests

[tsg]

hey @Mrawesomes thanks for the feedback on this!

To make sure, you can do the equivalent of select id from images where id > [imageid] limit 1 with something like this:

const users = await xata.db.Users
    .select(["id"])
    .filter({ id: { $gt: "effie" } })
    .getFirst();

Would that help? Just making sure you know you can use the $gt operator on the ID column.

I did open a feature request for getPrevious and getAfter here: https://feedback.xata.io/feature-requests/p/getprevious-and-getafter-on-the-record-in-the-sdk I think they might be worth it as part of the SDK magic.