Case When Query return duplicated objects

Django Internals ORM
1

I have the next model.

class Llanta(models.Model):
    # Modelo de la Llanta

    numero_economico = models.CharField(max_length=200, null=True)
    compania = models.ForeignKey(Compania, on_delete=models.CASCADE, null=True, blank=True)
    vehiculo = models.ForeignKey(Vehiculo, on_delete=models.CASCADE, null=True, blank=True)
    ubicacion = models.ForeignKey(Ubicacion, on_delete=models.CASCADE, blank=True, null=True)
    aplicacion = models.ForeignKey(Aplicacion, on_delete=models.CASCADE, blank=True, null=True)
    taller = models.ForeignKey(Taller, on_delete=models.CASCADE, blank=True, null=True)
    renovador = models.ForeignKey('Renovador', on_delete=models.CASCADE, null=True, blank=True)
    opciones_vida = (("Nueva", "Nueva"),
                            ("1R", "1R"),
                            ("2R", "2R"),
                            ("3R", "3R"),
                            ("4R", "4R"),
                            ("5R", "5R"),
                )
    vida = models.CharField(max_length=200, choices=opciones_vida, null=True, blank=True, default="Nueva")
    tipo_de_eje = models.CharField(max_length=4, null=True, blank=True)
    eje = models.IntegerField(blank=True, null=True)
    posicion = models.CharField(max_length=4, null=True, blank=True)
    opciones_de_eje = (("DirecciĂłn", "DirecciĂłn"),
                        ("TracciĂłn", "TracciĂłn"),
                        ("Arrastre", "Arrastre"),
                        ("Loco", "Loco"),
                        ("Retractil", "Retractil"),
                        ("RefacciĂłn", "RefacciĂłn")
                )
    nombre_de_eje = models.CharField(max_length=200, choices=opciones_de_eje, null=True, blank=True)
    presion_de_entrada = models.IntegerField(blank=True, null=True)
    presion_de_salida = models.IntegerField(blank=True, null=True)
    presion_actual = models.IntegerField(blank=True, null=True)
    fecha_de_inflado = models.DateTimeField(null=True, blank=True)
    primera_inspeccion = models.ForeignKey(Inspeccion, null=True, blank=True, on_delete=models.SET_NULL, related_name="inspecciones_first")
    ultima_inspeccion = models.ForeignKey(Inspeccion, null=True, blank=True, on_delete=models.SET_NULL, related_name="inspecciones")
    profundidad_izquierda = models.FloatField(blank=True, null=True)
    profundidad_central = models.FloatField(blank=True, null=True)
    profundidad_derecha = models.FloatField(blank=True, null=True)
    km_actual = models.IntegerField(blank=True, null=True)
    km_montado = models.IntegerField(blank=True, null=True)

    producto = models.ForeignKey(Producto, null=True, blank=True, on_delete=models.CASCADE)
    opciones_de_inventario = (("Nueva", "Nueva"),
                        ("Antes de Renovar", "Antes de Renovar"),
                        ("Antes de Desechar", "Antes de Desechar"),
                        ("Renovada", "Renovada"),
                        ("Con renovador", "Con renovador"),
                        ("Desecho final", "Desecho final"),
                        ("Servicio", "Servicio"),
                        ("Rodante", "Rodante"),
                        ("Archivado", "Archivado")
                )
    inventario = models.CharField(max_length=200, choices=opciones_de_inventario, null=True, blank=True, default="Rodante")
    fecha_de_entrada_inventario = models.DateField(null=True, blank=True)
    rechazo = models.ForeignKey("Rechazo", on_delete=models.SET_NULL, null=True, blank=True)
    observaciones = models.ManyToManyField("Observacion", limit_choices_to={'nivel': "Llanta"}, blank = True)
    tirecheck = models.BooleanField(default=False)
    fecha_de_balanceado = models.DateField(null=True, blank=True)
    
    parametro_desgaste_irregular = models.FloatField(blank=True, null=True)
    patito = models.BooleanField(default=False)

And my intencion is return a json whit the information of this, an i have a this class.

