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

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

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

6.10.3. Union

6.10.4. Alternative

6.10.5. Negation

6.10.6. Multiple Queries

6.10.7. Assignments

# doctest: +SKIP_FILE
# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database ORM Q
# - Difficulty: easy
# - Lines: 1
# - Minutes: 3

# %% English
# 0. Use `myproject.shop`
# 1. Define variable `result` with result of ORM call for:
#    Select all Customers
#    Where `firstname` is `Mark` And `lastname` is `Watney`
#    Or `firstname` is `Melissa` And `lastname` is `Lewis`
# 2. Use `Q` object

# %% Polish
# 0. Użyj `myproject.shop`
# 1. Zdefiniuj zmienną `result` z wynikiem zapytania ORM dla:
#    Wybierz wszystkich klientów
#    Gdzie `firstname` is `Mark` And `lastname` is `Watney`
#    Lub `firstname` is `Melissa` And `lastname` is `Lewis`
# 2. Użyj obiektu `Q`

# %% Hints
# - `Q()`
# - `|` - or
# - `.filter()`

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> assert sys.version_info >= (3, 10), \
'Python 3.10+ required'

>>> assert result is not Ellipsis, \
'Assign your result to variable `result`'

>>> from django.db.models.query import QuerySet
>>> assert type(result) is QuerySet, \
'Variable `result` has invalid type, should be QuerySet'

>>> from pprint import pprint
>>> pprint(result)
<QuerySet [<Customer: Mark Watney>, <Customer: Melissa Lewis>]>
"""

# Required for Django to work
import os; os.environ['DJANGO_SETTINGS_MODULE'] = 'myproject.settings'
import django; django.setup()

from django.db.models import Q
from shop.models import Customer


# Define variable `result` with result of ORM call for:
# Select all Customers
# Where `firstname` is `Mark` And `lastname` is `Watney`
# Or `firstname` is `Melissa` And `lastname` is `Lewis`
# Use `Q` object
# type: QuerySet
result = ...