Django QuerySet Field Lookups: Unlocking Advanced Data Filtering Techniques for Your Django Application

Django QuerySet Field Lookups

Field lookups are how you specify the meat of an SQL WHERE clause. They’re specified as keyword arguments to the QuerySet methods filter(), exclude() and get(). They help us to apply conditions while retrieving the records.

syntax: (fieldname__lookuptype = value)
eg: model.objects.filter(id__gt=5)

To understand the field lookups in depth and how to use them in your Django application lets create below model in models.py file.

#models.py 
class Player(models.Model):
    jerseyno = models.IntegerField(null=False,unique=True)
    name = models.CharField(max_length=50)
    city = models.CharField(max_length=100)
    totalruns = models.IntegerField()
    birthdate = models.DateField()
    testdebut = models.DateTimeField()

Considering we have above data in our database based on our Player model, lets try to retrieve the data using field lookups.

1. exact: Matches the exact values in field.

eg: Player.objects.filter(city__exact=’Delhi’)

2. iexact: Matches the exact values in field but ignores the case sensitivity.

eg: Player.objects.filter(city__iexact=’Delhi’)

3. contains: Matches field values that contains given value in it. Use ‘icontains’ to ignore case sensitivity.

eg: Player.objects.filter(city__contains=’hi’)

4. in: Matches field value in list of values.

eg: Player.objects.filter(jerseyno__in=[7,10,18])

5. gt: Matches all the field values which are greater than given value. Use ‘gte’ for greater than or equals to.

eg: Player.objects.filter(totalruns__gt=18820)

eg: Player.objects.filter(totalruns__gte=18820)

6. lt: Matches all the field values which are less than given value. Use ‘lte’ for less than or equals to.

eg: Player.objects.filter(totalruns__lt=18820)

eg: Player.objects.filter(totalruns__lte=18820)

7. startswith: Matches field values that starts with given value. Use ‘istartswith’ to ignore case sensitivity.

eg: Player.objects.filter(name__startswith=’M’)

8. endswith: Matches field values that ends with given value. Use ‘iendswith’ to ignore case sensitivity.

eg: Player.objects.filter(name__endswith=’i’)

9. range: Matches field dates that comes under given range. Works with both date and datetime.

eg: Player.objects.filter(birthdate__range=(‘1980–01–01’,’1985–01–01'))

10. date: Matches field dates with given date. Works only with datetime field.

eg: Player.objects.filter(testdebut__date=’2005–12–02')

11. year: Matches year from field dates with given year. Works with both date and datetime.

eg: Player.objects.filter(birthdate__year=1981)

12. month: Matches month from field dates with given month. Works with both date and datetime.

eg: Player.objects.filter(birthdate__month=4)

13. day: Matches day from field dates with given date. Works well with both date and datetime.

eg: Player.objects.filter(testdebut__day=20)

14. week: Matches week of field dates with given week number (Normally 52 weeks). Works with both date and datetime fields.

eg: Player.objects.filter(birthdate__week=18)

15. week_day: Matches week day of field date with given week day (usually starts with sunday as 1). Works well with both date and datetime field.

eg: Player.objects.filter(birthdate__week_day=3)

16. quarter: Matches quarter of field dates with given quarter. Works with both date and datetime.

eg: Player.objects.filter(birthdate__quarter=4)

17. time: Matches time of field with given time. Only works with datetime field.

eg: Player.objects.filter(testdebut__time=’09:33:55')

18. hour: Matches hour of the field time with given hour. Only works with datetime field.

eg: Player.objects.filter(testdebut__hour=10)

19. minute: Matches minute of the field time with given minute. Only works with datetime field.

eg: Player.objects.filter(testdebut__minute=49)

20. second: Matches second of the field time with given second. Only works with datetime field.

eg: Player.objects.filter(testdebut__second=55)

21. isnull: Matches null field values. Return all not null values if False.

eg: Player.objects.filter(jerseyno__isnull=False)

22. regex: Matches the field value to described regular expression. Use ‘iregex’ to ignore case sensitivity.

eg: Player.objects.filter(name__regex=’^.a’)

23. Combining lookups: We can combine conditional lookups like gt, gte, lt, lte along with other field lookups.

eg: Player.objects.filter(testdebut__day__lt=15)

eg: Player.objects.filter(birthdate__year__lte=1981)

eg: Player.objects.filter(birthdate__month__gt=7)

eg: Player.objects.filter(birthdate__month__gte=7)

24. Combining QuerySets: We can combine two or more QuerySets to produce required results. The latter QuerySet will apply lookup on results of earlier QuerySet.

eg: Player.objects.filter(jerseyno__in=[7,10,45]).filter(birthdate__month=4)

Above all lookups can be used with both get and exclude methods, Just note that get method will raise an exception if it returns more than 1 record.

If you want to learn more about QuerySets and FieldLookups head over to Django documentation here.