I have a complex sort order using several fields. The 'entry object' is the first item in the first batch defined by a query. I'm trying to create an infinite scroller where the entry item is shown at the top--scrolling down works as expected and scrolling up show items previous to the entry item.
Using a simple sort on a unique field like "username" is pretty easy but in my case the sort takes at least three fields in order to produce a unique ordering.
Reversing the sort order is not a problem either.
The question is how to define the entry object and get items both after and before it in the sorted collection using skip and limit. A negative value for skip is not allowed afaik.
Using mongoid and Rails the sort is defined by:
scope :rating_watchable, order_by(avg_rating: :desc, release_date: :desc, title: :asc)
The first object returned from the following is the 'entry object' at position 0:
Object.rating_watchable.where(:avg_rating.gte => 3).skip(0).limit(BATCH_SIZE)
This last query returns objects correctly and if I reverse the sort order I can get objects in the correct order but I can't think of how to define the same entry object using a relative "where" clause. I need this to get the skip/limit to work right in reverse order.
Sure would be nice to have a negative skip like this:
Object.rating_watchable.where(:avg_rating.gte => 3).skip( -BATCH_SIZE * batch_num ).limit(BATCH_SIZE)
maybe I'm missing some better way to do this?
The underlying problem is that you have a sort order, and you are trying to find the index of an object (the entry object) in the sort order. By definition, you have to get results from the sort in order to get the index of the entry object in the sort order. So...
1) Fetch the _id of the entry object, then 2) Fetch the _id's from the sort, then 3) Find the index of the entry object _id in the sort _id's
Given the above, you can now use skip and limit to display the initial page starting at the entry object, and calculate appropriate skip and limit values to move forward or back by a page or pages in the sort order.
MongoDB allows you to "project" selected top-level fields from documents, use this feature to select the _id's so that you don't have to pay the overhead for deserializing full documents for the complete sort results, see http://docs.mongodb.org/manual/tutorial/project-fields-from-query-results/ - In Mongoid, this is querying with Criteria#pluck, in Moped this is #select, see http://mongoid.org/en/mongoid/docs/querying.html
Hope that this helps.