How do i cast char to integer while querying in django ORM?

Question

Recently started using Django ORM.I want to execute this query

 select student_id from students where student_id like "%97318%" order by CAST(student_id as UNSIGNED) desc;

where student_id is a CharField which I want as integer for querying. I tried with

  students.objects.filter(student_id__contains "97318").order('-student_id')

works fine. But don't know and couldn't find how to cast "student_id" to int like the actual MySQL query mentioned above with "Django ORM". should I use raw query or is there a way out? Let me know your suggestions.

You're missing `=` on the `filter(...)`. If raw query works for you, I wonder is there any special reason you *have to* use ORM? — Anzel, Jan 23 '15 at 01:28
@Anzel You can't order strings by their number values. `"2" > "11"` but `2 < 11` — catavaran, Jan 23 '15 at 01:31
@catavaran, I agreed, but still doesn't explain why this *has to* be in ORM, whereas raw sql can perform just fine — Anzel, Jan 23 '15 at 01:33
@Anzel raw sql is not required here. `extra()` can do the job just fine. — catavaran, Jan 23 '15 at 01:36

score 63 · Accepted Answer · answered Dec 31 '16 at 01:33

63

An updated alternative without requiring the use of extra is the cast function (new in Django 1.10):

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Cast
>>> Value.objects.create(integer=4)
>>> value = Value.objects.annotate(as_float=Cast('integer', FloatField())).get()> 
>>> print(value.as_float)
4.0

From https://docs.djangoproject.com/en/1.10/ref/models/database-functions/#cast

answered Dec 31 '16 at 01:33

erikreed

1,447
1
16
21

1

This seems to be latest working solution, hence accepting it:) – Learner_Programmer Aug 06 '19 at 11:24

score 13 · Answer 2 · answered Jan 23 '15 at 01:29

13

Use queryset's extra() method:

students.objects.filter(student_id__contains="97318") \
                .extra({'stident_id_uint': "CAST(student_id as UNSIGNED)"}) \
                .order_by('-student_id_uint')

answered Jan 23 '15 at 01:29

catavaran

44,703
8
98
85

2

Since Django 1.11 extra is marked as deprecated, so be wary of using it – Andrew Barrett Jun 28 '19 at 02:47

score 9 · Answer 3 · answered Mar 13 '17 at 20:08

I have tried extra() and annotate() to CAST, but they did not work well with related fields and generates JOINS resulting unexpected queryset sometimes.

What I ended up was to create a Custom Lookup.

The documentation is few but can be found at here and here

Here is my example:

@Field.register_lookup
class IntegerValue(Transform):
    # Register this before you filter things, for example in models.py
    lookup_name = 'int'  # Used as object.filter(LeftField__int__gte, "777")
    bilateral = True  # To cast both left and right

    def as_sql(self, compiler, connection):
        sql, params = compiler.compile(self.lhs)
        sql = 'CAST(%s AS UNSIGNED)' % sql
        return sql, params

Then below should work:

students.objects.filter(student_id__int__gte="97318").order('-student_id')

where to put this lookup file? – Anuj TBE Aug 16 '18 at 12:41 — Anuj TBE, Aug 16 '18 at 12:41

score 2 · Answer 4 · answered Feb 01 '21 at 18:46

Use cast from Django. If you are working with Django CBV (class based view) you can do this way:

models.py:

class Document(TenantAwareModel):
    document_data = JSONField(null=True)

views.py:

from django.db.models import TextField
from django.db.models.functions import Cast

def get_queryset(self):
    document = Document.objects.annotate(document_data_as_text=Cast('document_data', TextField()))
    user = self.request.user
    tenant = self.request.user.tenant
    return document.filter(tenant=tenant)

Then, in your method you can use document_data_as_text as a normal field to filter by it:

def list(self, request, *args, **kwargs):
    document_part_filter_param = request.query_params.get("documentPart")
    if document_part_filter_param:
        queryset = queryset.filter(document_data_as_text__unaccent__icontains=document_part_filter_param)

How do i cast char to integer while querying in django ORM?

4 Answers4

Linked