How can this sql query be transformed in postgresql to query in django

Question

How can this sql query be transformed in postgresql to query in django

Navigation

#1 by (1 votes)

2

Good morning to all:

I have the following query in postgresql that returns the Id of the last contract that a worker has signed with a company in 2016 (from 01/01/2016 to 12/31/2016), as well as their paternal surnames and maternal, name, start date and end date of the last signed contract. For example: If the worker XYZ has signed 2 contracts in 2016, the first of them from 05/01/2016 to 03/31/2016 and the second contract from 01/06/2016 to 12/31/2016 , the query returns the information related to the second contract.

SELECT "rrhh_contrato"."id", "rrhh_contrato"."persona_id", "rrhh_persona"."apellido_paterno",
"rrhh_persona"."apellido_materno", "rrhh_persona"."nombre1","rrhh_persona"."nombre2","rrhh_persona"."nombre3","rrhh_persona"."nombre4","rrhh_contrato"."fecha_inicio", 
"rrhh_contrato"."fecha_fin"
FROM "rrhh_contrato" 
INNER JOIN "sistema_local" ON ("rrhh_contrato"."local_id" = "sistema_local"."id")
INNER JOIN "rrhh_persona" ON ("rrhh_contrato"."persona_id" = "rrhh_persona"."id") 
INNER JOIN (
SELECT "rrhh_contrato"."persona_id",MAX("rrhh_contrato"."fecha_fin") AS max_fecha
FROM "rrhh_contrato" 
INNER JOIN "sistema_local" ON ("rrhh_contrato"."local_id" = "sistema_local"."id") 
WHERE ("sistema_local"."producto_id" = 1 AND "rrhh_contrato"."fecha_fin" 
BETWEEN '2016-01-01'::date AND '2016-12-31'::date)
GROUP BY "rrhh_contrato"."persona_id"
ORDER BY "rrhh_contrato"."persona_id"
) R ON "rrhh_contrato"."persona_id" = R.persona_id AND "rrhh_contrato"."fecha_fin" = R.max_fecha
WHERE "sistema_local"."producto_id" = 1
ORDER BY "rrhh_contrato"."persona_id";

My question is: How can this query sql be converted to query django.

This is part of the method that is in the view of my project:

from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger

contratos = Contrato.objects.filter(fecha_fin__year=ejercicio, local__producto=1).order_by('persona__apellido_paterno','persona__apellido_materno','fecha_fin')

paginator = Paginator(contratos, 15)
page = request.GET.get('page')

try:
     contratos = paginator.page(page)
except PageNotAnInteger:
     contratos = paginator.page(1)
     except EmptyPage:
contratos = paginator.page(paginator.num_pages)

return render(request, 'lista_contratos.html', { 'permisos_opciones' : permisos_opciones, 'estados' : estados, 'contratos' : contratos, 'ejercicios' : ejercicios, 'id_ejercicio' : id_ejercicio })

and what's here is part of the .html template

<table id="tablaLstContratos" width="100%" class="table table-condensed table-bordered table-hover">
    <tr>
        <th style="text-align:center;">#</th>
        <th style="text-align:center;">Documento</th>
        <th style="text-align:center;">Fecha Inicio</th>
        <th style="text-align:center;">Fecha Fin</th>
        <th style="text-align:center;">Nombre Completo</th>
        <th style="text-align:center;">Tipo Contrato</th>
    </tr>
    {% for contrato in contratos %}
    <tr>
       <td style="text-align:center;">{{ forloop.counter0|add:contratos.start_index }}</td>
       <td style="text-align:center;">{{ contrato.persona.nro_documento }}</td>
       <td style="text-align:center;">{{ contrato.fecha_inicio|date:"d/m/Y" }}</td>
       <td style="text-align:center;">{{ contrato.fecha_fin|date:"d/m/Y" }}</td>
       <td style="text-align:justify-all;">{{ contrato.persona.apellido_paterno }} {{ contrato.persona.apellido_materno }}, {{ contrato.persona.nombre1 }} {{ contrato.persona.nombre2 }}</td>
       <td style="text-align:justify-all;">{{ contrato.tipo_contrato }}</td>
   </tr>
   {% endfor %}
</table>

These are the models:

class Persona(models.Model):
    tipo_documento = models.CharField(max_length=2)
    nro_documento = models.CharField(max_length=15, unique=True)
    pais_documento = models.CharField(max_length=3)
    fecha_nacimiento = models.CharField(max_length=10)
    apellido_paterno = models.CharField(max_length=40)
    apellido_materno = models.CharField(max_length=40)
    nombre1 = models.CharField(max_length=25)
    nombre2 = models.CharField(max_length=25, blank=True, null=True)
    nombre3 = models.CharField(max_length=25, blank=True, null=True)
    nombre4 = models.CharField(max_length=25, blank=True, null=True)
    sexo = models.CharField(max_length=1)
    nacionalidad = models.CharField(max_length=4)
    codigo_telefono = models.CharField(max_length=3, blank=True, null=True)
    telefono = models.CharField(max_length=9, blank=True, null=True)
    email = models.EmailField(max_length=50, blank=True, null=True)

