Google App Engine Datastore query problem

Question

I have the following problem:

I'd like to retrieve all products of a category

class Category(emodel):
      name = db.StringProperty()

class Channel(emodel):
      name = db.StringProperty()
      category = db.ReferenceProperty(Category,collection_name="cat_set")

class Product(emodel):
      name = db.StringProperty()
      channel = db.ReferenceProperty(Channel,collection_name="ch_set")

Now I'd like to write a gql query which retrive all profuct of a category. for example:

Product.gql("WHERE channel.category == KEY (:1)", category_selected_key)

Keep in mind that each Channel can change often its category, so I'd like something fast to avoid extra work for cpu

thanks

Answer 1

The App Engine datastore doesn't support joins (which you're implicitly trying to do, here). The easiest way to resolve this would be to add a ReferenceProperty from Product to Category, with the same value as the Product's Channel's Category, thus denormalizing it so you can query simply.

Answer 2

With GQL you can't do a 'nested' query where you filter on the property of a referenced entity like would in SQL (using a join).

Because the reference in your Product entity only stores the key of the referenced Channel entity you would have to do another query first to retrieve the key of the category you're trying to retrieve:

selected_channel = Channel.gql("WHERE category = :1", category_selected_key).get()

category_products = Product.gql("WHERE channel = :1", selected_channel).fetch()