class LlantaData(View):
    def get(self, request , *args, **kwargs):
        #Queryparams
        usuario = kwargs['usuario']
        user = User.objects.get(username = usuario)
        perfil = Perfil.objects.get(user = user)
        compania = perfil.compania
        llantas = Llanta.objects.filter(compania=compania
        ).annotate(
            health=Case(When(observaciones__color__in=["Rojo", "Amarillo"], then=False), default=True),
        
            punto_de_retiro = Case(
                When(nombre_de_eje="DirecciĂłn", then=F("vehiculo__compania__punto_retiro_eje_direccion")),
                When(nombre_de_eje="TracciĂłn", then=F("vehiculo__compania__punto_retiro_eje_traccion")),
                When(nombre_de_eje="Arrastre", then=F("vehiculo__compania__punto_retiro_eje_arrastre")),
                When(nombre_de_eje="Loco", then=F("vehiculo__compania__punto_retiro_eje_loco")),
                When(nombre_de_eje="Retractil", then=F("vehiculo__compania__punto_retiro_eje_retractil"))
                ),
            p1=Case(
                When(Q(profundidad_central=None) & Q(profundidad_derecha=None), then=Value(1)), 
                When(Q(profundidad_izquierda=None) & Q(profundidad_derecha=None), then=Value(2)), 
                When(Q(profundidad_izquierda=None) & Q(profundidad_central=None), then=Value(3)), 
                When(Q(profundidad_izquierda=None), then=Value(4)), 
                When(Q(profundidad_central=None), then=Value(5)), 
                When(Q(profundidad_derecha=None), then=Value(6)), default=0, output_field=IntegerField()),
            min_profundidad=
                Case(
                    When(p1=0, then=Least("profundidad_izquierda", "profundidad_central", "profundidad_derecha")),
                    When(p1=1, then=F("profundidad_izquierda")), 
                    When(p1=2, then=F("profundidad_central")), 
                    When(p1=3, then=F("profundidad_derecha")), 
                    When(p1=4, then=Least("profundidad_central", "profundidad_derecha")), 
                    When(p1=5, then=Least("profundidad_izquierda", "profundidad_derecha")), 
                    When(p1=6, then=Least("profundidad_izquierda", "profundidad_central")), 
                    output_field=FloatField()),
        
            ubicacion_llanta = F('vehiculo__ubicacion__nombre'),
            aplicacion_llanta = F('vehiculo__aplicacion__nombre'),
        
            presion_establecida = Case(
                 When(eje=1, then=F('vehiculo__presion_establecida_1')),
                 When(eje=2, then=F('vehiculo__presion_establecida_2')),
                 When(eje=3, then=F('vehiculo__presion_establecida_3')),
                 When(eje=4, then=F('vehiculo__presion_establecida_4')),
                 When(eje=5, then=F('vehiculo__presion_establecida_5')),
                 When(eje=6, then=F('vehiculo__presion_establecida_6')),
                 When(eje=7, then=F('vehiculo__presion_establecida_7')),
            ),
            
            objetivo =  (Cast('compania__objetivo', output_field=FloatField()) / 100.0),
            
            max_presion = F('presion_establecida') + ( F('presion_establecida') * F('objetivo') ),
            min_presion = F('presion_establecida') - ( F('presion_establecida') * F('objetivo') ),
            status_presion = Case(
                When(presion_actual__lt=F('min_presion'), then=Value('baja')),
                When(presion_actual__gt=F('max_presion'), then=Value('alta')),
                default=Value('buena')
            ),
            status_profundidad = Case(
                When( min_profundidad__lte = F('punto_de_retiro'), then = Value('baja') ),
                default = Value('buena')
            ),
            primera = Case(
                When(primera_inspeccion = None, then=0),
                default=1
            ),
            ultima = Case(
                When(ultima_inspeccion = None, then=0),
                default=2
            ),
            operacion_des = F('primera') + F('ultima'),
            primera_profundidad = Least(
                "primera_inspeccion__profundidad_izquierda", 
                "primera_inspeccion__profundidad_central", 
                "primera_inspeccion__profundidad_derecha"
                ),
            ultima_profundidad = Least(
                "ultima_inspeccion__profundidad_izquierda", 
                "ultima_inspeccion__profundidad_central", 
                "ultima_inspeccion__profundidad_derecha"
                ),
            desgaste_diario = Case(
                When(operacion_des = 3, then=F('primera_profundidad') - F('ultima_profundidad')),
                When(operacion_des = 2, then=Value(0.0)),
                default=None
            ),
            remplazo = Case(  #<- This is a problem!
                When(observaciones__observacion__in=["En punto de retiro", "Baja profundidad"], then=Value('REEMPLAZO')),
                When(observaciones__observacion__in=["Remplazo dual"], then=Value('REEMPLAZO DUAL')),
                default=Value('SIN REEMPLAZO'))
        ).distinct()
        
        
        #Serializar data
        llantas = list(llantas.values(
                            "status_presion",
                            "numero_economico",
                            "compania__compania",
                            "vehiculo__numero_economico",
                            "taller__nombre",
                            "renovador__nombre",
                            "vida",
                            "tipo_de_eje",
                            "eje",
                            "posicion",
                            "nombre_de_eje",
                            "presion_de_entrada",
                            "presion_de_salida",
                            "presion_actual",
                            "fecha_de_inflado",
                            "ultima_inspeccion_id",
                            "profundidad_izquierda",
                            "profundidad_central",
                            "profundidad_derecha",
                            "km_actual",
                            "km_montado",
                            "producto__producto",
                            "inventario",
                            "fecha_de_entrada_inventario",
                            "rechazo_id",
                            "tirecheck",
                            "fecha_de_balanceado",
                            "health",
                            "min_profundidad",
                            "ubicacion_llanta",
                            "aplicacion_llanta",
                            "status_profundidad",
                            "desgaste_diario",
                            "remplazo"
                            
                            ))
        
        dict_context = {
            'llantas': llantas,
        }

        json_context = json.dumps(dict_context, indent=None, sort_keys=False, default=str)

        return HttpResponse(json_context, content_type='application/json')

