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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  4. T H A N K Y O U

    View full-size slide

  5. H I !
    • Marc Tamlyn
    • Django core developer
    • Technical lead at Photocrowd

    View full-size slide

  6. A TA L K I N T H R E E A C T S
    • Querying your data
    • Encapsulating logic
    • Advanced Prefetching

    View full-size slide

  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!

    View full-size slide

  8. M O D E L S
    • Department, Employee, Customer
    • NewVehicle, UsedVehicle, OwnedVehicle
    • No core Vehicle model
    • Task
    • Many small models for specific processes

    View full-size slide

  9. Q U E RY I N G Y O U R D ATA
    A C T I

    View full-size slide

  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
    • …

    View full-size slide

  11. A N I C E A P I
    >>> UsedVehicle.objects.filter(
    ... registration_plate__year=2007,
    ... )

    View full-size slide

  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)

    View full-size slide

  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),
    )

    View full-size slide

  14. R E G I S T E R I T
    CharField.register_lookup(
    RegistrationPlateYearLookup
    )

    View full-size slide

  15. Q U E RY !
    >>> UsedVehicle.objects.filter(
    ... registration_plate__year=2007,
    ... )
    [, , …]

    View full-size slide

  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 "", line 1, in
    ValueError: Year "27" is not valid. Did you mean
    to query Chariot?

    View full-size slide

  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…

    View full-size slide

  18. A N I C E A P I
    >>> UsedVehicle.objects.order_by(
    ... PlateYear('registration_plate').desc(),
    ... )

    View full-size slide

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

    View full-size slide

  20. L E T ' S C H E AT
    def PlateYear(field):
    return Case(…)

    View full-size slide

  21. E N C A P S U L AT I N G L O G I C
    A C T I I

    View full-size slide

  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
    • …

    View full-size slide

  23. A P I
    # Common utilities
    vehicle.complete_purchase()
    # Specific utilities
    vehicle.perform_credit_check()
    vehicle.process_part_exchange()

    View full-size slide

  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()

    View full-size slide

  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

    View full-size slide

  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…

    View full-size slide

  27. T H E C L A S S
    class HirePurchase(BaseTransaction):
    slug = 'hire-purchase'
    def perform_credit_check(self):
    # do stuff here…

    View full-size slide

  28. T H E F I E L D
    class TransactionTypeField(CharField):
    def __init__(self, **kwargs):
    kwargs.setdefault('max_length', 100)
    super().__init__(**kwargs)

    View full-size slide

  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

    View full-size slide

  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)

    View full-size slide

  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)

    View full-size slide

  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__

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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?

    View full-size slide

  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()

    View full-size slide

  37. A P I S
    department.employee_set.prefetch_related(
    PrefetchUserTasks(today_only=True),
    )
    OwnedVehicle.objects.filter(
    service_date=today,
    ).prefetch_related(
    PrefetchVehicleTasks(),
    )

    View full-size slide

  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

    View full-size slide

  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',
    )

    View full-size slide

  40. A L L D O N E !
    >>> peeps = dept.employee_set.prefetch_related(
    ... PrefetchUserTasks(today_only=True),
    ... )
    >>> peeps[0].prefetched_tasks
    [, ]

    View full-size slide

  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

    View full-size slide

  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')
    [, , …]
    >>> owned_vehicle.task_set.order_by('-timestamp')
    [, , …]

    View full-size slide

  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

    View full-size slide

  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,
    )

    View full-size slide

  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

    View full-size slide

  46. T H E M O D E L
    class Vehicle(models.Model):
    task_set = TasksDescriptor()

    View full-size slide

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

    View full-size slide

  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())

    View full-size slide

  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,
    )

    View full-size slide

  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
    )

    View full-size slide

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

    View full-size slide

  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',
    )

    View full-size slide

  53. A L L D O N E !
    OwnedVehicle.objects.filter(
    service_date=today,
    ).prefetch_related(
    PrefetchVehicleTasks(),
    )

    View full-size slide

  54. S U M M A RY
    E P I L O G U E

    View full-size slide

  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

    View full-size slide

  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)

    View full-size slide

  57. T H A N K Y O U
    github.com/mjtamlyn
    twitter.com/mjtamlyn
    photocrowd.com/mjtamlyn

    View full-size slide