CODEWITHSUNDEEP
google-cloud-datastoreapp-engine-ndb
user2744119
user2744119

Reputation: 153

How do I perform an NDB query on the properties of models that have a parent-child relationship?

So I have a root model that looks like this:

class Contact(ndb.Model):
    first_name= ndb.StringProperty()
    last_name= ndb.StringProperty()
    age = ndb.IntegerProperty()

and a child model that looks like this:

class Address(ndb.Model)
    address_type=ndb.StringProperty(choices=['Home','Office','School'],default='Home')
    street = ndb.StringProperty()
    city = ndb.StringProperty()
    state = ndb.StringProperty()

I want to be able to perform a query similar to this: Select first_name, last_name, street, city, state WHERE contact.age > 25 and address.city = 'Miami' and address_type = 'School'

I know I can perform searches more easily if I were to setup the addresses as a structured property within the contact model, but I don't like using Structured Properties because they don't have their own keys, thus making entity maintenance more challenging.

I tried doing a search for contacts first and then feeding the resulting keys into a WHERE IN clause but it didn't work, example:

query1 = Contact.query(Contact.age>25).iter(keys_only = True)
query2 = Address.query(Address.city=='Miami', Address.address_type=='School',Address.ancestor.IN(query1))

Any ideas as to how to go about this would be appreciated.

Upvotes: 1

Views: 426

Answers (2)

user2744119
user2744119

Reputation: 153

OK so it looks like my original idea of filtering one query by passing in the keys of another will work. The problem is that you can't perform a WHERE-IN clause against an ancestor property so you have to store the parent key as a standard ndb.KeyProperty() inside of the child entity, then perform the WHERE-IN clause against that KeyProperty field.

Here's an example that will work directly from the interactive console in the Appengine SDK:

from google.appengine.ext import ndb

class Contact(ndb.Model):
    first_name= ndb.StringProperty()
    last_name= ndb.StringProperty()
    age = ndb.IntegerProperty()


class Address(ndb.Model):
    address_type=ndb.StringProperty(choices=['Home','Office','School'],default='Home')
    street = ndb.StringProperty()
    city = ndb.StringProperty()
    state = ndb.StringProperty()
    contact = ndb.KeyProperty()


# Contact 1
contact1 = Contact(first_name='Homer', last_name='Simpson', age=45)
contact1_result = contact1.put()
contact1_address1 = Address(address_type='Home',street='742 Evergreen Terrace', city='Springfield', state='Illinois', contact=contact1_result, parent=contact1_result)
contact1_address1.put()
contact1_address2 = Address(address_type='Office',street=' 1 Industry Row', city='Springfield', state='Illinois', contact=contact1_result, parent=contact1_result)
contact1_address2.put()

# Contact 2
contact2 = Contact(first_name='Peter', last_name='Griffan', age=42)
contact2_result = contact2.put()
contact2_address1 = Address(address_type='Home',street='31 Spooner Street', city='Quahog', state='Rhode Island', contact=contact2_result, parent=contact2_result)
contact2_address1.put()

# This gets the keys of all the contacts that are over the age of 25
qry1 = Contact.query(Contact.age>25).fetch(keys_only=True)

# This query gets all addresses of type 'Home' where the contacts are in the result set of qry1
qry2 = Address.query(Address.address_type=='Home').filter(Address.contact.IN(qry1))

for item in qry2:
    print 'Contact: %s,%s,%s,%s'% (item.contact.get().first_name, item.contact.get().last_name, item.address_type, item.street)

This will render a result that looks kinda like this:

Contact: Peter,Griffan,Home,31 Spooner Street
Contact: Homer,Simpson,Home,742 Evergreen Terrace

Upvotes: 1

Brent Washburne
Brent Washburne

Reputation: 13158

Can you use an Ancestor query?

query1 = Contact.query(Contact.age>25).iter(keys_only = True)
for contact in query1:
    query2 = Address.query(Address.city=='Miami',
                           Address.address_type=='School',
                           ancestor=contact)

If that's not efficient enough, how about filtering the addresses?

query1 = Contact.query(Contact.age>25).iter(keys_only = True)
contacts = set(query1)
query2 = Address.query(Address.city=='Miami', Address.address_type=='School')
addresses = [address for address in query2 if address.key.parent() in contacts]

Upvotes: 0

Related Questions