In my website I implement the standard data loading when scrolling down the page. To implement such a solution, it naturally comes to mind to use the normal LIMIT in database queries with an offset.

But I have a question:

How to correctly take into account the changed data in the process?

For example, we have 400 employee records in the database. Employees have an is_work field (Works). When opening, the page requests the first batch of working employees.

 SELECT * FROM users WHERE is_work = TRUE LIMIT 0, 20

After we got the first page in our database, some of the strings in the is_work field could have changed. These changes could be made both by us and other users.

And it turns out that the following query using LIMIT 20, 20 will not return the data we would like to receive. (Or those depending on where the changes will be made)

Is there any standard solution to this issue?

Comment:

I can solve this problem in several not very beautiful ways, including getting the whole array, using the NOT IN construct, etc. (By ugly, I mean all the ways, the implementation of which will slow down in direct proportion to the growth of the base).

  • But just to put the site on the service and make it not available - is this an option? - user33274
  • Not. Not an option. - Ukolov Artem
  • It would be necessary to determine the words that the user should see. On the first page something is removed, the second page is requested. What to display on it, as if on the first page there were 19 entries? An entry has been added to the first page in the very beginning, how to be with the second page, it still should not show the record that “got out” from the 1st and what's new with it, not to show it, is it like on the 1st? In my opinion, how not to do it, the behavior will still seem strange. And what would ideally do is fix the date / time of the is_work change in the database and try to dance from it - Mike
  • If you manage to display a "snapshot" of the state, it will give rise to its problems. Users will be outraged: as so, there is new data, but we do not see. Or: why do you show the record as is_work, and it is no longer is_work! So it’s better to take it as it is: yes, sometimes the entries will shift relative to the page border. Understand and forgive, I believe. - artoodetoo
  • Thanks for answers. The solution with a reduced number of lines on the second page suits me perfectly. However, the question about this - how to track it? Here's a natural way to calculate that the sample will be smaller, and get those same 19 lines. Thank you - Ukolov Artem

0