MongoDB, sort() and pagination

1
votes

I known there is already some patterns on pagination with mongo (skip() on few documents, ranged queries on many), but in my situation i need live sorting.

update:

For clarity i'll change point of question. Can i make query like this:

db.collection.find().sort({key: 1}).limit(n).sort({key: -1}).limit(1)

The main point, is to sort query in "usual" order, limit the returned set of data and then reverse this with sorting to get the last index of paginated data. I tried this approach, but it seems that mongo somehow optimise query and ignores first sort() operator.

1
mongodbsortingpagination
The standard sorting approaches (using skip+limit or $gt/$lt key) both provide 'live' sorting that pick up new docs. Can you elaborate on what's different about your case? - JohnnyHK
Using skip()+limit() is not considered - slow (0-10ms<) on large amount of data/documents(1mil<). I need to query result, which based on the current state of the data, not some ranges or some linking pages to data. So far i tried 2 sort()'s approach (sort(k:1).limit(n).sort(k:-1).limit(1)), but it seems that it doesn't work. Main idea is to use mongodb with its functionality only, and if there would be 1< queries thats ok, its still be faster wth indexes. - alex4k
"I need to query result, which based on the current state of the data, not some ranges or some linking pages to data" I think this is still a bit of confusion to us, it doesn't ring true. What IS the current state of the data for you? Do you mean before the user refreshes the page or after? And what is "linking pages to data"? - Sammaye
Ok, sorry for confusion.. With ranged queries when user starting viewing/seaching his first query have initial key/position, which we can use for pagination in future, some starting point. But that point is static, like stale cache. User can go forward and backward, but state of each current item on page would be the same, cause we have some initial information on first query. I need to find approach, where user getting fresh set of data with each pagination query. "new" in term of ltest state in db. - alex4k
@alex4k Aha I think I see now, when English fails code wins :) anyway I have edited my answer. - Sammaye

1 Answers

1
votes

I am having a huge problem attempting to grasp your question.

From what I can tell when a user refreshes the page, say 6 hours later, it should show not only the results that were there but also the results that are there now.

As @JohnnyHK says MongoDB does "live" sorting naturally whereby this would be the case and MongoDB, for your queries would give you back the right results.

Now I think one problem you might trying to get at here (the question needs clarification, massively) is that due to the data change the last _id you saw might no longer truely represent the page numbers etc or even the diversity of the information, i.e. the last _id you saw is now in fact half way through page 13.

These sorts of things you would probably spend more time and performance trying to solve than just letting the user understand that they have been AFAK for a long time.

Edit

Aha, I think I see what your trying to do now, your trying to be sneaky by getting both the page and the last item in the list at the same time. Unfortunately just like SQL this is not possible. Even if sort worked like that the sort would not function like it should since you can only sort one way on a single field.

However for future reference the sort() function is exactly that on a cursor and until you actually open the cursor by starting to iterate it calling sort() multiple times will just overwrite the cursor property.

I am afraid that this has to be done with two queries, so you get your page first and then client side (I think your looking for the max of that page) scroll through the records to find the last _id or just do a second query to get the last _id. It should be super dupa fast.