Following relationships backward using querysets

Using Django Using the ORM
1

I have 3 models with relationships as follows:
Event – 1>Many – Race – 1>Many RaceResult

If I know my Event.id I need to generate a resultset of all the related Race records and all the related RaceResult records (NB not all Race records may have RaceResult but this shouldn’t stop Race records being included).

I’m getting stuck with following the relationship backward as it only appears to accept a single row and not a query set.

This works in the shell: Race.objects.get(id=2).result_for_race.all().values()

This does not work but is closer to what I am looking to achieve: Race.objects.filter(event.id=1).result_for_race.all()

However the backward relationship Manager only appears to take a single value and not a set of objects.

I have read the documentation and tutorial but it lacks enough detail and examples to understand what is possible and what can be passed in each direction of a relationship and how they are referenced.

Advice on how to structure this query would be welcome.

Many thanks

models.py:
class Event(models.Model):
    name = models.CharField(max_length=255, unique_for_year='start_date')
    start_date = models.DateField()
    end_date = models.DateField()
    venue = models.CharField(max_length=255)
    host = models.ForeignKey(
            'Club',
            related_name='events',
            on_delete=models.PROTECT
            )
    theme = models.CharField(max_length=30, blank=True, null=True)  # hex colour
    max_entries = models.IntegerField()

    def __str__(self):
        return self.name + ' ' + str(self.start_date.year)

    class Meta:
        constraints = [
                models.CheckConstraint(
                    check=Q(end_date__gte=F('start_date')),
                    name='event_ch_dates'
                    ),
                ]

class Race(models.Model):
    event = models.ForeignKey(
            'Event',
            related_name = 'race_in_event',
            on_delete=models.PROTECT,
        )
    stage_number = models.IntegerField()
    schedule = models.ForeignKey(
            'Schedule',
            related_name='race_schedule',
            on_delete=models.PROTECT,
        )
    race_number = models.IntegerField()
    sort_order = models.IntegerField()
    group = models.IntegerField()
    data = models.TextField()

class RaceResult(models.Model):
    race = models.ForeignKey(
           'Race',
           related_name = 'result_for_race',
           on_delete = models.PROTECT,
        )
    team1 = models.ForeignKey(
            'Team',
            related_name='team1_races',
            on_delete=models.PROTECT
            )
    team2 = models.ForeignKey(
            'Team',
            related_name='team2_races',
            on_delete=models.PROTECT
            )
    winning_team = models.ForeignKey(
            'Team',
            related_name='won_races',
            on_delete=models.PROTECT
            )
    losing_team = models.ForeignKey(
            'Team',
            related_name='lost_races',
            on_delete=models.PROTECT
            )
    team1_score = models.IntegerField()
    team2_score = models.IntegerField()
    finishing_order = models.TextField()
    notes = models.TextField(blank=True)

views.py
def AllRacesView4(request, event_id):
    render_classes = [TemplateHTMLRenderer]
    resultdata = Race.objects.get(id=1).result_for_race.all()
    template = loader.get_template('ksail_backend/race_list_all.html')
    context = {
        'resultdata': resultdata,
    }
    return HttpResponse(template.render(context, request))
2

You are correct - this needs to be done in two parts.

First you query for Race, then you iterate over Race to retrieve the RaceResult for each race.

To reduce the number of queries involved, you would use the prefetch_related function on the first query. (e.g. Race.objects.filter(event=event).prefetch_related('result_for_race'))

3

Thanks for this help.
I can see with the debugger that this is running the right set of SQL queries but I’m unsure of how to access all the fields/queries in the template.
Fields in the tables Race and Event are available in the queryset, but not those in RaceResult

The query in a single queryset I’m looking to achieve is:
In Race where event.id=1, display all races/rows (with related event.name). Where exist in RaceResult, display RaceResult.finishing_order. (not all Races have a RaceResult but still should be in the queryset)

My view is now:

def AllRacesView4(request, event_id):
    render_classes = [TemplateHTMLRenderer]
    resultdata = Race.objects.filter(event_id=event_id).prefetch_related('result_for_race').all()
    template = loader.get_template('ksail_backend/race_list_all.html')
    context = {
        'resultdata': resultdata,
    }
    return HttpResponse(template.render(context, request))

