Writeup of the DjangoCon Europe 2019 talk »Pushing the ORM to its limits« by Sigurd Ljødal
Sigurd Ljødal: A software developer at Kolonial.no, Norway's largest online grocery store. I have worked with a wide range of technology from frontend to backend, in Python and other languages. For the past year I have been working with warehouse systems, including controlling conveyor systems using asyncio.
Sources – check them, I couldn't write up all the code examples.
Some tips and tricks
Use custom querysets and custom managers: Subclass
models.Manager. Then set
objects = CustomManager.from_queryset(CustomQuerySet) – and note that the
from_queryset method can be chained!
Inspect yo queries:
queryset.explain(verbose=True) are your best friends. But be
aware that this output will be heavily database dependent.
Avoid extra queries: Use
prefetch_related for greater good! But don't optimize
prematurely and make sure that you don't actually make your problems better.
Avoid race conditions: By using
select_for_update in a transaction, you're making sure that the row(s) will not
be modified by a different query in the meantime. This is a heavy and dangerous tool, so be careful.
Customer.objects.annotate( latest_order_time=Subquery( Order.objects.filter( customer=QuterRef('pk') ).order_by('-created_at')\ .values('created_at')[:1] ) )
Use Subquery and Outerref for clever limiting and filtering.
SaltesTarget.objects.annotate( gross_total_sales=Subquery( Order.objects.filter( created_at__year=OuterRef('year'), created_at__month=OuterRef('month') ).annotate( gross_total=Sum('lines__gross_amount') ).values_lists('gross_total') ) )
Run aggregation without grouping (which happens occasionally unintendedly):
SalesTagets.objects.annotate( weekend_revenue=Subquery( Order.objects.filter( created_at__week_day__in=[7, 1], ).values_list( Func( 'lines__gross_amount', function='SUM' # MAGIC ) ) ) )
Constraints and indexes
Custom constraints in addition to vanilla unique constraints are new in 2.2, and indexes were new in 2.0, including conditional constraints and conditional indexes.
Partial unique constraints
Unique Constraints are the old assignment to
unique_together in a model's
Meta class. But partial unique
constraint is new (and also the only way to limit the amount of
null values in a column to one):
class Order(Model): ... class Meta: constraints = [ UniqueConstraint( name='limit_pending_orders', fields=['customer', 'is_shipped'], condition=Q(is_shipped=False) ) ]
This kind of constraint applies to bulk queries and updates from outside Django:
class MontlyBudget(Model): class Meta: constraints = [ CheckConstraint( check=Q(month__in=range(1, 13)), name='check_valid_month', ) ] ]
… sorry, I didn't manage to copy that one.
class Order(Model): class Meta: indexes = [ Index(name=…) ]
Get the previous order from the same customer, huge possibilities there:
orders = Order.objects.annotate( prev_order_id=Window( expression=Lag('order_id', 1), partition_by=[F('customer_id')], order_by=F('created_at').asc() ) ) orders.first().prev_order_id
Customizing the ORM
Create your own database function by subclassing
class Round(Func): function = 'ROUND'
… aaand you're done. Use it like all annotation functions. But of course there are more complex possibilities – again, didn't manage to transcribe all of it.
class AsDateTime(Func): arity = 2 # takes two fields output_field = DateTimeField() def as_postgresql(self, compiler, connection, **extra_contextA): extra_context['tz'] = settings.TIMEZONE template = ( "(%(expression)s || '%(tz)s')" # TODO )
Writing custom SQL
Let's use the age function in Postgres: