SQL query with 3 tables

[mfreitas64]

Hi,

I need to get information from 3 different tables but I don’t know how to do it.

Here are my models:

class Alunos(models.Model):

    class Meta:
        verbose_name_plural = 'Alunos'

    nome = models.CharField(max_length=50, help_text="Nome do aluno", verbose_name="Nome:")
    dtnasc = models.DateField(help_text="Data de Nascimento", verbose_name="Data de Nascimento:")
    alunoid = models.CharField(max_length=4, help_text="ID interno do aluno", verbose_name="ID Aluno:")
    classeid = models.ForeignKey(Turmas, on_delete=models.CASCADE, verbose_name="Classe:")
    educaid = models.ForeignKey(Educa, on_delete=models.CASCADE, verbose_name="Encarregado de Educação:")
    moradaid = models.ForeignKey(Morada, on_delete=models.CASCADE, verbose_name="Morada:")
    cpostalid = models.ForeignKey(CodPostal, on_delete=models.CASCADE, verbose_name="Código Postal:")
    ativo = models.BooleanField(default=True)
    notas = models.TextField(help_text="Notas importantes")

    def __str__(self):
        return self.nome

class Turmas(models.Model):

    class Meta:
        verbose_name_plural = 'Turmas'

    turma_name = models.CharField(max_length=50, help_text="Nome da classe", verbose_name="Classe:")
    weekdays = models.CharField(max_length=7, choices=weekdays_choices, help_text="Dias da classe", verbose_name="Dias:")
    times = models.CharField(max_length=20, choices=class_times, help_text="Horário da classe", verbose_name="Horas:")
    profid = models.ForeignKey(Professores, on_delete=models.CASCADE, verbose_name="Professor:")
    anoid = models.ForeignKey(Ano, on_delete=models.CASCADE, verbose_name="Ano:")

    def __str__(self):
        return self.turma_name

class Educa(models.Model):

    class Meta:
        verbose_name_plural = 'Encarregados de Educação'

    nome_encarregado = models.CharField(max_length=150, help_text="Nome do encarregado de educação")
    nif = models.CharField(max_length=9, help_text="NIF do encarregado de educação ou aluno")
    email = models.EmailField(max_length=100, help_text="e-mail do encarregado de educação")
    telefone = models.CharField(max_length=9, help_text="Telefone do encarregado de educação")
    pagamento = models.CharField(max_length=11, choices=pagamentos_choices, help_text="Último pagamento")

    def __str__(self):
        return self.nome_encarregado

This is the SQL I need to transform into ORM in the view

select c.turma_name, a.nome, a.alunoid, a.educaid_id, b.nif, b.email, b.telefone
from schooladmin_alunos a, schooladmin_turmas c, schooladmin_educa b
where a.id = c.id and a.id = b.id

Any help would be much appreciated.

Thanks in advance

[KenWhitesell]

Since you have a foreign-key relationship defined, you write your query on Alunos and reference the related objects through the classeid and educaid id fields.

As a performance enhancement (it is not required), you can use the select_related function in the query to prefetch those objects.

See the docs and examples at Many-to-one relationships | Django documentation | Django for more detailed information.

[mfreitas64]

Here is what’s happening.

views.py

class TurmasDetailView(ListView):
    model = Alunos
    template_name = 'schooladmin/detalhe_turmas.html'

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        context ['sessao'] = Alunos.objects.all().select_related('educaid').select_related('classeid').get(pk=self.kwargs['pk'])
        return context

Template

{% block content %}
    <div class="row">
        <h4> {{ sessao.turma_name }} - {{ sessao.weekdays }} - {{ sessao.times }}</h4>

    </div>
    <br>
    <a class="btn btn-outline-primary btn-sm" href="{% url 'school:turmas' %}">Voltar às Turmas</a>
    <br>
    <br>
    <div class="row justify-content-center">
        {% for a in sessao %}
            <div class="col-md-3 book margens">
                <p>{{ a.nome }} - {{ a.alunoid }} - {{ a.educaid }} - {{ a.email }}</p>
            </div>
        {% endfor %}
    </div>
{% endblock content %}

the error message

