How to page using range queries?

[Donny V.]

I'm probably missing something since I'm still learning the in's and
outs of MongoDB.
But I need help with paging a collection.

I have a collection that has a list of names.

Bottom Round of Beef
Chicken Breast 6oz
Chicken Breast 8oz
Chicken Breast 8oz
Chicken Breast 8oz
Chicken Breast Random
Chicken Legs
Chicken Tenderloin
Chicken Thighs
Kosher Salt

I run this query.
db.ProductGuideItem.find( { ProductName : { $gt : "Chicken Breast
8oz" } } ).sort({ProductName:1}).limit(3);

Notice there are 3 "Chicken Breast 8oz".

If I run that query I get...
Chicken Breast Random
Chicken Legs
Chicken Tenderloin

If I was paging and started from the top. The query would have missed
the other 2 "Chicken Breast 8oz".

Is there a way around this?
Also how would I do this if the list was sorted the opposite way?

[Andrew Armstrong]

I think you want to be using .skip().limit() (and don't alter your original query you used to get Page #1).

See http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-%7B%7Bskip%28%29%7D%7D for more info

[Sam Millman]

As Andrew said Skip() and Limit() are used, for subdocuments slice() is what you need.

"If I was paging and started from the top. The query would have missed
the other 2  "Chicken Breast 8oz"."

Now sure I understand what you mean, the query will miss them anyway cos of you getting all names greater than that term. The only way (same as SQL) to page by "real" entities is to commit two calls. One to get the pages based on distinct and one to get elements for those pages based your criteria (I may be wrong by what you mean) however, that would create unreliable paging...

On 1 April 2011 06:00, Andrew Armstrong <phpl...@gmail.com> wrote:

I think you want to be using .skip().limit() (and don't alter your original query you used to get Page #1).

See http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-%7B%7Bskip%28%29%7D%7D for more info

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com.
To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

[Eliot Horowitz]

Make a compound index/query on name,_id
Then you can start off on the name,_id where you left off so you don't
miss anything.

[Donny V.]

I build the compound index on name,id but the query is still missing the other dups.

[Nat]

you should change your query too

db.ProductGuideItem.find( { ProductName : { $gt : "Chicken Breast
8oz" } }

).sort({ProductName:1,_id:1}).limit(3);

[Scott Hernandez]

You must include both the ProductName and id in your query; the id
part will make sure you don't get dups. Also, you must sort on that
combination as well, to ensure you use the index so your pagination is
consistent.

[Donny V.]

I'm still getting the same result.

This is my query.

[Scott Hernandez]

You need to use the last _id value in the query, as i mentioned earlier.

On Sat, Apr 2, 2011 at 8:29 AM, Scott Hernandez
<scotthe...@gmail.com> wrote:
> You must include both the ProductName and id in your query; the id
> part will make sure you don't get dups. Also, you must sort on that

See how your query still only has one criteria, the ProductName?

[Donny V.]

I'm not sure what that looks like in the query.

Can you give me an example using my query?

[Scott Hernandez]

Assuming you have these values:

{a:1, b:1}
{a:2, b:1}
{a:2, b:2}
{a:2, b:3}
{a:3, b:1}

So you do this for the range based pagination (page size of 2):
find().sort({a:1, b:1}).limit(2)
{a:1, b:1}
{a:2, b:1}

find({a:{$gt:2}, b:{$gt:1}}).sort({a:1, b:1}).limit(2)
{a:2, b:2}
{a:2, b:3}

find({a:{$gt:2}, b:{$gt:3}}).sort({a:1, b:1}).limit(2)
{a:3, b:1}

You use the previous values for the next page of data, and the start
values for previous page. You can also use a hybrid solution with
ranges and skips which will be much faster than just using skip when
you have an index.

Make sense?

On Sat, Apr 2, 2011 at 12:25 PM, Donny V. <don...@gmail.com> wrote:
> I'm not sure what that looks like in the query.
> Can you give me an example using my query?
>

[Donny V.]

Yeah I tried that and didn't work.

This is the query

db.ProductGuideItem.find( { ProductName: { $gt: "Chicken Breast 8oz" }, _id:{$gt:ObjectId("4d8e43f14b3f391734658594")} } ).sort({ProductName:1,_id:1}).limit(3);

Each page has 3 items and I want to see page 2 then I should see..

Chicken Breast 8oz

Chicken Breast 8oz

Chicken Breast Random.

But instead its giving me

Chicken Thighs

Kosher Salt

[Scott Hernandez]

Sorry, I forgot this is (still) a little more complicated. Let me try this again, after actually testing:

Assuming you have these values:

{a:1, b:1}
{a:2, b:1}
{a:2, b:2}
{a:2, b:3}
{a:3, b:1}

So you do this for the range based pagination (page size of 2):
find().sort({a:1, b:1}).limit(2)
{a:1, b:1}
{a:2, b:1}

find().min({a:2, b:1}).sort({a:1, b:1}).skip(1).limit(2)

{a:2, b:2}
{a:2, b:3}

find().min({a:2, b:3}).sort({a:1, b:1}).skip(1).limit(2)
{a:3, b:1}

Here are the docs for $min/max:

http://www.mongodb.org/display/DOCS/min+and+max+Query+Specifiers

I'll put it on my list to document range based paging when using a compound index. Without the compound index you don't need to use $min/max and gt/lt will work fine.

[Donny V.]

You ROCK!!

Yeah the only reason I was using the compound index is because "ProductName" had duplicates in it.

You should probably add that to the help text too, so people know why there doing min max with compound index.

Now the reason I was doing paging this way is because its faster and scales better for larger collections then the 

traditional skip and limit....right?

http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-%7B%7Bskip%28%29%7D%7D

[Donny V.]

Also if you have a stackOverflow account you can post the answer there, I created the same question there too.

http://stackoverflow.com/questions/5525304/how-to-do-pagination-using-range-queries-in-mongodb

If not just let me know, so I can post the answer so other people can benefit also.

Thanks again!

[Theo]

I've answered the StackOverflow question. It looks to me like a simple case of using $gt instead of $gte in the query. "Chicken Breast 8oz" is *not* greater than itself, therefore it's not included in the results. Change it to $gte and it should be.

[Scott Hernandez]

Yep, when you get to large collections, using skip/limit is just not
usable. This really only matters when the skip counts gets into large
numbers.

[Scott Hernandez]

This will include overlapping results and not page correctly when
there are duplicates.

[Scott Hernandez]

You can use a combination of the range query + skip. The key to this
process is never skipping very far at a time. If your skips are always
small then you should be fine.

On Sat, Apr 2, 2011 at 7:58 PM, Donny V. <don...@gmail.com> wrote:
> Sorry I have 1 more question.
>
> Is there a way to jump directly to a page using these range queries?
> Like jump right to page 3.

[Donny V.]

I think I have an idea on how to be able to jump to any page.

1. I was thinking what if you get the total count of pages there will be.
   
2. Then use the technique we have here using the min and max to get all the first values of all the pages
3. We then store this page index that has the page number and starting values in a temp collection or session variable

This should now give us an index to look up any page.

What do you think?

[Scott Hernandez]

So, from a technical point of view this is very doable but I must
wonder what use-case you have where you need to do this.

Generally people search, and want to look at a very small number of
results; the first 3 pages get 99% of the clicks or something like
that.

In fact, when you get to very scalable pagination where one might want
to go to the end of the results, really that just means you want to
reverse the sort and move in that direction.

[Donny V.]

This is going to be used in an inventory system.

So they will be jumping around a lot. There not really looking for 1 particular item.

They will be looking for a bunch of different items.

[Scott Hernandez]

If your inventory isn't millions upon millions of docs and you aren't
supporting lots of concurrent users you might not need these kind of
optimizations :)

I would suggest testing and see how usable the system is before going
too much farther, but that is just my 2c.

[Donny V.]

Not millions of records, probably a couple hundred thousand.

Its going to be a web app for restaurants, so definitely a lot of concurrent users.

Do you think I should just use the classic skip and limit for now?

[Scott Hernandez]

I'd stick with the range queries plus skip and only show 5 pages at
most; I would suggest testing early and often :)