Django dynamic field casting when annotate

user_2566671 0
Viewed 24

Lets say I want to save this data in to db:

{
        "fields": [
            {
                "field_type": "number",
                "name": "field_1",
                "value": 8223,
            },
            {
                "field_type": "string",
                "name": "field_2",
                "value": "demo"
              
            },
            {
                "field_type": "bool",
                "name": "field_3",
                "value": true
              
            }
        ],
        "blockchain_id": 47,
        "name": "event",
        "block_number": 3916102,
    }

Here value is dynamic based on field_type value. Here is my models:

class Event(models.Model):
    blockchain_id = models.IntegerField()
    name = models.CharField(max_length=100)
    block_number = models.IntegerField()
    fields = models.ManyToManyField(EventField)

class EventField(models.Model):
    TYPE_CHOICES = (
        ('number', 'Number'),
        ('string', 'String'),
        ('bool', 'Boolean'),
    )
    field_type = models.CharField(max_length=6, choices=TYPE_CHOICES)
    name = models.CharField(max_length=100)
    value = models.CharField(max_length=100)

I am saving dynamic value as Character (you can suggest something better). Then when I am returing those values I want to cast based on field_type value. So far here is my query:

Event.objects.filter(id=_id).annotate(fields_value=Case(
            When(fields__field_type='number', then=Cast('fields__value', output_field=models.IntegerField())),
            When(fields__field_type='string', then=Cast('fields__value', output_field=models.CharField())),
            When(fields__field_type='bool', then=Cast('fields__value', output_field=models.BooleanField())),
            output_field=Case(When(fields__field_type='number', then=models.IntegerField()))))

Now I am gettings this error message Cannot resolve expression type, unknown output_field .Maybe I am missing something. How can I return value with casting based on field_type ?

django-models django
1 Answers

You can not work with such dynamic casting: for SQL each column has a specific type and all records use the same type for that column.

You will need to do this at the Django/Python layer, for example with:

class EventField(models.Model):
    TYPE_CHOICES = (
        ('number', 'Number'),
        ('string', 'String'),
        ('bool', 'Boolean'),
    )
    field_type = models.CharField(max_length=6, choices=TYPE_CHOICES)
    name = models.CharField(max_length=100)
    value = models.CharField(max_length=100)

    @property
    def typed_value(self):
        if self.field_type == 'number':
            return int(self.value)
        if self.field_type == 'string':
            return self.value
        if self.field_type == 'bool':
            return self.value.casefold() == 'true'
        raise ValueError('Invalid field type.')

You can then inspec the value with my_event_field.typed_value.

user_675790
Related