All work fine except one thing, te last field “remplazo” cause duplicate items. I see that the Case takes 2 When as valid, for example:

askdjango
askdjango1080Ă—1520 379 KB

Is there a way to not take the 2 cases and only keep the first case that is fulfilled?

Im reead this, and mention of the posible cause of the problem for the many to many fields, and i don’t know how make a distinct in a many to many in anottate.
https://code.djangoproject.com/ticket/28292

2

You don’t.

Or, to be more specific, the explanation provided in the ticket you referenced is correct. Since you are assigning different values for the different relationships, the rows in the result set being generated are unique across the set of columns being returned.

It seems to me that you first need to decide what you want to have happen when both When clauses are true. You either need to select one of the two values to use, or add them both to the result row as a list.

4

I tried what you said, but I could not find a way to decide what will happen if the 2 Whens are fulfilled.

I still tried to do the following:

ope_remplazo_1 = Case(
                When(observaciones__observacion = "En punto de retiro", then=Value(2)),
                default=Value(0)),
ope_remplazo_2 = Case(
                When(observaciones__observacion = "Baja profundidad", then=Value(2)),
                default=Value(0)),
ope_remplazo_3 = Case(
                When(observaciones__observacion = "Remplazo dual", then=Value(1)),
                default=Value(0)),
operacion_remplazo = F('ope_remplazo_1') + F('ope_remplazo_2') + F('ope_remplazo_3'),
            
remplazo = Case(
                When(operacion_remplazo = 5, then=Value('REEMPLAZO DUAL')),
                When(operacion_remplazo = 4, then=Value('REEMPLAZO')),
                When(operacion_remplazo = 3, then=Value('REEMPLAZO DUAL')),
                When(operacion_remplazo = 2, then=Value('REEMPLAZO')),
                When(operacion_remplazo = 1, then=Value('REEMPLAZO DUAL')),
                When(operacion_remplazo = 0, then=Value('SIN REEMPLAZO')),
            )

Which according to my logic should not duplicate, because I ask directly and separately if a certain “observation” is found in the field, but again it duplicates the result.

What part of the documentation could I consult to try to decide what will happen if both whens are met as you say.

I really appreciate your help, as at least I know I can’t do the distict directly.

5

Another ticken I was researching was the following:
https://code.djangoproject.com/ticket/26658
And although it is not exactly the problem that I have, it gave me an idea (somewhat silly, but I didn’t lose anything by trying) and it was to place an ouput field, but it kept giving duplicate elements.

6

This isn’t a “documentation” question - it’s something you need to decide.

What do you want to have happen if both When clauses are True?

1 Like
7

I want that if both When clauses are True, the value “remplazo” is “REEMPLAZO DUAL”.
That’s the result I’m looking for, without duplicating the other conditions.

8

I think then the best thing for you to do would be to remove that field from your original query, iterate over the queryset, and add this field to the queryset before returning it in the response.

1 Like
9

Thanks, taking into account what you told me I tried to do and it seems to be the solution to this problem, just one more thing, in the process of doing what you said I ran into the following problem:
I added replacement as an empty field to later be able to edit it:

replacement = Value('')

Then I iterate it and to be able to modify it, at the moment I am with a placeholder
for llanta in llantas:

llanta.remplazo= 'Example'.

But at the moment of serializing the information the field appears empty.

I tried to make a print in each iteration to verify that the data was being saved, and so it happens, but at the moment of serializing it returns to its original value that is ’ '.

Sin tĂ­tulo-1
Sin tĂ­tulo-11024Ă—1486 168 KB