Is there an elegant way to model and represent a one to many inside a many to many relationship on Django?

Question

I am a bit blocked with something it does not seem to be very complicated. Here my model:

class Team(models.Model):
    name = models.CharField('team name', max_length=200, unique=True)

class QualityStream(models.Model):
    name = models.CharField('quality stream name', max_length=200, unique=True)
    team = models.ManyToManyField(Team)

class Milestone(models.Model):
    name = models.CharField('milestone name', max_length=200)
    quality_stream = models.ForeignKey(QualityStream)
    team = models.ForeignKey(Team)

As you can see, a Team can have several pre-defined Quality Streams, and several Milestones to be achieved that indeed belongs to a Quality Stream.

Here my view:

<p>{{team.name}}</p>

{% for stream in team.qualitystream_set.all %}
    <p>{{stream.name}}</p>
    {% for milestone in team.milestone_set.all %}

        {% if milestone.quality_stream.id == stream.id  %}
           <p>{{milestone.name}}</p>               
        {% endif %}

    {% endfor %}    
{% endfor %}

The idea is to display the Team, The quality streams associated to the team and each milestone grouped by quality stream:

Team

  Quality Stream 1
     Milestone 1 for Quality Stream 1
     Milestone 2 for Quality Stream 1
  Quality Stream 2
     Milestone 1 for Quality Stream 2
     Milestone 2 for Quality Stream 2

The code works nicely, but I do not feel too comfortable by looping over all the milestones for each Quality stream. I guess there must be a better way to achieve this. Any ideas?

Answer 1

Whenever you think you have to do multiple descending loops like you have, think about whether or not you can approach the problem from the opposite end.

As @Pathetique pointed out, the following link shows how to minimise the number of queries you're performing:

https://docs.djangoproject.com/en/1.5/topics/db/optimization/#retrieve-everything-at-once-if-you-know-you-will-need-it

The highlight in that link is select_related which only works on ForeignKeys, and prefetch_related which only works on ManyToManyFields.

Since you were approaching this problem from the point of view of "I have a set of teams, now I want to show all the data associated with those teams", you are unable to use any of the optimisations available since Team does not have ForeignKeys or ManyToManyFields.

Instead, you can approach the problem like so "I want to show all Milestones, grouped by team and then by quality streams". Since your Milestone class has access to all the data you require, it makes the queryset very easy to construct, only generating a single query.

def my_view(request):
    queryset = Milestone.objects.select_related(
        'team', 'quality_stream'
    ).order_by(
        'team__name', 'quality_stream__name'
    ) # a single query to fetch all teams + streams + milestones

    return render_to_response('/your/template.html', 
        { 'milestones':queryset },
        context_instance=RequestContext(request)
    )

Now, your template is going to break the way you have it constructed. This is where the definition of the problem I mentioned before comes in. You have all your milestones, but you want to group them by team and then by quality stream. The ordering of our queryset helps us now, since we can iterate through all the milestones, and check whether we're up to a new team or a new quality stream.

# your template
{% for milestone in milestones %}
    {% ifchanged %} <p> {{ milestone.team.name }} </p> {% endifchanged %}
    {% ifchanged %} <p> {{ milestone.quality_stream.name }} </p> {% endifchanged %}
    <p> {{ milestone.name }} </p>
{% endfor %}

The above template uses the ifchanged template tags, which seems designed exactly for this purpose.

Answer 2

There's nothing wrong with what you are doing, but there may be some database optimization by doing your queries in a view, particularly using select_related():

https://docs.djangoproject.com/en/1.5/topics/db/optimization/#retrieve-everything-at-once-if-you-know-you-will-need-it

Also, if your models had a bunch of fields but you only needed some you could use only() or defer(). I've seen substantial performance gains using defer to avoid large fields. only() is just the inverse of defer():

https://docs.djangoproject.com/en/1.5/ref/models/querysets/#defer

Your models don't seem too complex so I think just using select_related() will work for you. I highly recommend using the Django debug toolbar for making optimization decisions. The debug toolbar would show you the actual select statements and the time it took for each of them. It's good to know if there's really a problem before spending too much time optimizing.

Hope this helps