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 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 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 Slide

  4. T H A N K Y O U

    View Slide

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

    View 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 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 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 Slide

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

    View 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 Slide

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

    View 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 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 Slide

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

    View Slide

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

    View 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 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 Slide

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

    View 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 Slide

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

    View 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 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 Slide

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

    View 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 Slide

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

    View 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 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 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 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 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 Slide

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

    View Slide

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

    View 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 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 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 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 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 Slide

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

    View Slide

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

    View 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 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 Slide

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

    View Slide