Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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
Tweet

More Decks by Marc Tamlyn

Other Decks in Technology

Transcript

  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. H I ! • Marc Tamlyn • Django core developer

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

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

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

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

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

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

    ) [<Car: AB07CDE>, <Car: FG67HIJ>, <Car: KL07MNP>…]
  15. 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?
  16. 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…
  17. A N I C E A P I >>> UsedVehicle.objects.order_by(

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

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

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

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

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

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

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

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

    vehicle = RegistrationPlateField() action = models.CharField() notes = models.TextField() timestamp = models.DateTimeField()
  36. 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
  37. 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', )
  38. 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>]
  39. 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
  40. 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>, …]
  41. 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
  42. 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, )
  43. 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
  44. T H E M O D E L class Vehicle(models.Model):

    task_set = TasksDescriptor()
  45. 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())
  46. 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, )
  47. 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 )
  48. 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', ... )
  49. 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', )
  50. A L L D O N E ! OwnedVehicle.objects.filter( service_date=today,

    ).prefetch_related( PrefetchVehicleTasks(), )
  51. 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
  52. 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)