Weird and Wonderful things to do with the ORM

Weird and Wonderful things to do with the ORM

A collection of non-standard techniques for working with the ORM that are interesting and you possibly shouldn't do.


Marc Tamlyn

April 05, 2017


  1. 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
  2. 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
  3. 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
  4. T H A N K Y O U

  5. H I ! • Marc Tamlyn • Django core developer

    • Technical lead at Photocrowd
  6. A TA L K I N T H R E

    E A C T S • Querying your data • Encapsulating logic • Advanced Prefetching
  7. 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!
  8. M O D E L S • Department, Employee, Customer

    • NewVehicle, UsedVehicle, OwnedVehicle • No core Vehicle model • Task • Many small models for specific processes
  9. Q U E RY I N G Y O U

    R D ATA A C T I
  10. 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 • …
  11. A N I C E A P I >>> UsedVehicle.objects.filter(

    ... registration_plate__year=2007, ... )
  12. 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)
  13. 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), )
  14. R E G I S T E R I T

    CharField.register_lookup( RegistrationPlateYearLookup )
  15. Q U E RY ! >>> UsedVehicle.objects.filter( ... registration_plate__year=2007, ...

    ) [<Car: AB07CDE>, <Car: FG67HIJ>, <Car: KL07MNP>…]
  16. 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 "<stdin>", line 1, in <module> ValueError: Year "27" is not valid. Did you mean to query Chariot?
  17. 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…
  18. A N I C E A P I >>> UsedVehicle.objects.order_by(

    ... PlateYear('registration_plate').desc(), ... )
  19. 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')
  20. L E T ' S C H E AT def

    PlateYear(field): return Case(…)
  21. E N C A P S U L AT I

    N G L O G I C A C T I I
  22. 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 • …
  23. A P I # Common utilities vehicle.complete_purchase() # Specific utilities

    vehicle.perform_credit_check() vehicle.process_part_exchange()
  24. 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()
  25. 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
  26. 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…
  27. T H E C L A S S class HirePurchase(BaseTransaction):

    slug = 'hire-purchase' def perform_credit_check(self): # do stuff here…
  28. T H E F I E L D class TransactionTypeField(CharField):

    def __init__(self, **kwargs): kwargs.setdefault('max_length', 100) super().__init__(**kwargs)
  29. 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
  30. 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)
  31. 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)
  32. U S E I T >>> type(vehicle.transaction) HirePurchase >>> vehicle.transaction.vehicle

    is vehicle True >>> vehicle.transaction = PartExchange # Could be implemented via the __set__
  33. 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
  34. A D VA N C E D P R E

    F E T C H I N G A C T I I I
  35. 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?
  36. M O D E L class Task(models.Model): employee = models.ForeignKey(Employee)

    vehicle = RegistrationPlateField() action = models.CharField() notes = models.TextField() timestamp = models.DateTimeField()
  37. A P I S department.employee_set.prefetch_related( PrefetchUserTasks(today_only=True), ) OwnedVehicle.objects.filter( service_date=today, ).prefetch_related(

    PrefetchVehicleTasks(), )
  38. 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
  39. 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', )
  40. A L L D O N E ! >>> peeps

    = dept.employee_set.prefetch_related( ... PrefetchUserTasks(today_only=True), ... ) >>> peeps[0].prefetched_tasks [<Task: Change oil>, <Task: Check tyres>]
  41. 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
  42. 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') [<Task: Clean & Valet>, <Task: Change oil>, …] >>> owned_vehicle.task_set.order_by('-timestamp') [<Task: Clean & Valet>, <Task: Change oil>, …]
  43. 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
  44. 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, )
  45. 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
  46. T H E M O D E L class Vehicle(models.Model):

    task_set = TasksDescriptor()
  47. P R O G R E S S ! used_vehicle.task_set.order_by('-timestamp')

  48. 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())
  49. 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, )
  50. 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 )
  51. 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', ... )
  52. 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', )
  53. A L L D O N E ! OwnedVehicle.objects.filter( service_date=today,

    ).prefetch_related( PrefetchVehicleTasks(), )
  54. S U M M A RY E P I L

    O G U E
  55. 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
  56. 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)
  57. T H A N K Y O U