query using IN list on GAE?

[Dan]

What is the proper way to run a query on Google App Engine that uses
an IN clause?

I want to do something like this (in traditional SQL)
SELECT person.name, person.age
FROM person
WHERE person.name IN ('Ralph','Henry','Sue')

After searching around here and the web2py docs, I tried two different
approaches, and neither of them worked. Please let me know what I can
do to fix it. Thanks!
Dan


FIRST ATTEMPT:
personlist = ['Ralph','Henry','Sue']
personresultsqry = db.person.name.belongs(personlist)
personresults = db(personresultsqry).select(db.person.name,
db.person.age)

the error from the GAE dev app server is this:
AttributeError: 'SQLField' object has no attribute 'belongs'

SECOND ATTEMPT:
personlist = ['Ralph','Henry','Sue']
personresultsqry = db.GqlQuery("SELECT * FROM web2py_person WHERE
name IN :1", personlist)
personresults = db(personresultsqry).select(db.person.name,
db.person.age)

the error from the GAE dev app server is this:
KeyError: 'GqlQuery'

[mdipierro]

As far as I know GAE does not support this. The only option is to use
the ListProperty but web2py does not have an interface for it.

Massimo

[Dan]

the IN operator is supported according to
http://code.google.com/appengine/docs/python/datastore/queriesandindexes.html#Introducing_Queries

The IN operator also performs multiple queries, one for each item in
the provided list value where all other filters are the same and the
IN filter is replaces with an equal-to filter. The results are merged,
in the order of the items in the list. If a query has more than IN
filter, the query is performed as multiple queries, one for each
combination of values in the IN filters.
A single query containing != and IN operators is limited to 30 sub-
queries.


but it looks like the gql.py file has commented out the code for
belongs/IN (line 309) -- why?

[Dan]

is it possible to create a GQL format query and send it to the GAE
datastore that web2py connects to? what would the code for that be?

On Jun 15, 10:25 am, Dan <danbr...@gmail.com> wrote:
> the IN operator is supported according tohttp://code.google.com/appengine/docs/python/datastore/queriesandinde...

[mdipierro]

I have not tested/debugged it yet but you can try the latest trunk

db(db.table.field.belongs(('a','b','c'))).select()

Let me know if it works or not, and if not, what's the error.
We can make it work replatively soon.

Massimo

[Dan]

that works great! note that I didn't try to send more than 30 items -
so I don't know what would happen if that limit were exceeded.

thanks Massimo!

Dan

[mdipierro]

:-)

[Dan]

whoops, sorry- I was working with the older version of web2py when I
concluded that it was working properly.

using the trunk version, it did not work on GAE -- but does work on
SQLite. This is the error message I got (edited a bit for simplicity).
I'm using an array of integers as the argument to the belongs()
method, intending for them to match the user table's id field.

File "gluon/contrib/gql.py", line 309, in belongs
return SQLQuery(self,' IN ',value)
File "gluon/contrib/gql.py", line 530, in __init__
raise SyntaxError, 'id value must be integer'
SyntaxError: id value must be integer

[mdipierro]

You cannot do 'id IN' because GAE treats ID in a spacial way. Please
try a different field.

Massimo

[Dan]

On Jun 15, 4:02 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> You cannot do 'id IN' because GAE treats ID in a spacial way. Please
> try a different field.
>
> Massimo
>

shoot. I didn't see any mention of that in the GAE docs... can you
point me to it?

I can run a separate "=" query for each of the elements that I want to
include in an "IN" condition. Not efficient, but it should work.

Dan

[mdipierro]

Can you show me an example of how you do it?


>
> Dan

[Dan]

> > > You cannot do 'id IN' because GAE treats ID in a spacial way. Please
> > > try a different field.

> > I can run a separate "=" query for each of the elements that I want to
> > include in an "IN" condition. Not efficient, but it should work.

> Can you show me an example of how you do it?

Hi Massimo-
Here's an example. I'm using a slightly different data set than what I
described before, so allow me to re-introduce the problem.

I'm using GAE and also testing on SQLite. I have 2 tables in the
datastore, which I'm using to store information about people and their
pets. They would be defined like this in the model, which overrides
the default auth user table and has another table for the
relationships between pet owners and pets:

auth.settings.table_user = db.define_table( ## stores information
about people and pets
auth.settings.table_user_name,
db.Field('name', length=32,default='',
requires=[IS_NOT_EMPTY(),IS_NOT_IN_DB
(db,'%s.name'%auth.settings.table_user_name)]),
db.Field('is_human', 'boolean', default=True, ## for pets, this
should be set to "False"
writable=False, readable=False, requires=IS_NOT_EMPTY()),
db.Field('email', length=128,default='',
requires=[IS_NOT_EMPTY(),IS_EMAIL(),
IS_NOT_IN_DB
(db,'%s.email'%auth.settings.table_user_name)]),
db.Field('password', 'password', readable=False, label='Password',
requires=CRYPT()),
db.Field('registration_key', length=128, writable=False,
readable=False,default=''),
)

db.define_table('relationships',
SQLField('person_id',auth.settings.table_user,
requires=IS_IN_DB(db
(auth.settings.table_user.is_human=="True"),
'%s.id'%auth.settings.table_user_name,
'%s.name'%auth.settings.table_user_name)),
SQLField('pet_id',auth.settings.table_user,
requires=IS_IN_DB(db
(auth.settings.table_user.is_human=="False"),
'%s.id'%auth.settings.table_user_name,
'%s.name'%auth.settings.table_user_name)),
)

Then in the controller, I want to show each user a form which will
have a select input allowing the user to pick one of the pets that he
owns. And I want to validate the form variables to make sure that the
selected pet is owned by the user. I'm using auth and requiring the
user to be logged in. So here is the code:

@auth.requires_login()
def report_form():
# first run a query to find out which pets the current user owns
pet_ids_allowed = db
(db.relationships.person_id==auth.user.id).select()
# then run a query to get the name of each of the pets owned
# for GAE compatibility, we will run 1 query for each pet.
# If this was running on SQLite, we could use a belongs/IN clause
and get the names of all the pets in one query
petslookup = {} # associate userid with each pet. we'll use this
for validation
for p in pet_ids_allowed:
petname = db(db.auth_user.id==p['pet_id']).select()[0]['name']
petlookup[petname] = p['pet_id']

form=FORM(TABLE(
TR("Pet Name:",SELECT(_name='petname',
requires=[IS_NOT_EMPTY(),IS_IN_SET(petlookup.keys())],
*[OPTION(k, _value=k) for k in petlookup.keys()]
)),
TR("",INPUT(_type="submit",_value="Submit")) ))

if form.accepts(request.vars,formname=None):
response.flash="form accepted input"
pet_id = petlookup[petname]
actionsummary = "this is where we would do something with the
form input."
elif form.errors:
response.flash="form is invalid"
actionsummary="Information received was invalid. Nothing
done."