class Contrato(models.Model):
    persona = models.ForeignKey(Persona)
    fecha_inicio = models.DateField()
    fecha_fin = models.DateField(blank=True, null=True)
    local = models.ForeignKey('sistema.Local')
    ocupacion_producto = models.ForeignKey(Cargo)
    regimen_laboral = models.CharField(max_length=2)
    ocupacion_sunat = models.CharField(max_length=6)
    sctr_pension = models.CharField(max_length=1, blank=True)
    tipo_trabajador = models.CharField(max_length=2)
    tipo_contrato = models.CharField(max_length=2)
    jornada_atipica_acumulativa = models.CharField(max_length=1)
    jornada_trabajo_maxima = models.CharField(max_length=1)
    trabajo_horario_nocturno = models.CharField(max_length=1)
    sindicalizado = models.CharField(max_length=1)
    periocidad_ingreso = models.CharField(max_length=1)
    remuneracion = models.FloatField()
    situacion = models.CharField(max_length=2)
    estado_contrato = models.CharField(max_length=2, default='1')
    motivo_baja = models.CharField(max_length=2, blank=True, null=True)
    fecha_cese = models.DateField(null=True,blank=True)

class Local(models.Model):
    nombre = models.CharField(max_length=50)
    direccion = models.CharField(max_length=50)
    telefono = models.CharField(max_length=15)
    celular1 = models.CharField(max_length=15,null=True)
    celular2 = models.CharField(max_length=15,null=True)
    codigo_sunat = models.CharField(max_length=4)
    producto = models.ForeignKey(Producto)

class Producto(models.Model):
    descripcion = models.CharField(max_length=50,unique=True)
    color_a = models.CharField(max_length=7)
    color_b = models.CharField(max_length=7,null=True)
    empresa = models.ForeignKey(Empresa)

django postgresql

asked by Carlos1886 25.03.2017 в 14:16

source

1 answer

Listener in JavaFX Use input () within a for cycle (Python)

score 1 · Accepted Answer

I will ignore, momentarily , the query in SQL and the template since they are not relevant in the question and I am going to focus on what you expect as output:

the Id of the last contract that a worker has signed with a company in 2016 (from 01/01/2016 to 12/31/2016), as well as their paternal and maternal surnames, name, start date and end date of the last signed contract.

For this, we will work with a Minimum, complete and verifiable example of your problem:

# coding: utf-8
from django.db import models

# Create your models here.


class Persona(models.Model):
    apellido_paterno = models.CharField(max_length=40)
    apellido_materno = models.CharField(max_length=40)
    nombre1 = models.CharField(max_length=25)

    def __str__(self):
        return "%s %s %s" % (self.nombre1, self.apellido_paterno, self.apellido_materno)


class Producto(models.Model):
    descripcion = models.CharField(max_length=50,unique=True)

    def __str__(self):
        return "%s" % self.descripcion


class Local(models.Model):
    nombre = models.CharField(max_length=50)
    producto = models.ForeignKey(Producto)

    def __str__(self):
        return "%s - %s" % (self.nombre, self.producto)


class Contrato(models.Model):
    persona = models.ForeignKey(Persona)
    fecha_inicio = models.DateField()
    fecha_fin = models.DateField(blank=True, null=True)
    local = models.ForeignKey('Local')

    def __str__(self):
      return "De %s a %s" % (self.fecha_inicio, self.fecha_fin)

We will place the minimum data to understand the example:

u = Persona(nombre1='pedro', apellido_paterno='nada', apellido_materno='mas')
p = Producto(descripcion='jabón motita')
l = Local(nombre='bodega', producto=p)
c1 = Contrato(persona=u, fecha_inicio=datetime(2016,1,16), fecha_fin=datetime(2016,8,31), local=l)
c2 = Contrato(persona=u, fecha_inicio=datetime(2016,9,1), fecha_fin=datetime(2017,1,15), local=l)
c3 = Contrato(persona=u, fecha_inicio=datetime(2016,3,1), fecha_fin=datetime(2016,3,31), local=l)

As you can see, the person with pk=1 has two contracts, in order to review the example, we will look for the last contract that ended in 2016 .

First let's filter by user:

>>> contrato = Contrato.objects.filter(persona=1)
>>> contrato
>>> <QuerySet [
      <Contrato: De 2016-01-16 a 2016-08-31>, 
      <Contrato: De 2016-09-01 a 2017-01-15>, 
      <Contrato: De 2016-03-01 a 2016-03-31>
    ]>

Now we are going to add the 2016 year filter:

>>> contrato = contrato.filter(fecha_fin__year=2016)
>>> <QuerySet [
      <Contrato: De 2016-01-16 a 2016-08-31>, 
      <Contrato: De 2016-03-01 a 2016-03-31>
    ]>

Now, we are going to look for the last contract signed in 2016 (in the example it is the last contract finalized in 2016). For that we use the latest() filter.

>>> contrato
>>> <Contrato: De 2016-01-16 a 2016-08-31>

The complete query is like this:

>>> contrato = Contrato.objects.filter(persona=u, fecha_fin__year=2016).latest('fecha_fin')
>>> contrato
>>> <Contrato: De 2016-01-16 a 2016-08-31>

The resulting object contains the data of Persona that we can access without any problem:

>>> contrato
>>> <Contrato: De 2016-03-01 a 2016-03-31>

>>> contrato.persona
>>> <Persona: pedro nada mas>

>>> contrato.fecha_fin
>>> datetime.date(2016, 3, 31)

>>> contrato.fecha_inicio
>>> datetime.date(2016, 3, 1)

>>> contrato.id
>>> 3

Of course, you can use filters that involve other fields of your model using Expressions F () or more complex queries using Objects Q , consult the documentation.