How to join querysets from different models for use in template for loop

Using Django
1

I have two models that point to a third model using foreign keys, as follows:

from django.db import models

class UID(models.Model):
note = models.TextField(blank=True, null=True)

class Linked(models.Model):
uid = models.ForeignKey(UID, to_field=‘id’, on_delete=models.CASCADE, related_name=‘linked1’)

class AnotherLinked(models.Model):
uid = models.ForeignKey(UID, to_field=‘id’, on_delete=models.CASCADE, related_name=‘linked2’)

I wish to join the results of querysets from each model on the foreign key and render the results as a list in a template. My view is as follows:

from django.shortcuts import render
from .models import UID, Linked, AnotherLinked

def joined_list(request):
uid = UID.objects.all()
linked = Linked.objects.all()
another_linked = AnotherLinked.objects.all()
context = {‘uid’: uid, ‘linked’: linked, ‘another_linked’: another_linked,}
return(render(request, ‘links/joined_list.html’, context))

My template is as follows:

{% for i in uid %}
{{ i.id }}
{% endfor %}

2

Winging this off-the-cuff - no idea how close to accurate my syntax is going to be - hopefully this will give you some ideas to work from:

First, you don’t need to fetch all of the related objects in separate queries. The Django ORM already knows what the relationships are among your models.

All you really need is the first query:

uid = UID.objects.all()

Note: you can use the prefetch_related method on your query to minimize the number of queries performed to retrieve data for your page.

for example:

uid = UID.objects.all().prefetch_related('linked1', 'linked2')

Then, you can take advantage of the RelatedManager facility to access the list of items that are related to your UID object.

Then, in your termplate, you could do something like this:

{% for i in uid %}
    {{i.id}} {{i.note}}
    {% for j in i.linked1_set.all %}
        {{j.whatever field to be included}}
    {% endfor %}
    {% for j in i.linked2_set.all %}
        {{j.whatever field to be included}}
    {% endfor %}
{& endfor %}

(Obviously you’ll want to format it a lot nicer than this, but hopefully you get the idea.)

3

Thanks Ken that’s precisely what I needed to know!

4

Thanks Ken, that was extremely helpful. And so simple!

I had been trying ‘select_related’ and was returned a ‘FieldError’, and had assumed ‘prefetch_related’ would do the same. For the record, the below worked for me:

def joined_list(request):
    uid = UID.objects.all()
    char1 = Char1.objects.all()
    char2 = Char2.objects.all()
    join = UID.objects.all().prefetch_related('char1','char2')
    context = {'uid': uid, 'char1': char1, 'char2': char2, 'join': join,}
    return(render(request, 'links/joined_list.html', context))

I couldn’t make the ‘_set.all’ call work as you suggested but after some investigation found that I could get the data from the ‘values’ attribute. If anyone has a comment on whether that looks like an appropriate useage I’d be interested to hear.

<div>
{% for i in join %}
{{ i.id }} {{ i.note }}
    {% for j in i.char1.values %}
        {{ j.id }} {{ j.attr1 }}
    {% endfor %}
{% endfor %}
</div>
5

You’re still doing more work than what you need to do. The purpose of the join is to prevent you from needing to do the separate queries on the UID, Char1 and Char2 models. (You’ve changed some names from your first example, so I’m not sure how well the parallel holds, but if you post your models.py for the UID, Char1 and Char2 objects, I can give you some more specific assistance.)

In fact, notice in your template that you’re not referring to the uid, char1, or char2 collections from your context at all. You’re only using the join entry - unless there’s more to this template than what you’re showing. But as-is, you can remove those first three queries and the references to them in your context.

Ken

6

Thanks for your continued help Ken.

OK I think I’ve streamlined things but I’m not sure if it’s what you mean. Here’s the current iteration of my working example:

The models.

from django.db import models

class UID(models.Model):
    note = models.TextField(blank=True, null=True)

class Char1(models.Model):
    uid = models.ForeignKey(UID, to_field='id', on_delete=models.CASCADE, related_name='char1')
    attr1 = models.TextField(blank=True, null=True)
    attr2 = models.TextField(blank=True, null=True)

class Char2(models.Model):
    uid = models.ForeignKey(UID, to_field='id', on_delete=models.CASCADE, related_name='char2')
    attr1 = models.TextField(blank=True, null=True)
    attr2 = models.TextField(blank=True, null=True)

The view.

def joined_list(request):
    join = UID.objects.all().prefetch_related('char1','char2')
    context = {'join': join,}
    return(render(request, 'links/joined_list.html', context))

The template (with some nicer table formatting).

<div>
  <table>
  {% for i in join %}
    {% for j in i.char1.values %}
      {% for k in i.char2.values %}
        <tr>
          <td>{{ i.id }}</td> 
          <td>{{ i.note }}</td> 
          <td>{{ j.attr1 }}</td> 
          <td>{{ k.attr2 }}</td>
        </tr>
      {% endfor %}
    {% endfor %}
  {% endfor %}
  </table>
</div>

That’s got me what I need in regards to presenting related information from separate tables. If it can be expressed any simpler than the above then please let me know.

7

If that’s displaying what you want displayed, then I think you’ve got it!

(It just seems unusual to me that for each “j” (i.char1), you’re going to want to display all the same “k” (i.char2). So if you have two entries in char1 and three entries in char2, all related to the same UID, then you’ll get 6 rows displayed, with each char1.attr1 in the same row with each char2.attr2 )