Django ORM has lots of resources for making complex database queries and the documentation brings good examples on how to apply each one of them, but understanding how to orchestrate all of those resources on real-life projects may not be so simple. My goal with this talk is to show through examples how to combine some of the QuerySet Methods, Query Expressions and other optimization techniques to make the most of DB resources when processing information inside the code is not an option.
When we start modelling an application, we don’t always know how it’s models will evolve and it may be even more difficult to foresee their behaviour when big amounts of data are stored in their respective tables. With tables getting bigger through the project’s life apparently harmless operations may become impossible to make. Thankfully, databases are already prepared for dealing with large amounts of data and resource consuming operations and Django’s ORM provides solutions for most of them.
For this talk I’m going to build an example Django project, populate it’s tables with big enough datasets and formulate complex questions to demand the most on the ORM’s possibilities. For each question I’m writing at least one solution using the resources described in the QueryExpressions section of Django’s Docs to then analyse the SQL generated by it and the pros and cons between operational costs and code complexity.
The following topics will be covered:
- Using F() expressions for filtering, ordering and annotate operations
- Using Max, Min, Avg with annotations
- Compare Subquery expressions to queryset equivalents
- Present the new Window functions for partitioned operations
- Using Union queries
