7.10. ORM Q¶
>>> from django.db.models import Q
>>> fname = Q(firstname='Mark')
>>> lname = Q(lastname='Watney')
>>> Customer.objects.get(fname & lname)
<Customer: Mark Watney>
>>> mark = Q(firstname='Mark')
>>> watney = Q(lastname='Watney')
>>> Customer.objects.filter(mark & watney)
<QuerySet [<Customer: Mark Watney>]>
>>> fname = Q(firstname='Mark')
>>> lname = Q(lastname='Watney')
>>> Customer.objects.get(fname & lname)
<Customer: Mark Watney>
>>> Customer.objects.filter(fname & lname)
<QuerySet [<Customer: Mark Watney>]>
>>> mark = Q(firstname='Mark')
>>> watney = Q(lastname='Watney')
>>> melissa = Q(firstname='Melissa')
>>> lewis = Q(lastname='Lewis')
>>>
>>> mwatney = mark & watney
>>> mlewis = melissa & lewis
>>>
>>> Customer.objects.filter(mwatney | mlewis)
<QuerySet [<Customer: Mark Watney>, <Customer: Melissa Lewis>]>
>>> astro1 = Q(firstname='Mark', lastname='Watney')
>>> astro2 = Q(firstname='Melissa', lastname='Lewis')
>>> Customer.objects.filter(astro1 | astro2)
<QuerySet [<Customer: Mark Watney>, <Customer: Melissa Lewis>]>
>>> Customer.objects.filter(astro1|astro2 | (fname&lname))
<QuerySet [<Customer: Mark Watney>, <Customer: Melissa Lewis>]>
>>> Customer.objects.filter(astro1|astro2 | ~(fname&lname))
<QuerySet [<Customer: Mark Watney>, <Customer: Rick Martinez>, <Customer: Melissa Lewis>, <Customer: Mark Watney>, <Customer: Mark W>]>
>>> Customer.objects.filter( (astro1|astro2) & ~(fname&lname) )
<QuerySet [<Customer: Melissa Lewis>]>
>>> mark = Q(Customer__firstname='Mark')
>>> melissa = Q(Customer__firstname='Melissa')
>>>
>>> Address.objects.filter(mark|melissa)
<QuerySet [<Address: Mark Watney - NASA Pkwy, Houston, Texas USA>, <Address: Melissa Lewis - Powstańców Wielkopolskich, Krakow, malopolskie Poland>]>
>>> str(Address.objects.filter(mark|melissa).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"."firstname" = Mark OR "Customer_Customer"."firstname" = Melissa)'
7.10.1. Complex lookups with Q objects¶
from django.db.models import Q
Q(question__startswith='What')
Q(question__startswith='Who') | Q(question__startswith='What')
# WHERE question LIKE 'Who%' OR question LIKE 'What%'
Poll.objects.get(
Q(question__startswith='Who'),
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)
# SELECT * from polls WHERE question LIKE 'Who%'
# AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')
7.10.2. Q() expressions¶
from django.db.models import Q
Q(question__startswith='What')
Q(question__startswith='Who') | Q(question__startswith='What')
Q(question__startswith='Who') | ~Q(pub_date__year=2005) # negated query
Poll.objects.get(
Q(question__startswith='Who'),
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)
# Each publisher, with a separate count of books with a rating above and below 5
from django.db.models import Q
above_5 = Count('book', filter=Q(book__rating__gt=5))
below_5 = Count('book', filter=Q(book__rating__lte=5))
pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5)
pubs[0].above_5
# 23
pubs[0].below_5
# 12