Equivalent between `MySQL JOIN` and Django

Question

I'm using Django 1.11 and I would like to get the correct syntax in order to translate my MySQL Join request to Django QuerySet Syntax.

I have 3 different models (1 Model in my Identity application and 2 Models in my Fiscal application) as following :

#Identity.Individu

class Individu(models.Model):

    NumeroIdentification = models.CharField(max_length=30, null=True, verbose_name='Numero Identification physique', unique=True)
    Civilite = models.CharField(max_length=12,choices=CHOIX_TITRE, verbose_name='Civilité')
    NomJeuneFille = models.CharField(max_length=30, verbose_name='Nom de jeune fille', blank=True)
    Nom = models.CharField(max_length=30, verbose_name='Nom de famille')
    Prenom = models.CharField(max_length=30, verbose_name='Prénom(s)')

#Fiscal.Profession

class Profession (models.Model) :

    Employe = models.ForeignKey(Individu, related_name='Individu', verbose_name='Individu', null=False, to_field='NumeroIdentification')
    Entreprise = models.ForeignKey(Societe, related_name='Société', verbose_name='Société', null=False, to_field='NumeroIdentification')
    NumeroImpot = models.CharField(max_length=30, null=True, verbose_name='Numéro Imposition', unique=True)
    Profession = models.CharField(max_length=30, verbose_name='Profession')

#Fiscal.Salaire 

class Salaire (models.Model) :

    Employe = models.ForeignKey(Individu, related_name='Salaire_Individu', verbose_name='Salaire_Individu', null=False, to_field='NumeroIdentification')
    Salaire_B_mensuel = models.IntegerField(verbose_name='Salaire Brut Mensuel')
    Salaire_N_mensuel = models.IntegerField(verbose_name='Salaire Net Mensuel')

With Mysql, I have this request which works fine :

SELECT * FROM Identity_individu i 
    INNER JOIN Fiscal_profession p ON i.NumeroIdentification = p.Employe_id 
    INNER JOIN Fiscal_salaire s on i.NumeroIdentification = s.Employe_id;

I thing I have to use prefetch_related and I wrote this (first step, join between two tables) :

@login_required
def FiscalReporting(request) :

    Contribuable = Individu.objects.prefetch_related("Profession").all().filter(NumeroIdentification=Profession.Employe)

    context = {
        "Contribuable" : Contribuable,
        }

    return render(request, 'Fiscal_Reporting.html', context)

And in my template :

<table style="width:120%">
            <tbody>
            <tr>
                <th>ID</th>
                <th>État</th>
                <th>N° Identification</th>
                <th>Civilité</th>
                <th>Nom</th>
                <th>Prénom</th>
                <th>Date de Naissance</th>
                <th>N° Fiscal</th>
                <th>Salaire Annuel Net</th>
            </tr>
            {% for item in Contribuable %}
            <tr>
                <td>{{ item.id}}</td>
                <td>{{ item.Etat}}</td>
                <td>{{ item.NumeroIdentification}}</td>
                <td>{{ item.Civilite }}</td>
                <td>{{ item.Nom }}</td>
                <td>{{ item.Prenom }}</td>
                <td>{{ item.DateNaissance }}</td>
                <td>{{ item.NumeroImpot }}</td>
                <td>{{ item.Employe.Salaire_N_annuel }}</td>
            </tr>
            {% endfor %}
            </tbody>
        </table>

Any idea about my issue ? I don't find any result up to now because my array is empty :/

Then, How I can add a supplementary join in my Django QuerySet ?

score 1 · Accepted Answer · edited Oct 11 '17 at 08:19

1

You can use prefetch_related to prefetch more than one related table like this:

Contribuable = Individu.objects.prefetch_related("Individu", "Salaire_Individu")

Note that I use "Individu" related_name of Profession model as prefetch_related argument.

Now in template you can iterate over each profession and Salaire_Individu like this:

{% for item in Contribuable %}
    ...
    <td>{{ item.Nom }}</td>
    {% for profession in item.Individu.all %}
         <td>{{ profession.NumeroImpot }}</td>
    {% endfor %} 
    {% for salary in item.Salaire_Individu.all %}
         <td>{{ salary.Salaire_B_mensuel }}</td>
    {% endfor %} 
{% endfor %}

edited Oct 11 '17 at 08:19

Essex

6,042
11
67
139

answered Oct 10 '17 at 16:16

neverwalkaloner

46,181
7
92
100

`select_related` is closer to a sql join that `prefetch_related`, in fact, `select_related` is a sql join. https://stackoverflow.com/questions/31237042/whats-the-difference-between-select-related-and-prefetch-related-in-django-orm – luxcem Oct 10 '17 at 16:27
I tried your syntax but I get an issue in my array. I'm looping over each item in `Contribuable` and I can display `Nom`, ... (fields from `Individu` Table). But I should also display elements from Table `Profession` and `Salaire` right ? As you can see in my question : `item.Nom` works fine, but `item.NumeroImpot` doesn't display element. My syntax is false ? – Essex Oct 11 '17 at 07:15

Equivalent between `MySQL JOIN` and Django

1 Answers1