Use annotate and Case with different field types in Django

Question

I have a model with those field:

class ModelA(models.Model):
    parent = models.ForeignKey('self', null=True, blank=True, on_delete=models.PROTECT, related_name='child')
    number = models.PositiveIntegerField()
    text_code = models.CharField(max_length=255, blank=True, null=True)

I want to make a query on that model using annotate and Case with those rules:

When parent is null it returns the text_code field
When parent is not null it returns parent__number field

I can make it with SQL in PostgreSQL like:

CASE WHEN "ModelA"."parent_id" IS NOT NULL 
    THEN T3."number"**::integer** 
ELSE "ModelA"."text_code"**::integer** END AS "control_field"

But I can't make that with Django ORM. I've tried the following:

query = ModelA.objects.all().select_related('parent').annotate(
    control_field=Case(
        When(parent_id__isnull=False, then='parent__number'),
        default='text_code',
        output_field=IntegerField(),
    ),
)

But when I call query result, it rase the following error:

django.db.utils.ProgrammingError: CASE types character varying and integer cannot be matched

I know it is an Database error, because the SQL result from django construct doesn't have the ::integer add.

CASE WHEN "ModelA"."parent_id" IS NOT NULL 
    THEN T3."number" 
ELSE "ModelA"."text_code" END AS "control_field"

How can I solve this? I'm working with Django 1.11

Looks quite unsafe. Why is text_code a CharFieldif it is numerical? — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jul 25, 2018 at 11:42
But you can use Cast for this docs.djangoproject.com/en/2.0/ref/models/database-functions/… — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jul 25, 2018 at 11:45
@WillemVanOnsem this field came from another class and it can be numerical or textualc depending on the model — Tiago Gomes
– Tiago Gomes, Commented Jul 25, 2018 at 12:57
@WillemVanOnsem Thank you so much! I spent two days reading the docs, and couldn't saw that function! — Tiago Gomes
– Tiago Gomes, Commented Jul 25, 2018 at 13:05

willeM_ Van Onsem · Accepted Answer · 2018-07-25 13:15:53Z

2

The answer is, use Cast:

query = ModelA.objects.all().select_related('parent').annotate(
    control_field=Case(
        When(parent_id__isnull=False, then=Cast('parent__number', IntegerField())),
        default=Cast('text_code', IntegerField()),
        output_field=IntegerField(),
    ),
)

edited Jul 25, 2018 at 13:15

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

answered Jul 25, 2018 at 13:08

Tiago Gomes

3774 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use annotate and Case with different field types in Django

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related