Django making recursive calls on relationship

Question

I have the following model on Django 1.6.5

class Location(models.Model):
    parent = models.ForeignKey('Location', blank=True, null=True)
    name = models.CharField(max_length=50)

    class Meta:
        db_table = 'locations'
        ordering = ('parent__name', 'name')

    def __unicode__(self, ):
        if self.parent!= None:
            return self.name + " (" + self.parent.name +  ")"
        else:
            return self.name

And I'm listing these locations in a dropdown, so I want to show the name + (parent.name if available)

Any idea hwo to do this without having to do thousands of calls?

You probably want to look into something like [django-mptt](http://django-mptt.github.io/django-mptt/) which allows you to do these queries very efficiently. — Daniel Roseman, Jan 15 '15 at 13:37
Come on, there must some other way that using a third party library. This is the most common thing in the world. — Fabiot, Jan 15 '15 at 13:52
There is no way to do that if you stick with hierarchical storage of tree data in sql. You should look into Nested Sets http://en.wikipedia.org/wiki/Nested_set_model of Materialised Path models. django-mptt does both as far as I remember — Kirill Zaitsev, Jan 15 '15 at 14:00
What a weird reaction. This is hardly the "most common thing in the world": recursive relationships are a specialized use case, and we have specialized libraries to deal with querying them efficiently. The general use case for avoiding cascading database calls is to use `select_related` on the query that originally gets the location list, though. — Daniel Roseman, Jan 15 '15 at 14:34
I will probably do so, just write a custom join for this as it doesn't really need pagination or anything. So it should be straight away. In any case, I'll do a POC with django-mptt... but if somebody has a simpler approach, I am still listening. — Fabiot, Jan 15 '15 at 14:43
`django-mptt` will not help here. SQL join will no help too, You have to load all locations into the some sort of cache and use it while displaying of dropdown. Do you render this dropdown in HTML or use standard django form widgets? — catavaran, Jan 16 '15 at 07:55

score 1 · Answer 1 · answered Jan 16 '15 at 14:58

Finally I did this:

In the forms.py I created a method:

def get_locations_with_parents():
    location_list = [(0,'Select One ...')]
    locations = Position.objects.raw('select l.id, l.name, p.name as parent from locations l left join locations p on l.parent_id = p.id order by parent, l.name')
    for l in locations:
        if l.parent:
            location = (int(l.id), l.name + " (" + l.parent + ")")
        else:
            location = (int(l.id), l.name)

        location_list.append(location)

    return tuple(location_list)

and then in the form I use locations = forms.ChoiceField(choices=get_locations_with_parents(), validators=[validate_empty])

It does the trick and it doesn't do 2000 queries to the DB anymore. There are some validators and clean and so... but does not really relevant to the solution.

score 0 · Accepted Answer · edited May 23 '17 at 10:33

I may be misunderstanding your question, but I think I have a similar situation. In the dropdown you want the object to be labelled with the name of the location. If there is a parent for that location, you would like to show the name of that parent as well within parentheses following the location name.

You can accomplish this by overriding the __init__ method of your ModelForm:

def __init__(self, *args, **kwargs):
    def new_label_from_instance(self, obj):
        try:
            #see if there is a parent or not
            p = obj.parent 
        except:   
            #no parent; use the location's default label
            rep = obj.description
            return rep
        #if we find a location's parent, p=parent label and d=location label
        d = obj.description
        rep = "%s (%s)" % (d, p)
        return rep

    super(PublisherCreateTSCForm, self).__init__(*args, **kwargs)
    funcType = type(self.fields['location'].label_from_instance)
    self.fields['location'].label_from_instance = funcType(new_label_from_instance, self.fields['location'], forms.models.ChoiceField)

You should know the consequences of doing this before you do it. Check out this old question:

Django CheckboxSelectMultiple override 'choices' from ModelForm

And the docs related to label_from_instance here (bottom of the linked section): https://docs.djangoproject.com/en/1.7/ref/forms/fields/#modelchoicefield

Django making recursive calls on relationship

2 Answers2