Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 260
Learner_Programmer Avatar asked Sep 02 '25 18:09

Learner_Programmer


2 Answers

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/dev/ref/models/database-functions/#cast

like image 55
erikreed Avatar answered Sep 04 '25 08:09

erikreed


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')
like image 37
catavaran Avatar answered Sep 04 '25 06:09

catavaran