8.12. ORM Annotate

8.12.1. SetUp

>>> from django.db.models.functions import Concat
>>> from django.db.models import Value

>>> Customer.objects.all().values('firstname', 'lastname')
<QuerySet [{'firstname': 'Mark', 'lastname': 'Watney'}, {'firstname': 'Rick', 'lastname': 'Martinez'}, {'firstname': 'Melissa', 'lastname': 'Lewis'}, {'firstname': 'Mark', 'lastname': 'Watney'}, {'firstname': 'Mark', 'lastname': 'W'}]>

>>> Customer.objects.all().annotate(fullname=Concat('firstname', 'lastname'))
<QuerySet [<Customer: Mark Watney>, <Customer: Rick Martinez>, <Customer: Melissa Lewis>, <Customer: Mark Watney>, <Customer: Mark W>]>

>>> Customer.objects.all().annotate(fullname=Concat('firstname', 'lastname')).values('fullname')
<QuerySet [{'fullname': 'MarkWatney'}, {'fullname': 'RickMartinez'}, {'fullname': 'MelissaLewis'}, {'fullname': 'MarkWatney'}, {'fullname': 'MarkW'}]>

>>> Customer.objects.all().annotate(fullname=Concat('firstname', '', 'lastname')).values('fullname')
Traceback (most recent call last):
django.core.exceptions.FieldError: Cannot resolve keyword '' into field. Choices are: address, age, attachment, birthdate, created_author, created_author_id, created_date, email, firstname, gender, height, homepage, id, is_adult, job, lastname, modified_author, modified_author_id, modified_date, notes, phone_country_code, phone_number, picture, salary, weight

>>> Customer.objects.all().annotate(fullname=Concat('firstname', Value(''), 'lastname')).values('fullname')
<QuerySet [{'fullname': 'MarkWatney'}, {'fullname': 'RickMartinez'}, {'fullname': 'MelissaLewis'}, {'fullname': 'MarkWatney'}, {'fullname': 'MarkW'}]>

>>> Customer.objects.all().annotate(fullname=Concat('firstname', Value(' '), 'lastname')).values('fullname')
<QuerySet [{'fullname': 'Mark Watney'}, {'fullname': 'Rick Martinez'}, {'fullname': 'Melissa Lewis'}, {'fullname': 'MarkWatney'}, {'fullname': 'Mark W'}]>

>>> Customer.objects.all().annotate(fullname=Concat('firstname', Value(' '), 'lastname')).values('fullname')
<QuerySet [{'fullname': 'Mark Watney'}, {'fullname': 'Rick Martinez'}, {'fullname': 'Melissa Lewis'}, {'fullname': 'MarkWatney'}, {'fullname': 'Mark W'}]>

>>> result = Customer.objects.all().annotate(fullname=Concat('firstname', Value(' '), 'lastname')).values('fullname')
>>> list(result)
[{'fullname': 'Mark Watney'}, {'fullname': 'Rick Martinez'}, {'fullname': 'Melissa Lewis'}, {'fullname': 'MarkWatney'}, {'fullname': 'Mark W'}]

>>> result = Customer.objects.all().annotate(fullname=Concat('firstname', Value(' '), 'lastname')).values_list('fullname')
>>> result
<QuerySet [('Mark Watney',), ('Rick Martinez',), ('Melissa Lewis',), ('Mark Watney',), ('Mark W',)]>

>>> result = Customer.objects.all().annotate(fullname=Concat('firstname', Value(' '), 'lastname')).values_list('fullname', flat=True)
>>> result
<QuerySet ['Mark Watney', 'Rick Martinez', 'Melissa Lewis', 'Mark Watney', 'Mark W']>

>>> list(result)
['Mark Watney', 'Rick Martinez', 'Melissa Lewis', 'Mark Watney', 'Mark W']

8.12.2. Assignments

# doctest: +SKIP_FILE
# %% About
# - Name: Database ORM F
# - Difficulty: easy
# - Lines: 1
# - Minutes: 3

# %% 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

# %% English
# 0. Use `myproject.shop`
# 1. Use queryset `DATA` containing all Customers
# 2. Modify queryset to add field `fullname` by concatenating field `firstname`, space and `lastname`
# 3. Present the result as `list[dict]` (`.values('fullname')`)
# 4. Use `F`, `Concat` and `Value`

# %% Polish
# 0. Użyj `myproject.shop`
# 1. Użyj queryset `DATA` zawierający wszystkich Klientów
# 2. Zmodyfikuj queryset aby dodać pole `fullname` przez połączenie pola `firstname`, spacji i `lastname`
# 3. Wynik przedstaw jako `list[dic]` (`.values('fullname')`)
# 4. Użyj `F`, `Concat` i `Value`

# %% Example
# >>> result
# <QuerySet [{'fullname': 'Mark Watney'}, {'fullname': 'Melissa Lewis'}, {'fullname': 'Rick Martinez'}, {'fullname': 'Alex Vogel'}, {'fullname': 'Beth Johanssen'}, {'fullname': 'Chris Beck'}]>

# %% Hints
# - `F()`
# - `Concat()`
# - `Value()`

# %% Doctests
"""
>>> import sys; sys.tracebacklimit = 0

>>> assert sys.version_info >= (3, 12), \
'Python has an is invalid version; expected: `3.12` or newer.'

>>> assert 'result' in globals(), \
'Variable `result` is not defined; assign result of your program to it.'

>>> assert result is not Ellipsis, \
'Variable `result` has an invalid value; assign result of your program to it.'

>>> from django.db.models import QuerySet
>>> assert type(result) is QuerySet, \
'Variable `result` has an invalid type; expected: `int`.'

>>> from pprint import pprint
>>> pprint(result)
<QuerySet [{'fullname': 'Mark Watney'}, {'fullname': 'Melissa Lewis'}, {'fullname': 'Rick Martinez'}, {'fullname': 'Alex Vogel'}, {'fullname': 'Beth Johanssen'}, {'fullname': 'Chris Beck'}]>
"""

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

# %% Imports
import os; os.environ['DJANGO_SETTINGS_MODULE'] = 'myproject.settings'
import django; django.setup()
from django.db.models import F, Value
from django.db.models.functions import Concat
from shop.models import Customer

# %% Types
result: int

# %% Data
DATA = Customer.objects.all()

# %% Result
result = ...