How do I query a many to many relationship model? - Google App Engine

Question

Here are my models:

class User(db.Model):
    id = db.StringProperty(required=True)
    created = db.DateTimeProperty(auto_now_add=True)
    updated = db.DateTimeProperty(auto_now=True)
    name = db.StringProperty(required=True)
    email = db.StringProperty()

class Page(db.Model):
    id = db.StringProperty(required=True)
    created = db.DateTimeProperty(auto_now_add=True)
    updated = db.DateTimeProperty(auto_now=True)
    name = db.StringProperty(required=True)
    link = db.StringProperty(required=True)

class UserPage(db.Model): 
    user = db.ReferenceProperty(User, collection_name='pages') 
    page = db.ReferenceProperty(Page, collection_name='users')

How would I construct a query to find a users pages?

I found an article that describes a method to do it but is this the best way? http://blog.arbingersys.com/2008/04/google-app-engine-better-many-to-many.html

Answer 1

After some testing, it appears I can use:

 myuser = User.get_by_key_name("1")
 up = UserPage.all().filter('user =', myuser).fetch(5)
 for x in up:
     self.response.out.write(x.page.id)

Answer 2

Your answer will work, but it will perform 7 calls to the datastore:

• 1 for the call to User.get_by_key_name()
• 1 for the call to UserPage...fetch()
• 5 for each dereference of x.page.id inside the loop

An alternative approach which only does 3 calls to the datastore would be something like this:

myuser = User.get_by_key_name("1") 
up = UserPage.all().filter('user =', myuser).fetch(5)
keys = [UserPage.page.get_value_for_datastore(x) for x in up]
pages = db.get(keys)
for p in pages: 
     self.response.out.write(p.id)

See http://blog.notdot.net/2010/01/ReferenceProperty-prefetching-in-App-Engine for more details.

Answer 3

I would recommend a different approach, that is less "relational-oriented" than your UserPage relationship:

class User(db.Model):
    id = db.StringProperty(required=True)
    created = db.DateTimeProperty(auto_now_add=True)
    updated = db.DateTimeProperty(auto_now=True)
    name = db.StringProperty(required=True)
    email = db.StringProperty()

class Page(db.Model):
    id = db.StringProperty(required=True)
    created = db.DateTimeProperty(auto_now_add=True)
    updated = db.DateTimeProperty(auto_now=True)
    name = db.StringProperty(required=True)
    link = db.StringProperty(required=True)

    # Users linking to this page
    users = db.ListProperty(db.Key)

And then you can get all pages of a specific user with the following query:

Page.gql("WHERE users = :1", user.key())

Please note that you should place the list property of keys on the side where you expect less items. I've assumed you will have less users liked to a page, than pages linked to a user, so I've put it on the Page side, but that will depend on your specific use case.

See here for official recommendations on the many-to-many topic: http://code.google.com/appengine/articles/modeling.html