Django Filter an String as Integer?

January 26, 2024

I have a problem when I need to make my filter:

I need that Django compare a String field as Integer (Almost the entire table are Integers)

This don’t work, because only return the entry with 9, not with 12 or 13.

queryset = Cards.objects.extra(select={'stars': 'CAST(stars AS INTEGER)'}).filter(stars__gte= 9)

I tried also this:

Cards.objects.annotate(stars_integer=Cast('stars', output_field=IntegerField())).filter(stars__gte= 9)

With the same result, and I can’t change the field to Integer because some values are Strings like "?" and "X"

class Cards(models.Model):
    # CARD NAME
    name = models.CharField(
        max_length=128,
        null=False,
        unique=True,
    )
    # AMMOUNT OF STARS
    stars = models.CharField(
        max_length=2, null=True, unique=False, default="", verbose_name="Stars"
    )

Thanks

>Solution :

You were quite close, but you forgot to use stars_integer:

Cards.objects.annotate(
    stars_integer=Cast('stars', output_field=IntegerField())
).filter(stars_integer__gte=9)

You might want to use .alias(…) ^[Django-doc] if the item does not have to appear in the SELECT clause, but only should be used for filtering:

Cards.objects.alias(
    stars_integer=Cast('stars', output_field=IntegerField())
).filter(stars_integer__gte=9)

That being said, if the field contains integer data, you better use an IntegerField in the first place, and perhaps add an index. This will also boost efficiency when searching.