views:

457

answers:

1

Is there an easy way to fetch the ManyToMany objects from a query that returns more than one object? The way I am doing it now doesn't feel as sexy as I would like it to. Here is how I am doing it now in my view:

contacts = Contact.objects.all()
# Use Custom Manager Method to Fetch Each Contacts Phone Numbers
contacts = PhoneNumber.objects.inject(contacts)

My Models:

class PhoneNumber(models.Model):
    number = models.CharField()
    type = models.CharField()

    # My Custom Manager
    objects = PhoneNumberManager()

class Contact(models.Model):
    name = models.CharField()
    numbers = models.ManyToManyField(PhoneNumber, through='ContactPhoneNumbers')

class ContactPhoneNumbers(models.Model):
    number = models.ForeignKey(PhoneNumber)
    contact = models.ForeignKey(Contact)
    ext = models.CharField()

My Custom Manager:

class PhoneNumberManager(models.Manager):
    def inject(self, contacts):
        contact_ids = ','.join([str(item.id) for item in contacts])
        cursor = connection.cursor()

        cursor.execute("""
            SELECT l.contact_id, l.ext, p.number, p.type
            FROM svcontact_contactphonenumbers l, svcontact_phonenumber p
            WHERE p.id = l.number_id AND l.contact_id IN(%s)
            """ % contact_ids)

        result = {}
        for row in cursor.fetchall():
            id = str(row[0])
            if not id in result:
                result[id] = []

            result[id].append({
                 'ext': row[1],
                 'number': row[2],
                 'type': row[3]
            })

        for contact in contacts:
            id = str(contact.id)
            if id in result:
                contact.phonenumbers = result[id]

        return contacts
+2  A: 

There are a couple things you can do to find sexiness here :-)

Django does not have any OOTB way to inject the properties of the through table into your Contact instance. A M2M table with extra data is a SQL concept, so Django wouldn't try to fight the relations, nor guess what should happen in the event of namespace collision, etc... . In fact, I'd go so far as to say that you probably do not want to inject arbitrary model properties onto your Contact object... if you find yourself needing to do that, then it's probably a sign you should revise your model definition.

Instead, Django provides convenient ways to access the relation seamlessly, both in queries and for data retrieval, all the while preserving the integrity of the entities. In this case, you'll find that your Contact object offers a contactphonenumbers_set property that you can use to access the through data:

>>> c = Contact.objects.get(id=1)
>>> c.contactphonenumbers_set.all()
# Would produce a list of ContactPhoneNumbers objects for that contact

This means, in your case, to iterate of all contact phone numbers (for example) you would:

for contact in Contact.objects.all():
    for phone in contact.contactphonenumbers_set.all():
        print phone.number.number, phone.number.type, phone.ext

If you really, really, really want to do the injection for some reason, you'll see you can do that using the 3-line code sample immediately above: just change the print statements into assignment statements.


On a separate note, just for future reference, you could have written your inject function without SQL statements. In Django, the through table is itself a model, so you can query it directly:

def inject(self, contacts):
    contact_phone_numbers = ContactPhoneNumbers.objects.\
                            filter(contact__in=contacts)
    # And then do the result construction... 
    # - use contact_phone_number.number.phone to get the phone and ext
    # - use contact_phone_number.contact to get the contact instance
Jarret Hardie
Hmm, I may be doing this wrong. running: cons = Contact.objects.all()[0:50] and then num = ContactPhoneNumbers.objects.filter(contact__in=cons) runs over 150 database queries.
Matt
Ahh hah! contact_phone_numbers = ContactPhoneNumbers . objects.select_related() .filter(contact__in=contacts)That is very sexy.
Matt
I wonder if it is smart enough to not pull in the contacts again.. I will have to inspect the query in a little while.
Matt
Cool! I forgot to mention select_related(), so glad you've tried it.
Jarret Hardie
Django can cache querysets, but not object pulls (http://docs.djangoproject.com/en/dev/topics/db/queries/#id5). In other words, if you define a queryset and assign it to a variable, it will execute it only once. I won't, however, cache object retrieval across 2 totally different querysets.
Jarret Hardie