In my template I have:

{% for data in resultdata %}
                <div class="row">
                    <div class="col">Race_id: {{ data.id }}</div>
                    <div class="col">Event_name: {{ data.event.name }}</div>
                    <div class="col">RaceResult_id: {{ data.RaceResult.id }}</div>
                    <div class="col">RaceResult_finishing_order{{ data.RaceResult.finishing_order }}</div> 
                </div> 
                {% endfor %}

Hopefully this makes sense.

4

As a 1-to-many relationship, you may have multiple RaceResult instances for each Race. This means that if you want to display all the RaceResult for each Race, then you would want to have a nested loop within your template to iterate over result_for_race within each data.

5

Thanks for the fast response.
I understand the idea of the nested loop to iterate, however I’m not sure where this should go or how to reference the RaceResult table/fields.

If it went in the template then I don’t know what the queryset would be called as I have only passed in resultdata. I’d be grateful if you could provide an example.

Many thanks

6

There’s no separate queryset involved here - all the data you need is accessible through the objects you’ve already retrieved.

You are currently iterating over resultdata. Each time through the loop, you’re setting a variable named data, which would be an instance of Race.

If you have an instance of Race named data, then data.result_for_race is the set of RaceResult for that race. You would iterate over that, giving you one instance of RaceResult each time through the loop.

7

Perhaps I’m misunderstanding something here as I’m still not getting any data from the RaceResult table.

My template with a nested loop now looks like:

                {% for data in resultdata %}
                <div class="row">
                    <div class="col">{{ data.event.name }}</div>
                    <div class="col">{{ data.stage_number }}</div>
                    <div class="col">{{ data.schedule }}</div>
                    <div class="col">{{ data.race_number }}</div>
                        {% for results in result_for_race %}
                        <div class="col">{{ results.team1_score }}</div>
                        <div class="col">{{ results.team2_score }}</div>
                        <div class="col">{{ results.finishing_order }}</div> 
                        {% endfor %}
                 </div>
                {% endfor %}

I’d be grateful for either an example of how to do this iteration with my code, or below is a generic exemplar of what I’m trying to achieve.

{% for parent in query %}
  {{ parent.field1 }}
  {{ parent.field2 }}
  {% for child in related_one_parent_to_many_child %}
    {{ child.field1 }}
    {{ child.field2 }}
  {% endfor %}
{% endfor %}

The parent table may have data

field1    field 2    fk_field3
1           a              1
2           a              1 
3           b              2
4           c

The child table may have data

field3    field4
1           x
2           y
3           z

The anticipated output should be

field1    field2    field3    field4
1          a           1           x
2          a           1           x
3          b           2           y
4          c
8

You’re close, but not quite there.

What you have:

What I wrote:

Just like in the “outer loop” you reference fields like data.schedule or data.race_number, you need to specify which result_for_race to access.

9

I give up, you are going to have to tell me the exact answer.

I have tried

{% for results in result_for_race %}
{% for results in result_for_race.all %}
{% for results in data.result_for_race %}
{% for results in data.result_for_race.all %}

None of which return anything.

Please tell me the answer and then I’ll be able to understand how this works and be able to repeat for other cases. At the moment I’m just floundering around hoping for some help with either an answer or being directed to the right part of the documentation which gives not only the syntax but a worked example. I’ve spent 2 days reading documentation, searching for help and not getting anywhere.

Thank you for your time.

10

That should be the right one. If that’s not working, then we need to take a step back and look at this in its entirety.

Please post the current template fragment (including both loops and what you’re rendering within them) and your current view.

11

Data is an attribute in the Race class. Wouldn’t the use of the data attribute in a loop tweak the system out? I would go for something like the below:

Methodology:
loop_variable.class.class.attribute
or
loop_variable.class.attribute

Application:
{ for x in resultdata %}
{{x.event}}
{{x.raceresult.result_for_race}}
{%endfor%}

  • You are using FK’s aka One to Many. So, I thought, you don’t want to use “prefetch_related`” because that is for ManytoMany relationships.
  • “select_related” is for FK’s and one to one’s, so this might work. Although, I don’t think you need either. Just use the class.class queries in the HTML to access items linked through FK’s. Examples above.