How to architecturally make loadable lists?

Question

Software design

There is a backend and there is a frontend.
The front displays a list of anything, transactions for example. He takes transactions from the back.
There are a lot of transactions, I don’t want to pull all the records to the front at once, so it was decided to load them in parts, as you scroll down the list.

Problem: I would like to have sorting of records on the front, but it turns out that new loaded records are not sorted or sorted differently than the user chose. I do not want to redraw the entire list, but I feel I will have to.

Question: maybe there are standard solutions how to make it convenient for the user and how to plan it architecturally (at the level of service methods)?
While there is an idea to give back the id of the entry on which the user was in the list, well, or it was in the middle of the visible part, and request a "window" of the list so that this entry is in the same place on the screen.

Answer 1

but it turns out that the new uploaded records are not sorted or sorted differently than the user chose

When requesting transactions from the back, what prevents you from sending the required sorting with the request so that new records are sorted in the same way?
If you want to understand what to do when changing the sorting on the front, then there are different options - one of them you voiced is quite normal, but you will need to find the position of the desired id in the new sorted list. you can just start showing the list again.
In general, there are two main common ways to organize data loading - page loading (pass the page number or the index of the page start record in the request) and cursors (pass the cursor and the number of objects to load to the back, the back returns a new cursor along with the data that you use to subsequently load new ones data).

Answer 2

Of course, we will send the required sorting, and load it with paginated output. The problem is different, maybe I didn't explain clearly.
Let's say you are a user, looking for the desired transaction, scrolled through a couple of hundreds. Come to the conclusion that somehow it’s still far to scroll through, you need to sort them also by a couple of other parameters in order to find them faster. Turn on the sorting options (another case - they switched the sorting by mistake) and everything disappeared, start again from the beginning of the list.
That's the shock I want to avoid :)

Answer 3

In several stages:
1. The request for sorting and the active record itself (id) in the position of the "cursor" (or the serial number of the position at the beginning of the entire list, if there is suddenly no active "cursor") are sent to the back.
2. We make a sorting/filtering query in the database and save the current result of the selection to temporary storage.
3. At the front, when ready: we immediately take the list of elements relative to the location of the "cursor" in the "display window" (or the number of elements from the beginning of the list) , which completely fits into this "window".
4. As we scroll, we take the pieces of the selection from the Ajax backing (sets of elements of the "display window") located above or below. This technique is called "virtual window" .