7.3. ORM Lookup

7.3.1. Empty

  • __isnull

>>> Customer.objects.filter(is_active__isnull=True)

7.3.2. Sequences

  • __in - value in a list

>>> Customer.objects.filter(id__in=[1,4,7])  
>>> Customer.objects.filter(lastname__in=['Watney', 'Lewis', 'Martinez'])

7.3.3. Strings

  • __exact - value exactly matches text (case sensitive) - this is default behavior if no lookup is provided

  • __iexact - value exactly matches text (case insensitive)

  • __contains - value contains text (case sensitive)

  • __icontains - value contains text (case insensitive)

  • __endswith - value ends with text (case sensitive)

  • __iendswith - value ends with text (case insensitive)

  • __startswith - value starts with text (case sensitive)

  • __istartswith - value starts with text (case insensitive)

Case sensitive:

>>> Customer.objects.filter(lastname__exact='Watney')  
>>> Customer.objects.filter(lastname__contains='tne')  
>>> Customer.objects.filter(lastname__endswith='ney')  
>>> Customer.objects.filter(lastname__startswith='Wat')

Case insensitive:

>>> Customer.objects.filter(lastname__iexact='WATNEY')  
>>> Customer.objects.filter(lastname__icontains='TNE')  
>>> Customer.objects.filter(lastname__iendswith='NEY')  
>>> Customer.objects.filter(lastname__istartswith='WAT')

7.3.4. Numeric, Dates

  • __eq - value equal to

  • __gt - value greater than

  • __gte - value greater or equal than

  • __lt - value less than

  • __lte - value less or equal than

  • __range - value between lower and upper bounds (upper bound included)

>>> Customer.objects.filter(birthdate__gte='2000-01-01')  
>>> Customer.objects.filter(birthdate__lt='2001-01-01')  

>>> Customer.objects.filter(birthdate__gte='2000-01-01', birthdate__lt='2001-01-01')  
>>> Customer.objects.filter(birthdate__range=('2000-01-01', '2001-01-01'))

7.3.5. Dates

  • __year - year part of a datetime or date value

  • __month - month part of a datetime or date value

  • __day - day part of a datetime or date value

  • __hour - hour part of a datetime or time value

  • __minute - minute part of a datetime or time value

  • __second - second part of a datetime or time value

  • __microsecond - microsecond part of a datetime or time value

>>> Customer.objects.filter(birthdate__year=2000)  
>>> Customer.objects.filter(birthdate__month=1)  
>>> Customer.objects.filter(birthdate__day=1)  
>>> Customer.objects.filter(birthdate__year=2000, birthdate__month=1)

7.3.6. Relationships

>>> Order.objects.filter(customer__lastname='Watney')  
>>> Order.objects.filter(customer__lastname__in=['Watney', 'Lewis', 'Martinez'])  
>>> Order.objects.filter(customer__addresses__city__in=['Houston', 'Cologne'])  

>>> 
... Order.objects.exclude(
...     customer__firstname='Mark',
...     customer__birthdate__year=2000,
... )

>>> 
... Order.objects.exclude(
...     user__in=Customer.objects.filter(
...         firstname='Mark',
...         birthdate__year=2000,
...     )
... )

>>> 
... excluded = Customer.objects.filter(firstname='Mark', birthdate__year=2000)
... Order.objects.exclude(user__in=excluded)

7.3.7. The pk Lookup

>>> Customer.objects.get(id__exact=14)  # Explicit form  
>>> Customer.objects.get(id=14)         # __exact is implied  
>>> Customer.objects.get(pk=14)         # pk implies id__exact

Get customers with id 1, 4 and 7:

>>> Customer.objects.filter(pk__in=[1,4,7])

Get all customers with id > 14:

>>> Customers.objects.filter(pk__gt=14)

pk lookups also work across joins.

>>> Order.objects.filter(customer__id__exact=3)  # Explicit form  
>>> Order.objects.filter(customer__id=3)         # __exact is implied  
>>> Order.objects.filter(customer__pk=3)         # __pk implies __id__exact