Filter data based on sum of two fields

Question

I have following Django model

class OfflineExamResult(models.Model):
  """ Model to store result of offline exam """
  batch = models.CharField(max_length=40, null=True, blank=True)
  date = models.CharField(max_length=20, null=True, blank=True)
  full_marks = models.IntegerField(default=80)
  rank = models.CharField(max_length=5, null=True, blank=True)
  uid1 = models.CharField(max_length=10, null=True, blank=True)
  uid2 = models.CharField(max_length=10, null=True, blank=True)
  name = models.CharField(max_length=30, null=True, blank=True)
  marks = models.CharField(max_length=5, null=True, blank=True)

The students in my class have been allotted a roll number which is sum (string sum) of uid1 and uid2. I want to create a search field so that students enter their roll number and get their result which will happen, only if I am able to filter objects after comparing roll number to sum of uid1 and uid2.

score 4 · Answer 1 · answered Jul 04 '18 at 06:14

4

You need to use F() with annotation().

from django.db.models import F

students = OfflineExamResult.objects.annotate(roll=F('uid1') + F('uid2')).\
filter(roll=given_roll_number)

answered Jul 04 '18 at 06:14

Astik Anand

12,757
9
41
51

The code is running without any error, it just not returning any result at all. – Ujjawal Anand Jul 04 '18 at 07:20
Do you have data in db, and have you tried printing the `students` object. – Astik Anand Jul 04 '18 at 07:22
Yes, I do have data in db, have tried the code in Django shell, like I told you - it return an empty list. – Ujjawal Anand Jul 04 '18 at 07:25
The code should work, just try adding some more data and try. – Astik Anand Jul 04 '18 at 08:45

score 2 · Answer 2 · answered Jul 04 '18 at 09:51

Since your uid1 and uid2 are CharFields you need to use Cast database function if you want to perform arithmetical operations among CharFields.

This should work:

from django.db.models import F, IntegerField
from django.db.models.functions import Cast

students = OfflineExamResult.objects.annotate(uid1_int=Cast('uid1', IntegerField()), uid2_int=Cast('uid2', IntegerField())).annotate(roll=F('uid1_int') + F('uid2_int')).filter(roll=roll_number)

P.S.

I used IntegerField field in my example but if you have decimal numbers in the database you should use FloatField instead.

Filter data based on sum of two fields

2 Answers2