Error during template rendering
In template /Users/demiguelfreitas/django_lu/schooladmin/templates/schooladmin/detalhe_turmas.html, error at line 14

'Alunos' object is not iterable

What am I doing wrong?

[KenWhitesell]

@mfreitas64

context ['sessao'] = Alunos.objects.all().select_related('educaid').select_related('classeid').get(pk=self.kwargs['pk'])

The get method doesn’t return a queryset - it returns the single instance of the object. Therefore, it doesn’t make any sense to try and iterate over it at:

@mfreitas64

{% for a in sessao %}

So the for and endfor can be removed from the template, and the template fragment inside that for loop should directly reference sessao and not a.

Side note: That query can be simplified to:
context ['sessao'] = Alunos.objects.select_related('educaid', 'classeid').get(pk=self.kwargs['pk'])

[mfreitas64]

Thank you Ken, I’ve made the changes but I’m not getting the Educa and Turmas classes data on the template, nothing appears, only the data from Alunos is displayed.

{% extends "schooladmin/base.html" %}
{% load static %}

{% block content %}
    <div class="row">
        <h4> {{ sessao.turma_name }} - {{ sessao.weekdays }} - {{ sessao.times }}</h4>

    </div>
    <br>
    <a class="btn btn-outline-primary btn-sm" href="{% url 'school:turmas' %}">Voltar às Turmas</a>
    <br>
    <br>
    <div class="row justify-content-center">

            <div class="col-md-3 book margens">
                <p>{{ sessao.nome }} - {{ sessao.alunoid }} - {{ sessao.educaid }} - {{ sessao.email }}</p>
            </div>

    </div>
{% endblock content %}

[KenWhitesell]

On your first line:

@mfreitas64

<h4> {{ sessao.turma_name }} - {{ sessao.weekdays }} - {{ sessao.times }}</h4>

You don’t have those attributes on the Alunos object. Those attributes are on the Turmas object.

Your reference to the Turmas object that is related to the Alunos object is the classeid field in the Alunos object. Since your Alunos object in the template is named sessao, then your reference to the related Turmas object is sessao.classeid.

That means that in your template, your reference to the turma_name field would be sessao.classeid.turma_name.

[mfreitas64]

Ken, thank you, it’s working.
Best regards

[mfreitas64]

I found another problem. I have 2 Alunos on the same Turmas but the template only displays one. How can I make it to display all Alunos that are in the same Turmas? I thought the ‘select_related’ would return all the Alunos from the same Turmas.

[KenWhitesell]

You are issuing a query for a single Alunos:

@KenWhitesell

context [‘sessao’] = Alunos.objects.select_related(‘educaid’, ‘classeid’).get(pk=self.kwargs[‘pk’])

You will get the one Alunos where the primary key matches the parameter passed in the url.

[mfreitas64]

But I need to get all the Alunos with the same classeid_id, as in the SQL query and I’m only getting the first one. The vale that is passed on the url is 2

[KenWhitesell]

That means your query is looking for the wrong field. You would want to search on the classeid field and not pk.

That would also mean that your template would be listing a queryset and not a single instance, and so your template would iterate over that queryset.

[mfreitas64]

I’ve changed my view to

class TurmasDetailView(ListView):
    model = Alunos
    template_name = 'schooladmin/detalhe_turmas.html'
    context_object_name = 'sessao'

    def get_queryset(self):
        return Alunos.objects.select_related('educaid','classeid').get(classeid=self.kwargs['pk'])

and the template like

{% block content %}
    <div class="row">
        <h4> {{ sessao.classeid.turma_name }} - {{ sessao.classeid.weekdays }} - {{ sessao.classeid.times }}</h4>

    </div>
    <br>
    <a class="btn btn-outline-primary btn-sm" href="{% url 'school:turmas' %}">Voltar às Turmas</a>
    <br>
    <br>
    <div class="row justify-content-center">

            <div class="col-md-3 book margens">
                {% for a in sessao %}
                <ul>
                    <li>{{ a.nome }} - {{ a.alunoid }} - {{ a.educaid }} - {{ a.educaid.email }}</li>
                </ul>
                {% endfor %}
            </div>

    </div>
{% endblock content %}

