[Django]-Django model joining a one to many relationship for displaying in a template

by

8👍

✅

Assuming you’ve set up your django models to use your current database setup, I’d do the following if I didn’t have a foreign key set up.

contacts = Contact.objects.all()

for contact in contacts:
    contact.attributes = Attribute.objects.filter(contactId=contact.pk) 


return render_to_response("mytemplate.html", {'contacts': contacts })

Alternately, save some queries;

attributes_map = dict( 
    [(attribute.contactId, attribute) for attribute in \
        Attribute.objects.filter(contactId__in=[contact.pk for contact in contacts])]
    )

for contact in contacts:
    contact.attributes = attributes_map.get(contact.pk)

return render_to_response("mytemplate.html", {'contacts': contacts })

Template

<contacts>
{% for contact in contacts %}
    <contact>
        <contactId>{{ contact.pk }}</contactId>
        <firstName>{{ contact.firstName }}</firstName>
        <lastName>{{ contact.lastName }}</lastName>

        {% if contact.attributes %}
            <attributes>
            {% for attribute in contact.attributes %}
                <attribute>
                    <attributeId>{{ attribute.pk }}</attributeId>
                    <attributeValue>{{ attribute.attributeValue }}</attributeValue>
                </attribute>
            {% endfor %}
            </attributes>
        {% endif %}

    </contact>
{% endfor %}
</contacts>
👤Yuji 'Tomita' Tomita

1👍

It sounds like you are in legacy db hell.

As such, you will probably find it difficult to use standard django model stuff like ForeignKey, but you should still strive to keep code like that out of the view code.

So it would probably be better to define some methods on your contact class, something like…

def attributes(self):
    return Attribute.objects.filter(contactId=self.contactId)

then, you can use that in your templates.

<attributes>
    {% for attribute in contact.attributes.all %}
        <attribute>
            ...
        </attribute>
    {% endfor %}
</attribute>

Though, contact.attributes.all in a template might not be ideal(nailing the relationship in the template). but it would be simple to add into your view to stuff something into the context.

👤fatgeekuk

0👍

Yuji’s answer is a lot like Nick Johnson’s prefetch_refprops for App Engine:
http://blog.notdot.net/2010/01/ReferenceProperty-prefetching-in-App-Engine

I found a way to optimize this code:

attributes_map = dict( 
    [(attribute.contactId, attribute) for attribute in \
        Attribute.objects.filter(contactId__in=[contact.pk for contact in contacts])]
    )

Replace it with:

attributes_map = dict(Attribute.objects
    .filter(contactId__in=set([contact.pk for contact in contacts]))
    .values_list('contactId', 'attribute'))

This substitution saves time in the query by:

  • compacting the list of keys into a set(), and
  • retrieving only the two fields used by the dict().

It also takes advantage of the fact that .values_list() returns the list of tuples expected by dict(), but this only works if the first field (i.e., ‘contactId’) is unique.

To extend this solution for multiple foreign keys, create a map for each key field using the code above. Then, inside of this loop:

for contact in contacts:
    contact.attributes = attributes_map.get(contact.pk)

add a line for each foreign key. For example,

attributes_map = dict(Attribute.objects
    .filter(contactId__in=set([contact.pk for contact in contacts]))
    .values_list('contactId', 'attribute'))
names_map = dict(Name.objects
    .filter(nameId__in=set([contact.nameId for contact in contacts]))
    .values_list('nameId', 'name'))

for contact in contacts:
    contact.attributes = attributes_map.get(contact.pk)
    contact.name = names_map.get(contact.nameId)

If you’ve ever had a template generate lots of extra SQL calls for each row of a result set (in order to look up each foreign key’s value), this approach will make a huge savings by pre-loading all the data before sending it to the template.

👤Brent Washburne

Leave a comment