6.9. ORM Lookup

6.9.1. SetUp

>>> import os; os.environ['DJANGO_SETTINGS_MODULE'] = 'myproject.settings'
>>> import django; django.setup()
>>>
>>> from shop.models import Customer, Address, Order

6.9.2. Empty

  • __isnull

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

6.9.3. Sequences

  • __in - value in a list

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

6.9.4. 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')

6.9.5. 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'))

6.9.6. 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)

6.9.7. 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)

6.9.8. 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

6.9.9. Use Cases

>>> Customer.objects.filter(firstname__startswith='M')
<QuerySet [<Customer: Mark Watney>, <Customer: Melissa Lewis>]>

>>> Customer.objects.filter(firstname__startswith='m')
<QuerySet []>

>>> Customer.objects.filter(firstname__istartswith='m')
<QuerySet [<Customer: Mark Watney>, <Customer: Melissa Lewis>]>

>>> Customer.objects.filter(firstname__endswith='a')
<QuerySet [<Customer: Melissa Lewis>]>

>>> Customer.objects.filter(firstname__contains='ar')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(firstname__in=('Mark', 'Melissa'))
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(firstname__isnull=True)
<QuerySet []>

>>> Customer.objects.filter(firstname__regex=r'^[A-Z]')
<QuerySet [<Customer: Mark Watney>, <Customer: Rick Martinez>, <Customer: Alex Vogel>, <Customer: Beth Johansson>, <Customer: Chris Beck>, <Customer: Melissa Lewis>]>

>>> print(Customer.objects.filter(firstname__regex=r'^[A-Z]').query)
SELECT "shop_customer"."id", "shop_customer"."uuid", "shop_customer"."created_user_id", "shop_customer"."created_date", "shop_customer"."modified_user_id", "shop_customer"."modified_date", "shop_customer"."is_deleted", "shop_customer"."comment", "shop_customer"."firstname", "shop_customer"."lastname", "shop_customer"."birthdate", "shop_customer"."gender", "shop_customer"."tax_number", "shop_customer"."email", "shop_customer"."phone", "shop_customer"."image", "shop_customer"."is_verified" FROM "shop_customer" WHERE "shop_customer"."firstname" REGEXP ^[A-Z]

>>> Address.objects.filter(city__iexact='kraków')
<QuerySet [<Address: Mark Watney shipping: Nipodległości 11, Kraków, Poland>]>

>>> Address.objects.filter(customer__firstname__startswith='M')
<QuerySet [<Address: Mark Watney shipping: Nipodległości 11, Kraków, Poland>]>

>>> print(Address.objects.filter(customer__firstname__startswith='M').query)
SELECT "shop_address"."id", "shop_address"."uuid", "shop_address"."created_user_id", "shop_address"."created_date", "shop_address"."modified_user_id", "shop_address"."modified_date", "shop_address"."is_deleted", "shop_address"."comment", "shop_address"."customer_id", "shop_address"."type", "shop_address"."street", "shop_address"."city", "shop_address"."postcode", "shop_address"."region", "shop_address"."country" FROM "shop_address" INNER JOIN "shop_customer" ON ("shop_address"."customer_id" = "shop_customer"."id") WHERE "shop_customer"."firstname" LIKE M% ESCAPE '\'

# %%

>>> Customer.objects.filter(address__city='Kraków')
<QuerySet [<Customer: Mark Watney>, <Customer: Melissa Lewis>]>

>>> print(Customer.objects.filter(address__city='Kraków').query)
SELECT "shop_customer"."id", "shop_customer"."uuid", "shop_customer"."created_user_id", "shop_customer"."created_date", "shop_customer"."modified_user_id", "shop_customer"."modified_date", "shop_customer"."is_deleted", "shop_customer"."comment", "shop_customer"."firstname", "shop_customer"."lastname", "shop_customer"."birthdate", "shop_customer"."gender", "shop_customer"."tax_number", "shop_customer"."email", "shop_customer"."phone", "shop_customer"."image", "shop_customer"."is_verified" FROM "shop_customer" INNER JOIN "shop_address" ON ("shop_customer"."id" = "shop_address"."customer_id") WHERE "shop_address"."city" = Kraków

# %%

>>> Customer.objects.filter(
...     pk__in=Address.objects.filter(city='Kraków').values('customer_id')
... )
<QuerySet [<Customer: Mark Watney>, <Customer: Melissa Lewis>]>

