Build a GQL with "IN' query for ReferenceProperty

Question

class Message(db.Model):

user = db.ReferenceProperty(User, required=True, collection_name='Message_set')  
text = db.TextProperty(required=True)

I tried this but I'm getting BadValueError on the gql statement.

users = []

users.append(userA)

users.append(userB)

users.append(userC)

messages = Message.gql('Where user In :users', users=users).fetch(100)

Answer 1

The IN operator in GQL is actually synthesized by application-level Python code as a loop doing a series of = fetches and concatenating them -- it's not really part of the underlying store's capabilities and so is unfortunately subject to some limitations (and its performance is never particularly good). I'm not sure it was a great design decision to include it in the Python API (I believe it was not duplicated in the Java API), but, there you are. (Btw, the != operator has similar issues, too).

I'm not sure why in particular this should interfere with your intended usage, but I suspect it's due to the "query" object being an instance of datastore.MultiQuery rather than plain datastore.Query -- what happens if you do in your own application-level Python code what the GAE-supplied application level Python code normally does? See in your SDK sources file google/appengine/api/datastore.py, specifically the MultiQuery class -- in the end it boils down to doing all the separate queries and merging their results (in sorted order, if required, but that doesn't seem to be the case here).

Answer 2

I think you may want a list of users' keys? try users.append(userA.key()). The value of the user referenceproperty is a key to a user entity.

Answer 3

It's hard to tell without the complete stacktrace (please?), but I would guess that IN doesn't like being passed entities rather than keys. Try this:

users = [userA.key(), userB.key(), userC.key()]
messages = Message.gql("WHERE user in :users", users=users).fetch(100

Answer 4

with this models:

class MyUsuario(db.Model):
    nombre=db.StringProperty(required=True)

class Message(db.Model):
    MyUsuario = db.ReferenceProperty(MyUsuario, required=True, collection_name='Message_set')  
    text = db.StringProperty(required=True)

This code works:

from modelos import MyUsuario, Message
from google.appengine.ext import db

usuarios=MyUsuario.all()

userA=usuarios[0]
userB=usuarios[1]
userC=usuarios[2]

usuarios= []

usuarios.append(userA.key())
usuarios.append(userB.key())
usuarios.append(userC.key())

messages = db.GqlQuery('select * from Message Where MyUsuario In :usuari', usuari=usuarios)

for message in messages:
    print message.text

I saw the awnser in this google groups post