Querying for a value existing in a model's list property in AppEngine

Question

A sample model:

  class Foo(db.Model):
     id = db.IntegerProperty()
     bar = db.ListProperty(int, required=True)

1. How can I query using either Query or GqlQuery to return all Foo entities that have a given value in their bar property?

2. If I have a list of ids, is there a single filter that will return all entities whose id property is in that list?

Platform

Answer 1

1.

If you use an equals query on a list property, it will check all items in the list:

search = 2
results = Foo.all().filter('bar =', search).fetch()

2.

You can use an IN filter, but note that internally this makes a datastore query for each item in the list, so it may be slow, and there are also a maximum of 30 internal queries per request.

items = [1, 2, 3]
results = Foo.all().filter("id IN", items).fetch()

See Introducing Queries for details for both 1 and 2, and ListProperty for further details on 1.

Carrick

Answer 2

For those of you like me who could not get the above answer to work.

Using gae version 1.5.3

results = Foo.all().filter('bar =', search).fetch()

gives no results. But

results = Foo.all().filter('bar =', search).fetch(100)
results = Foo.all().filter('bar =', search)

gives results.

Rezzani

Answer 3

Additionally you can also use Gql.. this may have been a development that happened in the time since the question was asked

For question 1

wheres = 2
pageSize = 10
qry = db.GqlQuery("SELECT * FROM Foo WHERE bar = :1", wheres)
Foos = qry.fetch(pageSize)

For question 2

wheres = [ 1, 3, 44, 101 ]
pagesize = 10
qry = db.GqlQuery("SELECT * FROM Foo WHERE bar IN (:1)", wheres)
Foos = qry.Fetch(pageSize)

Watch out for the in query, though. It will actually execute N subqueries (one for each element in your IN clause). Here's the Gql documentation: https://developers.google.com/appengine/docs/python/datastore/gqlreference

Chaparro