>>> print(Customer.objects.filter(
...     pk__in=Address.objects.filter(city='Kraków').values('customer_id')
... ).query)
SELECT "shop_customer"."id", "shop_customer"."uuid", "shop_customer"."created_user_id", "shop_customer"."created_date", "shop_customer"."modified_user_id", "shop_customer"."modified_date", "shop_customer"."is_deleted", "shop_customer"."comment", "shop_customer"."firstname", "shop_customer"."lastname", "shop_customer"."birthdate", "shop_customer"."gender", "shop_customer"."tax_number", "shop_customer"."email", "shop_customer"."phone", "shop_customer"."image", "shop_customer"."is_verified" FROM "shop_customer" WHERE "shop_customer"."id" IN (SELECT U0."customer_id" FROM "shop_address" U0 WHERE U0."city" = Kraków)

>>> Customer.objects.filter(firstname='Mark', lastname__startswith='W')
<QuerySet [<Customer: Mark Watney>, <Customer: Mark Watney>]>

>>> Customer.objects.filter(firstname='Mark', lastname__startswith='W')
<QuerySet [<Customer: Mark Watney>, <Customer: Mark Watney>]>

>>> Customer.objects.filter(firstname='Mark', lastname__startswith='w')
<QuerySet [<Customer: Mark Watney>, <Customer: Mark Watney>]>

>>> Customer.objects.filter(firstname='Mark', lastname__istartswith='w')
<QuerySet [<Customer: Mark Watney>, <Customer: Mark Watney>]>

>>> Customer.objects.filter(firstname='Mark', lastname__istartswith='W')
<QuerySet [<Customer: Mark Watney>, <Customer: Mark Watney>]>

>>> Customer.objects.filter(firstname='Mark', created_date__year='2000')
<QuerySet [<Customer: Mark Watney>, <Customer: Mark Watney>]>

>>> Customer.objects.filter(firstname='Mark', created_date__gt='2000-01-01')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(firstname='Mark', created_date__gt='2000-01-01 00:00:00+00:00')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(age__lt=18)
<QuerySet []>

>>> Customer.objects.filter(age__lt=50)
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(age__lte=50)
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(age__gt=50)
<QuerySet []>

>>> Customer.objects.filter(age__gte=50)
<QuerySet []>

>>> Customer.objects.filter(lastname__contains='ney')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(lastname__icontains='ney')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(birthdate='1970-01-01')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(birthdate__gt='1970-01-01')
<QuerySet []>

>>> Customer.objects.filter(birthdate__gte='1970-01-01')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(birthdate__lt='1970-01-01')
<QuerySet []>

>>> Customer.objects.filter(birthdate__lte='1970-01-01')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(birthdate__range=('1900-01-01', '2001-01-01'))
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(birthdate__in=('1970-01-01', '1969-07-21'))
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(birthdate__year='2000')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(birthdate__month='2000')
<QuerySet []>

>>> Customer.objects.filter(birthdate__month='01')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(birthdate__day='01')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(birthdate__year='2000', birthdate__month='01')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(lastname__in=[])
<QuerySet []>

>>> Customer.objects.filter(lastname__in=['Watney', 'Lewis'])
<QuerySet [<Customer: Melissa Lewis>, <Customer: Mark Watney>]>

>>> DATA = [1,2,3]
>>> Customer.objects.filter(pk__in=DATA)
<QuerySet [<Customer: Mark Watney>, <Customer: Rick Martinez>, <Customer: Melissa Lewis>]>

>>> Customer.objects.filter(id__in=DATA)
<QuerySet [<Customer: Mark Watney>, <Customer: Rick Martinez>, <Customer: Melissa Lewis>]>

>>> str(Customer.objects.filter(id__in=DATA).query)
'SELECT "Customer_Customer"."id", "Customer_Customer"."created_date", "Customer_Customer"."created_author_id", "Customer_Customer"."modified_date", "Customer_Customer"."modified_author_id", "Customer_Customer"."firstname", "Customer_Customer"."lastname", "Customer_Customer"."salary", "Customer_Customer"."job", "Customer_Customer"."birthdate", "Customer_Customer"."age", "Customer_Customer"."gender", "Customer_Customer"."is_adult", "Customer_Customer"."weight", "Customer_Customer"."height", "Customer_Customer"."email", "Customer_Customer"."homepage", "Customer_Customer"."phone_country_code", "Customer_Customer"."phone_number", "Customer_Customer"."picture", "Customer_Customer"."attachment", "Customer_Customer"."notes" FROM "Customer_Customer" WHERE "Customer_Customer"."id" IN (1, 2, 3)'

