W E I R D A N D W O N D E R F U L T H I N G S
T O D O W I T H T H E O R M
M A R C TA M LY N
Slide 2
Slide 2 text
Y O U C A N I N T E R A C T W I T H Y O U R
D ATA B A S E
T H E W E I R D
Slide 3
Slide 3 text
Y O U D O N ' T H AV E T O W R I T E S Q L
T H E W O N D E R F U L
Slide 4
Slide 4 text
T H A N K Y O U
Slide 5
Slide 5 text
H I !
• Marc Tamlyn
• Django core developer
• Technical lead at Photocrowd
Slide 6
Slide 6 text
A TA L K I N T H R E E A C T S
• Querying your data
• Encapsulating logic
• Advanced Prefetching
Slide 7
Slide 7 text
D E F I N I N G O U R P R O J E C T
• Real world motivation
• Car dealership administration system
• New car sales, used car sales, servicing, repairs…
• LOTS of deliberate naivety!
Slide 8
Slide 8 text
M O D E L S
• Department, Employee, Customer
• NewVehicle, UsedVehicle, OwnedVehicle
• No core Vehicle model
• Task
• Many small models for specific processes
Slide 9
Slide 9 text
Q U E RY I N G Y O U R D ATA
A C T I
Slide 10
Slide 10 text
F I N D A U S E D C A R F R O M A C E R TA I N Y E A R
• UK registration plates
• \w\w17\w\w\w
• \w\w66\w\w\w
• \w\w16\w\w\w
• …
Slide 11
Slide 11 text
A N I C E A P I
>>> UsedVehicle.objects.filter(
... registration_plate__year=2007,
... )
Slide 12
Slide 12 text
D E F I N E T H E L O O K U P
class RegistrationPlateYearLookup(Lookup):
lookup_name = 'year'
def as_sql(self, compiler, connection):
lhs_sql, lhs_args = self.process_lhs(
compiler, connection,
)
rhs1 = str(self.rhs)[-2:]
rhs2 = str(int(rhs1) + 50)
Slide 13
Slide 13 text
D E F I N E T H E L O O K U P
return (
(
"(%s ~ '^\w\w%s\w\w\w$'"
" OR %s ~ '^\w\w%s\w\w\w$')"
) % (
lhs_sql, rhs1, lhs_sql, rhs2,
),
list(lhs_args) + list(lhs_args),
)
Slide 14
Slide 14 text
R E G I S T E R I T
CharField.register_lookup(
RegistrationPlateYearLookup
)
Slide 15
Slide 15 text
Q U E RY !
>>> UsedVehicle.objects.filter(
... registration_plate__year=2007,
... )
[, , …]
Slide 16
Slide 16 text
A D D I T I O N A L VA L I D AT I O N
>>> UsedVehicle.objects.filter(
... registration_plate__year=27,
... )
Traceback (most recent call last):
File "", line 1, in
ValueError: Year "27" is not valid. Did you mean
to query Chariot?
Slide 17
Slide 17 text
O R D E R C A R S B Y R E G I S T R AT I O N Y E A R
• 17, 66, 16, 65, 15, 64, 14, …, 52, 02, 51, 01
• Z\d\d\d?\w\w\w, Y\d\d\d?\w\w\w, X\d\d\d?\w\w\w…
• \w\w\w\d\d\d?Z, \w\w\w\d\d\d?Y, \w\w\w\d\d\d?X…
Slide 18
Slide 18 text
A N I C E A P I
>>> UsedVehicle.objects.order_by(
... PlateYear('registration_plate').desc(),
... )
Slide 19
Slide 19 text
W E H AV E T H E T E C H N O L O G Y
>>> UsedVehicle.objects.annotate(
... year=Case(
When(
registration_plate__regex='…',
SubString(…),
),
When(…),
…
),
... ).order_by('year')
Slide 20
Slide 20 text
L E T ' S C H E AT
def PlateYear(field):
return Case(…)
Slide 21
Slide 21 text
E N C A P S U L AT I N G L O G I C
A C T I I
Slide 22
Slide 22 text
S E L L I N G A U S E D C A R
• Lots of different transaction modes
• Cash sale
• Part exchange
• Hire purchase
• …
Slide 23
Slide 23 text
A P I
# Common utilities
vehicle.complete_purchase()
# Specific utilities
vehicle.perform_credit_check()
vehicle.process_part_exchange()
Slide 24
Slide 24 text
N I C E R A P I
# Hire purchase
vehicle.transaction.complete_purchase()
vehicle.transaction.perform_credit_check()
# Part exchange
vehicle.transaction.complete_purchase()
vehicle.transaction.process_part_exchange()
Slide 25
Slide 25 text
H O W T O A C H I E V E T H I S
• Custom field
• Stores an identifying string
• Resolves to a bound class instance when loaded
Slide 26
Slide 26 text
T H E C L A S S
class BaseTransaction(object):
def __init__(self, vehicle):
self.vehicle = vehicle
def complete_transaction(self):
# do stuff here…
Slide 27
Slide 27 text
T H E C L A S S
class HirePurchase(BaseTransaction):
slug = 'hire-purchase'
def perform_credit_check(self):
# do stuff here…
Slide 28
Slide 28 text
T H E F I E L D
class TransactionTypeField(CharField):
def __init__(self, **kwargs):
kwargs.setdefault('max_length', 100)
super().__init__(**kwargs)
Slide 29
Slide 29 text
S E N D VA L U E S T O T H E D ATA B A S E
class TransactionTypeField(CharField):
…
def get_prep_value(self):
if value is None:
return value
return value.slug
Slide 30
Slide 30 text
G E T B A C K F R O M T H E D ATA B A S E
class TransactionTypeField(CharField):
…
def from_db_value(self, value, expression,
connection, context):
if value is None:
return value
return TransactionDescriptor(value)
Slide 31
Slide 31 text
T H E D E S C R I P T O R
class TransactionDescriptor(object):
def __init__(self, value):
self.value = value
def __get__(self, instance, cls=None):
cls = get_class(self.value)
return cls(instance)
Slide 32
Slide 32 text
U S E I T
>>> type(vehicle.transaction)
HirePurchase
>>> vehicle.transaction.vehicle is vehicle
True
>>> vehicle.transaction = PartExchange
# Could be implemented via the __set__
Slide 33
Slide 33 text
W H Y B O T H E R
• Won't need it for the first 1-2 logical chunks
• Encapsulates complex, different processes applicable
to instances of the same model
• Testable
• Keeps model class size free from large numbers of
methods full of elifs
Slide 34
Slide 34 text
A D VA N C E D P R E F E T C H I N G
A C T I I I
Slide 35
Slide 35 text
TA S K L O G G I N G
• Two main views
• What has happened to all the cars in for a service
today?
• What have the mechanics done today?
Slide 36
Slide 36 text
M O D E L
class Task(models.Model):
employee = models.ForeignKey(Employee)
vehicle = RegistrationPlateField()
action = models.CharField()
notes = models.TextField()
timestamp = models.DateTimeField()
Slide 37
Slide 37 text
A P I S
department.employee_set.prefetch_related(
PrefetchUserTasks(today_only=True),
)
OwnedVehicle.objects.filter(
service_date=today,
).prefetch_related(
PrefetchVehicleTasks(),
)
Slide 38
Slide 38 text
T H E U S E R C A S E
• A wrapper on top of the Prefetch object
• Easy enough because we have a ForeignKey
relationship
Slide 39
Slide 39 text
T H E U S E R C A S E
def PrefetchUserTasks(Prefetch):
def __init__(self, today_only=False):
qs = Task.objects.all()
if today_only:
qs = qs.filter(…)
super().__init__(
'prefetched_tasks', qs, 'task_set',
)
Slide 40
Slide 40 text
A L L D O N E !
>>> peeps = dept.employee_set.prefetch_related(
... PrefetchUserTasks(today_only=True),
... )
>>> peeps[0].prefetched_tasks
[, ]
Slide 41
Slide 41 text
T H E V E H I C L E C A S E
• This one is harder!
• Vehicle history may come from the time the vehicle
was in different tables, and there is no foreign key
• We look up by registration plate which is shared
between those tables
Slide 42
Slide 42 text
S E T T I N G U P T H E R E L AT E D M A N A G E R
>>> used_vehicle.task_set.order_by('-timestamp')
[, , …]
>>> owned_vehicle.task_set.order_by('-timestamp')
[, , …]
Slide 43
Slide 43 text
T H E M A N A G E R
class RelatedTaskManager(Manager):
def __init__(self, instance):
super().__init__()
self.instance = instance
self.model = Task
Slide 44
Slide 44 text
T H E M A N A G E R
class RelatedTaskManager(Manager):
def get_queryset(self):
plate = self.instance.registration_plate
return self.model.objects.filter(
registration_plate=plate,
)
Slide 45
Slide 45 text
T H E D E S C R I P T O R
class TasksDescriptor(
ReverseManyToOneDescriptor):
related_manager_cls = RelatedTaskManager
def __init__(self):
"""Don't call super as we don't
have a rel object"""
pass
Slide 46
Slide 46 text
T H E M O D E L
class Vehicle(models.Model):
task_set = TasksDescriptor()
Slide 47
Slide 47 text
P R O G R E S S !
used_vehicle.task_set.order_by('-timestamp')
Slide 48
Slide 48 text
A D D I N G P R E F E T C H I N G
class RelatedTaskManager(Manager):
def get_prefetch_queryset(
self, instances, queryset):
• Instances is a concrete list of Vehicle objects
• Queryset is the base queryset of Tasks to start from
(e.g. Task.objects.all())
Slide 49
Slide 49 text
A D D I N G P R E F E T C H I N G
class RelatedTaskManager(Manager):
def get_prefetch_queryset(
self, instances, queryset):
plates = [
instance.registration_plate
for instance in instances
]
queryset = queryset.filter(
registration_plate__in=plates,
)
Slide 50
Slide 50 text
A D D I N G P R E F E T C H I N G
return (
queryset, # Queryset of Tasks
# Task value matcher
lambda result: result.registration_plate,
# Vehicle value matcher
lambda inst: inst.registration_plate,
False, # Many objects not one
'task_set', # cache name
)
Slide 51
Slide 51 text
N O R M A L P R E F E T C H I N G N O W W O R K S
>>> UsedVehicle.objects.prefetch_related(
... 'task_set',
... )
Slide 52
Slide 52 text
C U S T O M P R E F E T C H O B J E C T
def PrefetchVehicleTasks(Prefetch):
def __init__(self):
qs = Task.objects.select_related(
'employee',
)
super().__init__(
'prefetched_tasks', qs, 'task_set',
)
Slide 53
Slide 53 text
A L L D O N E !
OwnedVehicle.objects.filter(
service_date=today,
).prefetch_related(
PrefetchVehicleTasks(),
)
Slide 54
Slide 54 text
S U M M A RY
E P I L O G U E
Slide 55
Slide 55 text
B E A U T I F U L A P I S A R E P O S S I B L E
• Expressions, lookups and transforms are a powerful
collection of tools to create domain specific logic
• Descriptors are magical
• Prefetching can apply to any way you can connect the
two objects together
Slide 56
Slide 56 text
T H E F U T U R E
• Subquery expressions (Django 1.11+)
• Functional indexes from expressions (Custom indexes
Django 1.11+, functional 2.0+ hopefully)
• Virtual fields resolving to an expression (TODO)
• Lazily evaluated prefetching across multiple querysets
(TODO)
Slide 57
Slide 57 text
T H A N K Y O U
github.com/mjtamlyn
twitter.com/mjtamlyn
photocrowd.com/mjtamlyn