[FIXED] Django Filter an String as Integer?

Issue

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.

Answered By - willeM_ Van Onsem