>>> Customer.objects.filter(groups__name__in=['astronauts', 'scientists'])
<QuerySet [<Customer: Mark Watney>, <Customer: Alex Vogel>, <Customer: Rick Martinez>, <Customer: Melissa Lewis>, <Customer: Mark Watney>, <Customer: Alex Vogel>]>

>>> Customer.objects.filter(groups__name__in=['astronauts', 'scientists']).distinct()
<QuerySet [<Customer: Mark Watney>, <Customer: Alex Vogel>, <Customer: Rick Martinez>, <Customer: Melissa Lewis>]>

>>> Customer.objects.filter(birthdate__gte='1969-07-21', birthdate__lte='1970-01-01')
<QuerySet [<Customer: Mark Watney>]>

>>> str(Address.objects.filter(Customer__lastname__contains='ney').query)
'SELECT "Customer_address"."id", "Customer_address"."Customer_id", "Customer_address"."type", "Customer_address"."street", "Customer_address"."house", "Customer_address"."apartment", "Customer_address"."postcode", "Customer_address"."city", "Customer_address"."region", "Customer_address"."country" FROM "Customer_address" INNER JOIN "Customer_Customer" ON ("Customer_address"."Customer_id" = "Customer_Customer"."id") WHERE "Customer_Customer"."lastname" LIKE %ney% ESCAPE \'\\\''

>>> str(Address.objects.filter(Customer__lastname__startswith='Wat').query)
'SELECT "Customer_address"."id", "Customer_address"."Customer_id", "Customer_address"."type", "Customer_address"."street", "Customer_address"."house", "Customer_address"."apartment", "Customer_address"."postcode", "Customer_address"."city", "Customer_address"."region", "Customer_address"."country" FROM "Customer_address" INNER JOIN "Customer_Customer" ON ("Customer_address"."Customer_id" = "Customer_Customer"."id") WHERE "Customer_Customer"."lastname" LIKE Wat% ESCAPE \'\\\''

>>> Customer.objects.filter(firstname='Mark')
<QuerySet [<Customer: Mark Watney>, <Customer: Mark Watney>]>

>>> Customer.objects.filter(firstname='Mark').exclude(lastname='W')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(lastname__endswith='ney')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(lastname__iendswith='ney')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(lastname__startswith='Wat')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(lastname__istartswith='Wat')
<QuerySet [<Customer: Mark Watney>]>

>>> Customer.objects.filter(age__isnull=True)
<QuerySet [<Customer: Rick Martinez>, <Customer: Melissa Lewis>, <Customer: Mark Watney>, <Customer: Mark Watney>]>

>>> Address.objects.filter(Customer__age__isnull=True)
<QuerySet [<Address: Melissa Lewis - Powstańców Wielkopolskich, Krakow, malopolskie Poland>]>

>>> Customer.objects.filter(firstname='Mark')
<QuerySet [<Customer: Mark Watney>, <Customer: Mark Watney>]>

>>> Address.objects.filter(Customer__in=Customer.objects.filter(firstname='Mark'))
<QuerySet [<Address: Mark Watney - NASA Pkwy, Houston, Texas USA>]>

>>> str(Address.objects.filter(Customer__in=Customer.objects.filter(firstname='Mark')).query)
'SELECT "Customer_address"."id", "Customer_address"."Customer_id", "Customer_address"."type", "Customer_address"."street", "Customer_address"."house", "Customer_address"."apartment", "Customer_address"."postcode", "Customer_address"."city", "Customer_address"."region", "Customer_address"."country" FROM "Customer_address" WHERE "Customer_address"."Customer_id" IN (SELECT U0."id" FROM "Customer_Customer" U0 WHERE U0."firstname" = Mark)'

>>> Customer.objects.filter(lastname='XYZ').exists()
False

>>> Customer.objects.filter(lastname='Watney').exists()
True

>>> Customer.objects.all().last()
<Customer: Mark Watney>

>>> Customer.objects.all().first()
<Customer: Mark Watney>

>>> Address.objects.filter(Customer__lastname='Watney')
<QuerySet [<Address: Mark Watney - NASA Pkwy, Houston, Texas USA>]>

>>> str(Address.objects.filter(Customer__lastname='Watney').query)
'SELECT "Customer_address"."id", "Customer_address"."Customer_id", "Customer_address"."type", "Customer_address"."street", "Customer_address"."house", "Customer_address"."apartment", "Customer_address"."postcode", "Customer_address"."city", "Customer_address"."region", "Customer_address"."country" FROM "Customer_address" INNER JOIN "Customer_Customer" ON ("Customer_address"."Customer_id" = "Customer_Customer"."id") WHERE "Customer_Customer"."lastname" = Watney'