7.9. ORM Filter¶

.all()
.complex_filter()
.extra()
.filter()
.reverse()
.union()

7.9.1. Filter by field¶

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

7.9.2. Filter by multiple fields¶

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

7.9.3. Filter by field lookup¶

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

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

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

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

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

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

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

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

>>> 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 W>]>

>>> 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 W>]>

>>> 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 W>]>

>>> 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 W>

>>> 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'

7.9.4. Compose¶

>>> q = Customer.objects
>>> q = q.filter(firstname='Mark')
>>> q = q.filter(created_date__lte=datetime.now(timezone.utc))
>>> q = q.exclude(lastname='W')
>>> q = q.distinct()
>>> q = q.order_by('lastname', 'firstname')
>>> q
<QuerySet [<Customer: Mark Watney>]>

7.9.5. Chain¶

>>> Customer.objects \
...        .filter(firstname='Mark') \
...        .filter(created_date__gte='2021-09-07 00:00:00+00:00') \
...        .exclude(lastname='W') \
...        .distinct() \
...        .order_by('lastname', 'firstname')

>>> from datetime import datetime, timezone
>>>
>>>
>>> Customer.objects \
...        .filter(firstname='Mark') \
...        .filter(created_date__lte=datetime.now(timezone.utc)) \
...        .exclude(lastname='W') \
...        .distinct() \
...        .order_by('lastname', 'firstname')
<QuerySet [<Customer: Mark Watney>]>

7.9.6. GetItem¶

>>> Customer.objects.filter(firstname='Mark')[1]
<Customer: Mark W>

7.9.7. Slice¶

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

>>> Customer.objects.filter(firstname='Mark')[1:5]
<QuerySet [<Customer: Mark W>]>

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