0

I have an SQL table which holds all assignments for all users on the site, and these assignments can be completed/re-opened/edited/deleted if you have the necessary permissions. I'm displaying these assignments to each user in an HTML table with AJAX pagination and sorting, and what I'm trying to achieve is to display the data as it was on a specific page load.

As it is currently, since the data can be changed at any time, when you go to the next page (or change the sorting) you might get unexpected results (data you've already seen, or even no data at all). Another problem here is that the user can interact with these assignments (complete/delete via AJAX), and I want to take these changes into account for the current page load (and any subsequent ones), but not for anyone who opened the page before these changes occurred, they should see the data as it was on their page load.

I hope this makes at least some sense. Does anyone have an idea how this could be accomplished? I'm not looking for complete solutions, a nudge in the right direction would be more than enough. Thanks!

spinn
  • 1
  • 1
    Is this a big table? If not then maybe you could load all rows at once and paginate on client? But I would add o LastModified (DATETIME) column and load appropriate data. – Ziouas Feb 25 '14 at 13:38
  • From your question, it is most likely that you are using ORDER BY (id) for pagination. If you want to have persistent pages you should use timestamp column for example, and next page is always "posts older than the last post on the current page". That would eliminate the movements when the element is deleted. However, when the sorting is changed, you must get different results, I dont know what you thought about that. – dkasipovic Feb 25 '14 at 13:39
  • Would it be practical to return a complete data set, and then handle all the pagination at the application level? That way, you make one hit to the database, so the data set won't change (unless you reload the page) – Strawberry Feb 25 '14 at 13:39
  • you should use `session variables` to save your last search ,sort and order.. so you can retrieve it back if you go back to that page.. – Nishant Solanki Feb 25 '14 at 13:40
  • @Ziouas - every user can potentially have hundreds or thousands of assignments - not something I'd want to load from the DB on every page load. – spinn Feb 25 '14 at 14:16
  • @D.Kasipovic - there are multiple sort options (priority, date created, last edited). Also, the problem is that assignments can be changed (completed/edited/re-assigned), but I still want to display the data as it was on page load, so the timestamp wouldn't help here. – spinn Feb 25 '14 at 14:18
  • @Strawberry - I thought about that, but since each user can have a large amount of assignments, it seems kinda wasteful to load hundreds (or thousands) of table rows on every page load. – spinn Feb 25 '14 at 14:19
  • My point is, you should not use LIMIT 300,100 for a third page of 100 assignments, you should use WHERE id < LAST_ID_OF_THE PAGE LIMIT 100 if you want to have some consistency, altough if something is deleted from the next page (the one user has not seen yet), user will not see it obviously. – dkasipovic Feb 25 '14 at 14:22
  • @D.Kasipovic - but since there are multiple sort options, the ids are not always consecutive. The first page might contain ids 3, 4 and 500, while the second page might contain 1000, 1001 and 2, depending on your sorting. – spinn Feb 25 '14 at 14:25
  • Yes, but when you sort, instead of ID use column by which the table is sorted, the principle should be the same? – dkasipovic Feb 25 '14 at 14:26
  • @D.Kasipovic - some sortings are a bit more complicated (on 2 or more columns) where some values can be non-unique. You can also sort by name and it's possible that many assignments could have the same name. – spinn Feb 25 '14 at 14:30
  • Well then, another option is that for page of for example 100 elements, you take in the background 300 elements (100 for page before, 100 for current, and 100 for next), so when the user goes to next you display the "next 100" which are preloaded and then load new 100 for the next. Current page becomes previous 100. I took 100 as an arbitrary number but you can extend it as you like. Only problem is, if in the meantime element from next 100 is deleted (while user is on current page) they will still see it on the next page even though it really does not exist. – dkasipovic Feb 25 '14 at 14:35
  • @D.Kasipovic - I've been toying with this idea as well, but this would also mean that the user could get two similar pages (or identical, or even completely unexpected ones) if a larger chunk of data gets changed from page load to the point when we load the next 100. I'm currently thinking I'd have to save historical data (revisions) and display only the appropriate revisions. But this seems like an overly complicated solution. – spinn Feb 25 '14 at 14:51
  • It really depends a lot on your current structure, back-end code, languages, databases, etc. – dkasipovic Feb 25 '14 at 14:53

1 Answers1

0

At the request of the page, request all the data at once from your database and hold this in a variable. Then let your AJAX get his data from that variable instead of directly from the database.

Edit:

I guess you have to solve it in your database, if you don't want to pull all the data at once. I'm not the best in SQL but try something like: this . You can then pull your data from this temporary table that cannot be changed by others. Make sure you clean up those tables as well, or you will get a HUGE database.

Community
  • 1
  • 1
Wesley De Keirsmaeker
  • 1,026
  • 9
  • 29
  • I thought about that, but since there can be a large amount of data, it seems kinda wasteful to load everything (hundreds or even thousands of table rows) at once. – spinn Feb 25 '14 at 14:20
  • I guess you have to solve it in your database then. I'm not the best in SQL but try something like: http://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table . You can then pull your data from this temporary table that cannot be changed by others. Make sure you clean up those tables as well, or you will get a HUGE database. – Wesley De Keirsmaeker Feb 25 '14 at 14:27
  • I was also thinking that it would be best to solve this in the DB, but haven't yet come up with a good-enough solution. Temporary tables are discarded when the connection closes (which means I couldn't reference it later on). Views are either non-updatable (so you can't remove anything from them), or they get updated when the underlying tables get changed, depending on how you create it. I thought about creating a new table for each page load, but this also seems wasteful and, as you said, the DB would get huge (I would periodically drop old unneeded tables, but this does not seem optimal). – spinn Feb 25 '14 at 14:41
  • You could just make a table for each page load, and drop the tables once your php session is destroyed. – Wesley De Keirsmaeker Feb 25 '14 at 14:48