Cannot combine 'order' and 'filters' in same query

[thesandlord]

I am trying to query comments that belong to a story.

query = datastore.Query(kind='Comment', filters=[('storyid', '=', 12345)], order=['timestamp'])

This code breaks with to following error (I also have additional internal debug info I can share):

gcloud.exceptions.PreconditionFailed: 412 no matching index found.

Trying to debug this, I found the following work with no problems:

Removing order

query = datastore.Query(kind='Comment', filters=[('storyid', '=', 12345)])

Removing filters

query = datastore.Query(kind='Comment', order=['timestamp'])

Changing order to use storyid

query = datastore.Query(kind='Comment', filters=[('storyid', '=', 12345)], order=['storyid'])

Why is this happening? The fact that the bottom three queries work and to top one does not is very confusing. Nothing I saw in the documentation indicates that filters and order affect each other.

[dhermes]

/cc @pcostell Maybe the error could list which index is required?

@thesandlord Give me a moment, I'll tell you which index is required (and how I determined that).

[dhermes]

@thesandlord I fired up the App Engine dev server and executed

from google.appengine.ext import ndb


class Comment(ndb.Model):
    story_id = ndb.IntegerProperty()
    timestamp = ndb.DateTimeProperty()


q = Comment.query(Comment.story_id == 12345).order(Comment.timestamp)
print q.fetch()

from the Interactive Console.

After doing this, the dev server autogenerated the following index.yaml file:

indexes:

# AUTOGENERATED

# This index.yaml is automatically updated whenever the dev_appserver
# detects that a new type of query is run.  If you want to manage the
# index.yaml file manually, remove the above marker line (the line
# saying "# AUTOGENERATED").  If you want to manage some indexes
# manually, move them above the marker line.  The index.yaml file is
# automatically uploaded to the admin console when you next deploy
# your application using appcfg.py.

- kind: Comment
  properties:
  - name: story_id
  - name: timestamp

[dhermes]

@thesandlord Check out the Cloud Datastore docs on indexes as well.

[thesandlord]

Ok makes sense, I need to do an index on both.

I'm not running in App Engine, which is why the indexes were not autogenerated. I'm using gcd to manually create the indexes, so explicitly stating the indexes that are missing would be a great feature!

[pcostell]

We plan to include index definitions in the error message, but haven't gotten to it yet. Please file a bug here: https://github.com/GoogleCloudPlatform/google-cloud-datastore/issues and we'll update it once it gets released.

[dhermes]

Awesome, thanks @pcostell!