How to fix index error when querying GAE datastore?

Question

When I try to run a query on the datastore ordered by date I get the following error:

NeedIndexError: no matching index found.
The suggested index for this query is:

- kind: Message
  properties:
  - name: author
  - name: ref
  - name: date

The query runs without error if I don't try to order by date. The appengine console under datastore indexes says:

author ▲ , ref ▲ , date ▼   
Serving

What am I doing wrong? How can I run my query ordered by date? Thanks!

Here is my entity definition:

from google.appengine.ext import ndb

class Message(ndb.Model):
    subject = ndb.StringProperty()
    body = ndb.TextProperty()
    date = ndb.DateTimeProperty(auto_now_add=True)
    ref = ndb.StringProperty( required=True )
    author = ndb.KeyProperty(required=True)

and this is the query that fails:

def readMessages( ref, user = None ):
    query = Message.query()
    query = query.filter(Message.ref == ref )
    if user:
        query = query.filter(Message.author == user.key )
    query = query.order(Message.date)

# convert to a list so we can index like an array
return [ message for message in query ]

My index.yaml contains:

indexes:

- kind: Message
  properties:
  - name: author
  - name: ref
  - name: date
    direction: desc

Nolpros

Answer 1

You need to specify the "direction" as well because "ordering" is done when index is written to speed things up in Google's style.

So, your index.yaml should be like:

indexes:

- kind: Message
  properties:
  - name: author
  - name: ref
  - name: date
    direction: desc

Here's Google's official description about order:

The direction to sort, either asc for ascending or desc for descending. This is only required for properties used in sort orders of the query, and must match the direction used by the query. The default is asc.

I hope this helps.

Hydroelectric

Thanks. Actually, I forgot to copy the last line of my index definition. The app engine console indicates the index has been created as ref ▲ , author ▲ , date ▼ . So I don't think this is my problem, but I have updated the index definition in the question. – Nolpros

Answer 2

Thanks Lawrence, you got me on the right track- I think I have found the answer. The query must EXACTLY match the index definition.

I discovered this by trying different GQL queries in the datastore admin box.

For example, the following 2 queries:

SELECT * FROM Message where ref='' and author='' order by date 
SELECT * FROM Message where ref='' and author='' order by date asc 

both fail with:

no matching index found.

The suggested index for this query is:
- kind: Message
  properties:
  - name: author
  - name: ref
  - name: date

However,

SELECT * FROM Message where ref='' and author='' order by date desc

succeeds. Likewise, queries which have less parameters than the index contains will also fail, eg:

SELECT * FROM Message where ref='' order by date DESC

fails with:

no matching index found.

The suggested index for this query is:
- kind: Message
  properties:
  - name: ref
  - name: date
    direction: desc

So the problem was in my query, the line:

query = query.order(Message.date)

is actually sorting in ascending order, but my index says DESCENDING order. The fix is:

query = query.order(-Message.date)

Nolpros

you're welcome! yes that must be an exact match of your query and the index order. – Hydroelectric