NDB Query offset - limit

[Moises Belchin]

Hi guys,

I have this query:

q = MyKind.query()

regs = q.fetch(offset = 990, limit = 10) // Returns 10 entities.

If I press next button on my UI:

q = MyKind.query()

regs = q.fetch(offset = 1000, limit = 10) // Returns 0  entities .

MyKind has 1300 entities.

Any thoughts?

Thanks and regards.

Moisés Belchín.

[Guido van Rossum]

I can confirm this. I believe it is intentional to encourage you to use cursors instead of offsets for such queries; internally, you are paying for reading all the entities that you are skipping using the offset.

--Guido

[Moises Belchin]

Hi Guido,

Thanks for your answer.

We test to use query cursors and we decide don't use them because query cursors don't work in reverse order if you don't do query orders.

We have queries with many filters and we can't do query orders for this queries. In this case if we use cursors, the reverse function doesn't work properly.

We want to know how we must work in this cases without create indexes for this kind of queries.

Thanks and regards.

Moisés Belchín.

2012/9/20 Guido van Rossum <gu...@google.com>

--Guido

--
You received this message because you are subscribed to the Google Groups "Google App Engine" group.
To view this discussion on the web visit https://groups.google.com/d/msg/google-appengine/-/uNrSdb1M29wJ.
To post to this group, send email to google-a...@googlegroups.com.
To unsubscribe from this group, send email to google-appengi...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-appengine?hl=en.

[Guido van Rossum]

On Thu, Sep 20, 2012 at 1:59 AM, Moises Belchin <moises...@gmail.com> wrote:
> Hi Guido,
>
> Thanks for your answer.
>
> We test to use query cursors and we decide don't use them because query
> cursors don't work in reverse order if you don't do query orders.

If you have inequalities the order you give must start with the
property involved in the inequalities (<, <=, >, >=, !=).

> We have queries with many filters and we can't do query orders for this
> queries. In this case if we use cursors, the reverse function doesn't work
> properly.

Did you follow the recipe for paging backwards in the docs?
https://developers.google.com/appengine/docs/python/ndb/queries#cursors

> We want to know how we must work in this cases without create indexes for
> this kind of queries.

Can you post your model and query here? (Or mail them privately if you
think they're too sensitive.)

--
--Guido van Rossum (python.org/~guido)

[Guido van Rossum]

Hi Moises,

I think you may be able to solve this by adding a sort order by key, as follows:

q_next = q.order(MaqHistMov.idsm, MaqHistMov.key)
q_previous = q.order(-MaqHistMov.idsm, -MaqHistMov.key)

FWIW, here is an example that I think represents what you are doing:

class M(ndb.Model):
a = ndb.IntegerProperty()
b = ndb.IntegerProperty()
M(a=1, b=1).put()
M(a=2, b=1).put()
M(a=3, b=1).put()
M(a=4, b=2).put()
M(a=5, b=3).put()
M(a=6, b=4).put()
q = M.query()
q_asc = q.order(M.b, M.key)
q_desc = q.order(-M.b, -M.key)
r1, c1, m1 = q_asc.fetch_page(3)
print [r.a for r in r1]
r2, c2, m2 = q_asc.fetch_page(3, start_cursor=c1)
print [r.a for r in r2]
r3, c3, m3 = q_desc.fetch_page(3, start_cursor=c2.reversed())
print [r.a for r in r3]
r4, c4, m4 = q_desc.fetch_page(3, start_cursor=c3)
print [r.a for r in r4]

This outputs the following for me, which I think is correct:

[1, 2, 3]
[4, 5, 6]
[6, 5, 4]
[3, 2, 1]

Note that the final query does not reverse the cursor, since its start
cursor is already reversed. So you only reverse the cursor upon
reversing query directions.

--Guido

On Mon, Sep 24, 2012 at 4:12 AM, Moises Belchin <moises...@gmail.com> wrote:
> Hi Guido,
>

> Thanks in advance to take your time and figure out our problem.
>
> I send you the code we're using and an image to show you our model and some
> entities to review our query results.
>
> The first problem we encountered is that idsm property must be indexed if
> you want to order by MaqHistMov.idsm desc. If you use limit, offset query
> fetch this requirement doesn't exist.

[Guido van Rossum]

FWIW, this behavior appears to be a bug in NDB. I've filed
http://code.google.com/p/appengine-ndb-experiment/issues/detail?id=210
I'll investigate.

[Guido van Rossum]

On Tue, Sep 25, 2012 at 1:12 AM, Moises Belchin <moises...@gmail.com> wrote:
> Hi Guido,
>

> Thanks for your comments and help.
>
> I think adding sort order by key is a problem. For example:
>
> If you have a kind on which you have equals filters using fetch with limit,
> offset you don't need to use indexes. However if you use fetch_page, you
> need to create indexes for q_desc. If you have a big app and use indexes for
> any kind you'll quickly consume your index quotas.

OTOH the cost of using offset is not zero either -- you pay as much
for the skipped entities as for the results.

> In addition, this result,

>
> [1, 2, 3]
> [4, 5, 6]
> [6, 5, 4]
> [3, 2, 1]
>

> for me is not correct.
>
> I think if you show a grid to an user (1,2,3), the user clicks on next
> button and he views (4,5,6). Then If the user now clicks on previous button
> the user must view (1,2,3) instead of (3,2,1).
>
> Now I you use fetch with offset and limit you get this behaviour(1,2,3)->
> (4,5,6) <- (1,2,3), however with fetch_page and reversed cursor this not.

If you want to display the results as [3, 2, 1] just reverse the
results list in memory. [...].reverse() does the job in-line in O(N)
time with o extra space needed.

> I think, one option may be use fetch_page with positive or negative limit to
> simulate this behaviour. I don't know If this can be correct.

If you're proposing a feature change, underneath the implementation
would have to do all the same work. Consider fetch_page() a building
block that makes it possible to do the right thing, not necessarily
the most convenient thing (which would depend on the needs of a
particular app).

> Thanks for your time and for heard me.
> Regards.
> Moisés Belchín.
>
>
>
> 2012/9/24 Guido van Rossum <gu...@google.com>