and I’m getting this error message

[KenWhitesell]

I’m curious here - have you worked through the Official Django Tutorial? If not, you should. If you have, I suggest you review the work you would have done on step 2.

[mfreitas64]

I have done that, but it does not help. What am I missing here?

[KenWhitesell]

The understanding of how to write queries and the data returned from them explained starting with the Playing with the API at Writing your first Django app, part 2 | Django documentation | Django, followed by Making queries | Django documentation | Django and finally the QuerySet API reference | Django documentation | Django

Particularly, you want to focus on understanding the different data types involved and what functions return.

[mfreitas64]

Thank you for your suggestion but the documentation is not that good, it just teach you the very basic. There is not one example in these docs with 3 tables and how to relate them and use the data on a template. I can write a very simple SQL query, has the one I posted initIally, that gives me what I need but to translate that into django languague it proving very difficult. And the help is not to much.
Maybe you could use more detail and explain the ins and outs so people like me can, looking for help, could learn a little more. Posting a reply saying to read the docs it’s not very usefull. You must also understand that not everyone has the same experience. Anyway thank you for your effort.

[KenWhitesell]

Forget the third table then for a moment. Solve the query for two tables. Then add the references for the third. (Adding a third table into the mix does not materially affect the solution.)

Actually, you can break this problem down further. First, write your query for just the one table (Alunos). Get the rows that you want from that. Then add your references for the second and third table.

See, the simple version is the answer - you’re looking for complexity that isn’t there. Fundamentally, there is no difference between a reference to sessao.nome and sessao.classeid.turma_name. The query doesn’t change when referencing the second compared to the first.

To address some of your other points:

@mfreitas64

Maybe you could use more detail and explain the ins and outs…

It’s not practical for me to write books here. To know what details I need to address, I need to know the starting point and which details I need to cover.

@mfreitas64

Posting a reply saying to read the docs it’s not very usefull.

Actually, it is. Because I know that not everyone has the same background and knowledge, I don’t know what you may or may not have read - nor can I know what concepts you may be struggling with. (If you search through the posts here, you’ll find numerous threads where the answer is a link to the docs, because the person posting the question didn’t know where to find the information.)

Nor is there much value in me re-writing what has already been written.

Additionally, the tutorial is extremely important in cases like this, because it does provide a baseline of knowledge that we can build from. That “Playing with the API” in step two highlights the difference between .get, .filter, and .all.

[mfreitas64]

So, why am I getting the data from all tables correctly and displaying but the

line on top of the template doesn’t work?

{% block content %}
    <div class="row">
        <h4> {{ sessao.classeid.turma_name }} - {{ sessao.classeid.weekdays }} - {{ sessao.classeid.times }}</h4>
    </div>
    <br>
    <a class="btn btn-outline-primary btn-sm" href="{% url 'school:turmas' %}">Voltar às Turmas</a>
    <br>
    <br>
    <div class="row justify-content-center">

            <div class="col-md-3 book margens">
                {% for a in sessao %}
                <ul>
                    <li>{{ a.nome }} - {{ a.alunoid }} - {{ a.educaid }} - {{ a.educaid.email }} - {{ a.educaid.nif }}</li>
                </ul>
                {% endfor %}
            </div>

    </div>
{% endblock content %}

class TurmasDetailView(ListView):
    model = Alunos
    template_name = 'schooladmin/detalhe_turmas.html'
    context_object_name = 'sessao'

    def get_queryset(self):
        return Alunos.objects.select_related('educaid','classeid').filter(classeid=self.kwargs['pk'])

That’s what I need to understand now, and I’m not looking for complexity, I’m looking to learn more so I can be a better programmer.

[KenWhitesell]

What data type is sessao in your context?

Or, to phrase this a different way, what does this query return?

@mfreitas64

Alunos.objects.select_related('educaid','classeid').filter(classeid=self.kwargs['pk'])

(This issue is a template issue, not a query issue.)

[mfreitas64]

If you know it’s a template issue why don’t you share it? Again you are just giving your opinion and not helping, you make suggestions but no real answers for my problem.

Is there anyone else in this forum that can help???

Thanks