Django - Problem with DB query using foreign key-CodePudding

I'm using Django for a Web app and I have the following data model:

class classi(models.Model):
nome = models.TextField(null=True)
class Meta:
    db_table = 'classi'

class users(models.Model):
    name = models.TextField(null=True)
    email = models.TextField(null=True)
    password = models.TextField(null=True)
    classe = models.ForeignKey(classi, db_column='classe', on_delete=models.CASCADE, null=True)
    class Meta:
        db_table = 'users'

class smartphone(models.Model):
    marca = models.TextField(null=True)
    modello = models.TextField(null=True)
    possessore = models.ForeignKey(users, db_column='possessore', on_delete=models.CASCADE, null=True)
    class Meta:
        db_table = 'smartphone'

My goal is to show, on an HTML page, all classi, and for each classi all users and for each user all smartphone.

How can I implement my view.py and my html file?

The only solution that I found is to scan all table with a for loop and, through a condition, select the row using foreign key:

   {% for c in classi %}
        <p>{{ c.nome }}</p>
        {% for u in users %}
            {% if  u.classe  == c  %}
                <p>{{ u.name }}, {{ u.email }}, {{ u.password }}</p> 
                {% for s in smartphone %}
                    {% if  s.possessore  == u  %}<p>{{ s.marca }}, {{ s.modello }}</p> {% endif %}
                {% endfor %}
            {% endif %}
        {% endfor %}
    {% endfor %}

Is there a better solution?

CodePudding user response：

You can use *prefetch_related for prefetching related object from foreign key. More information: https://docs.djangoproject.com/fr/4.1/ref/models/querysets/#prefetch-related

The view:

def view(request):
    classe = Classi.objects.all().prefetch_related("users_set").prefetch_related("users_set__smartphone_set")
    context["classe"] =) classe
    return render(request, 'template.html', context)

And the html:

{% for c in classe %}
    <p>{{ c.nome }}</p>
    {% for u in c.users_set %}
        <p>{{ u.name }}, {{ u.email }}, {{ u.password }}</p> 
        {% for s in u.smartphone_set %}
            <p>{{ s.marca }}, {{ s.modello }}</p>
        {% endfor %}
    {% endfor %}
{% endfor %}

CodePudding user response：

You could use backwards relations, which will only have items that have a connection

The general format is obj.{model}_set but you can also set it to something different in the models.py with the related_name attr in the ForeignKey field

  {% for c in classi %}
        <p>{{ c.nome }}</p>
        {% for u in c.user_set.all %}
              <p>{{ u.name }}, {{ u.email }}, {{ u.password }}</p> 
              {% for s in u.smartphone_set.all %}
                  <p>{{ s.marca }}, {{ s.modello }}</p>
              {% endfor %}
        {% endfor %}
    {% endfor %}

Or, and this is probably cleaner:
Go the reverse way by looping through the smartphones in the top and just order smartphones by nome in the view with smartphoneList = smartphone.objects.all().order_by('possessore__classe__nome') so they are grouped together

{% for s in smartphoneList %}

  <p>{{ s.possessore.classe.nome }}</p>
  <p>{{ s.possessore.name }}, {{ s.possessore.email }}, {{ s.possessore.password }}</p> 
  <p>{{ s.marca }}, {{ s.modello }}</p>

{% endfor %}

Note after writing this I noticed @Lucas Grugru as already posted the reverse relations way.. From my testing it doesn't require pre-fetching, but it's probably a good idea to do that (I also have no idea). I'm still posting this because I think looping through smartphones might be a cleaner way of doing it