Join between two tables with django

Question

Join between two tables with django

Navigation

#1 by (1 votes)

1

I am trying to make a join between two tables in my database. I have my model called parameter and my model called validity, the latter has an attribute that is a foreign parameter key. I need to list all the parameters and for each of them the value and date of the longest period. What I do not know how to do that the join between these two models to stay for each parameter its registration of the validity model with greater date

Thanks

django

asked by rodrigo_uy 15.04.2017 в 15:23

source

1 answer

Kivy: Simple program does not work for me Argument errors in C # [closed]

score 1 · Accepted Answer

The join in Django can be done by accessing as an attribute, for example (assuming that the foreign key in the Validity model is called "parameter":

vigencias = Vigencia.objects.all() # Obtengo todas las vigencias
for vig in vigencias:
    print(vig.parametro) # Imprime el objeto parametro
    print(vig.parametro.descripcion) # Suponiendo que el modelo Parametro tiene un atributo descripcion

One optimization that can be done is to use the select_related() (if you are sure that later you will use the foreign key), the first line would look like this:

vigencias = Vigencia.objects.all().select_related('parametro') # Obtengo todas las vigencias, y los parametros en la misma consulta.

This way you will not consult the database every time you want to obtain a parameter of a Validity.

In the same way you can access the attributes in a template:

<ul>
    {% for vig in vigencias %}
        <li>{{vig.parametro.descripcion}}</li>
    {% endfor %}
</ul>

On the other hand, if you have multiple connected models you can also filter in a join, for example assuming that there are 3 models:

class Modelo1(models.Model):
    nombre = models.CharField(max_length=30)

class Modelo2(models.Model):
    descripcion = models.CharField(max_length=30)
    mod1 = models.ForeignKey(Modelo1, on_delete=models.CASCADE)

class Modelo3(models.Model):
    altura = models.CharField(max_length=30)
    mod2 = models.ForeignKey(Modelo2, on_delete=models.CASCADE)

The following query can be made:

mod3 = Modelo3.objects.filter(mod2__mod1__nombre = "Rodrigo") # Hace un join de las 3 tablas y deja solo los que tiene un nombre igual a "Rodrigo"

For the case of wanting to obtain the validity of the major date, said minor date equal to today would be:

vigenciaMasReciente = Vigencia.objects.filter(fecha__lte = date.today()).select_related('parametro').order_by('-fecha')[0] # Primero filtro por fecha menor igual a hoy, despues ordeno de forma decreciente y por ultimo obtengo el primer elemento de ese QuerySet retornado

Here I leave you to read about the select_related ().

I hope I have helped you.