Pushing the ORM to its limits
Ljødal, Sigurd
You can do a lot more with the Django ORM than you might think, and when something isn’t possible using the existing primitives, you can easily add your own. In this talk you will learn about both advanced features of the ORM and how to run your own SQL queries. Django’s ORM makes it super simple to run quick queries against your database, but it can do a lot more for you than you might think. Need to get some statistics on a monthly-basis? Easy, just annotate your queryset with year and month using ExtractYear and ExtractMonth, and you can use .values() to get the data aggregated by month. When you find yourself in need of something that is not already covered by the existing ORM functions, you will often be able to create it yourself. Have you modelled your data with separate date and time fields, but need to compare this to a datetime field? You can make a custom expression in Django for combining the date and time into a datetime in the database. Starting with Django 1.11, you can easily make subqueries using the Django ORM. In Django 2.0 we got support for window functions. Django 2.1 exposes additional functions that are available in many databases, and version 2.2 will introduce support for custom constraints and extends the primitives for custom indexes. It is also easy to drop down to writing raw SQL if you need to. It is probably not something you need to do every day, but it’s good to know that the option is there, and know how and when to do it.
 Thank you. Can everyone hear me? Yeah. Do I have slides? Yeah. OK. Hello. I am Sigur, and I am a developer at Kroneal.no, where we do a lot of stuff in Django. We built an online grocery store with logistics, and we've used the ORM for quite a lot. So I'm going to tell you a bit about that. And you will find the models that I'm going to use in my examples in Django project on my GitHub account, which you can see on that URL. OK. So let's get started. Quick agenda. I'm first going to show you some quick tips and tricks to optimize your code, to reduce your repetition, and various other minor things. Then I'm going to deep dive into subqueries, which is something you can do in the ORM now. I will show you a bit about custom constraints and indexes. A quick example of window functions using the ORM. And finally, a bit about how you can actually add your own custom stuff to the ORM. And a quick disclaimer. Unfortunately, my talk is quite code heavy. So I'm sorry to those who are not developers, but I hope you will be allowed to follow along. OK. So some quick tips and tricks. The first one is you can add your own custom query sets and managers to your models, which is really useful if you have a lot of views or serializers that access common objects. For example, we use it a lot for confirmed orders. It's a single thing that we do all the time. So what you can do is have a subclass of models.manager and model.queryset. And you can implement any methods you like to do on this. So this example, I have a method to create an order, which will also, for example, create the lines for each product or filter out any orders that are delivered, even case I'm looking at undelivered orders. And this can be instantiated on the order model by setting the objects attribute like this. And then you can access this through the objects reference on your model. And the query set method can be chained like any other method on default query sets. So this is useful if, like I said, you do a lot of things repetitively. OK. Another quick thing, if you are ever wondering why is something doing something weird or just wondering how is this working under the hood, you can inspect your queries. So if we have a query set and we convert it to a string, then it will actually print out the actual SQL being run in the database. And another new thing in Django 2.1 is that Django has support for explain, which will run an explain query in the database, show you the output. And this is really useful if you're wondering why is my query slow, what is this actually doing. And it has a lot of options. You can specify, in this example I said verbose true, which is giving me extra output. You can specify what formats you want, if you want it to be to do analyze, which is a deeper check. And yeah, just a quick note, this output is really database dependent. So this is example from Postgres. In other databases, you will most likely get different results. Yeah. Another quick example, if you do something like this, where we iterate over each order and then print out the customer's name, and then we have a set of lines on an order, and we print out each line. This will generate a lot of queries against the database, which can be slow. So what we can do here is use select related and prefetch related. The first one will, in this example, fetch all the customers. So the first example on a topter will not generate an extra query in the loop. And the second one is fetching all the lines. So the second example there will not generate extra queries. And select related is for references where you have one to one or one to many. So for example, an order only has one customer. But an order has multiple lines. So then we have to use prefetch related, which will generate join in Python instead of the database. So it's a separate query, but it will be run once for an entire query set. OK. And just a quick note on this. Don't optimize prematurely. If you do this blindly, you might actually end up with slower queries. So just be aware of this and know that you actually have a problem before you optimize too much. And another quick tip. You can also use the database to avoid the race conditions. For it, select for update will, for any row in the database returned by that query, it will keep a lock for the duration of a transaction. So in this case, when I say product.inventory minus one, I can be sure that no other object or no other query has modified the same object in the database. And then I can safely save. And when the transaction ends, in this case, when the with block exits, it will release in the database. And this is really useful in some cases. It's also a bit heavy, maybe. So if you can model something in a different way, maybe consider it. But yeah, it's worth knowing that it is an option available to you. OK. So let's have a deep dive into subqueries, which is something that the Django arm got in, I think it was 1.11. So it's been around for quite a while. But it's really powerful, and it allows you to do a lot. So I will now show you first an example where, for each customer, I want the timestamp when they place their latest order. So I have this query. And yeah, it's a lot of code. There's a lot going on. But we use the annotate method on the query set, which allows us to add new fields, basically, to the resulting objects. And we use the subquery class, which wraps separate query set, where we can basically do anything we want. And we can also use the special class called outer ref, which will reference the wrapping query outside of this. So in this case, I am referencing the primary key of the customer. And then I'm, so this basically returns a query set with all the orders of that customer. And then ordering it by timestamp, selecting only the timestamp, because that's the one value I'm interested in. And subqueries only allow you to return a single value in these cases. And then I'm limiting it to the first one, because it's going to be added as a column, so I can only have one value. And the result of this is that I can say, latest order time on a customer. And my example is wrong. It should have been a timestamp. Sorry. Yeah. And it's also possible to do exists queries, where you basically replace subquery with exists. And then you can check, basically, does something exist in a database without actually fetching the data. But we can also use subqueries to do aggregation. So say that we have a sales target, where we each month want to sell for a certain amount. And then we try to check if we've reached that target. Then we want every single order that matches the year and month specified on the sales target. We use values list to group by those two fields, where we extract the year from the date and the month from the date. These two values will be unique for everything that matches the first filter. So the group by is basically annulled, but it's something we have to do in the current Django arm. But yeah, at least this is how it's documented. Then we annotate the sum. This basically is a group by that does a group by, but at two top levels, calculates some of the total of the lines. And then we return, like in the other example, a values list. And because we've grouped by something, this will only return a single value. So we don't have to limit this to a single row. And the result of this is that we get a gross total on the object. And this is currently the only way this is documented. But say that we don't have anything unique to group by. What do we do then? So let's say we have a database table, where we have three values. And we want to select only orders placed on a weekend date, which is 7 and 1 if you use the Django arm. So the last one here should not be included. So yeah, we do the same filtering, where we say weekday is either 7 or 1. But it can be either 7 or 1. So we don't have anything unique to group by. What do we do then? Well, we could try to just select the sum directly. But because of how the way sum is implemented in Django, this will add group by on the ID of the order objects. So basically what you see is that we get the first value, which is not really what we wanted. Well, there is a trick we can do here, which is, sorry. So we have the same table, same values. And by replacing sum with just func, and specifying that we want to use the SQL function named sum, we get the result we want. And the trick here is that sum is a subclass of aggregate. And aggregate does add a group by clause, where if we don't already have specified what to group by, it will group by the primary key of the current query set, which in this case is the order ID. But func is not a subclass of aggregate. So this will actually just calculate the sum across all rows without grouping, which is what we wanted in the first place. So yeah, this is not the prettiest. It does work, though. So it's worth knowing. And a note on this is sometimes it's really useful to inspect the query set's query attribute. That's basically how I discovered this. I discovered that, OK, sum adds a group by a group clause. But this doesn't. So yeah, hopefully we can implement this in Django at some point. But right now, this is a solution that works. OK. And done. Let's have a quick look on custom constraints and indexes, which Django has had support for unique constraints for quite a while. But custom constraints and indexes is new. Starting in Django 2.0, I think, you can specify custom indexes. And starting in Django 2.2, you can specify either conditional constraints or conditional indexes. So let's have a look at that. First, unique constraints. So say we have a sales target, where we specify a month and a year. Here, we don't want to have duplicate values. We can only have one target for a single month. So we use unique together and specify that year and month should be those two in combination should be unique. So yeah, this has been around for a long time. New now is that we can have a partial unique constraint. So say that on an order, we don't want to allow customers to actually have multiple unshipped orders. Now we can set, why did my slide disappear? OK, sorry. OK, so we can set, instead of setting unique together, we actually set constraints to a list of constraints. So in this case, we say we want to field customer, and it's shipped to be unique together, but only in the case where it's shipped is set to false. This will do basically the same as the other example on the previous slide. But it will do it only on rows where that condition is true. So this is useful for a lot of things. If you have any rows with null values, one row with null is considered different from another row with null. So if you want to have unique constraint saying only one row should be allowed to be null, this is basically the only way to do it. You say, you filter by the row that is null and say it has to be unique on some other way, and you can have a separate constraint constraining other values where it's not null. Yeah. And another new type of constraint in Django 2.2 is check constraints. This is similar to what we've had in Django for quite a while, where we have validators on fields. That's basically when you set a value, it will check around some validators to check if the value you have entered is within some constraint. But new here is that you can make the database to this. So you can say, for example, on our monthly sales targets, we can say the month must be in the range 1 till 12 inclusive, which basically it's in the Julian and Gregorian calendars are the valid months. And we don't want any rows with values outside of that. And this is unlike the normal validators in Django. This is validated by the database. So this will protect you if you have bulk inserts, bulk updates. If you access the database outside of Django, it will still be checked. So it can be really useful. OK. And then we have partial indexes. This is similar. In Postgres, it's actually the same basically as the partial constraints, except it doesn't limit you. But this will help you maybe speed up some indexes, some queries. Or if you have a table with a lot of null values that you don't want to have in your index, you can specify a condition on the index that just index the values that don't have null. Or in this case, just index the unshipped orders. So those are the ones that we most often access. So we want the queries on that to be fast. OK. Window functions is another new thing in Django 2.0. My quick example of this is for each order, I want to fetch the idea of the previous order from the same customer. So yeah, our output here will be 1. So we use the window class from the arm. And we use a function called lag, which is referencing a value in a different row in the query set. So in this case, since I specified 1, it's the previous row. I could specify 2, and it would go two steps back and similar. And then we say partition by the customer ID, because we wanted an order from the same customer. And then I specify that you order it by the created time to get the previous one. And this results in a query set where we have annotated an ID on it. And this is a really basic example, but it can be really powerful. If, for example, we want to calculate a cumulative sum of gross amounts for each order where it grows, or we want to compare the total sales amount on an order to the previous order, for example. So yeah, this has a lot of uses. If we want to extend the ORM with our own functionality in cases where we don't really find what we need, it's also possible. So one example is, OK, the database has a function that I want to use, but the ORM does currently not expose it. Well, we can create our own subclass of fun, which you've seen earlier, and specify that the function used should be, in this example, round, which is doing a rounding of a number in the database instead of having to do it in Python code. So this is everything that's required to use that function. Use it like any of the other annotations that we have from Django. And you can also do more complex examples. So in this case, I'm implementing a function that takes a separate date and time column and combines them together to get a date time. If, for example, I want to compare this to another table where I have the date time as a proper date time value. So we take in two inputs, RIT2, the two columns, and we output a date time. And this one is implemented separately for the different database backends. So in this example, I'm going to show you the Postgres code. So we implement this function. It takes in arguments. And we annotate the given values with some extra information. So in this example, we want to add the time zone to be sure that we get the value in the intended time zone. We specify a template, which Django will use to generate the actual SQL. In this case, we have the two values we get in, and we append the time zone. And then we do SQLCast to a timestamp, TC. And then we will allow this to be done by Django. And we can use it like this at the bottom here. So basically, we specify the two fields that have the date and time. And the output of this will be at date time. OK. Last example, I want to write some custom ORM. Custom SQL, sorry. OK. First example, I want to use the age function in Postgres. So I annotate my query set with raw SQL class. Or this same thing can be done using extra. This has a lot more options. So yeah, check out the documentation if you are interested. Or if I actually want to write the entire SQL myself, I can do that using raw on the query set. Or I can actually build the entire thing myself using just a database cursor where I can run my own SQL and get any output. So we execute SQL and use fetch1, which will return one row. In this case, it will be a tuple where the value is just 2. OK. And there's so much more to Django ORM that I've been able to fit into 30 minutes. So please have a look at the documentation. Specifically, the ORM query set API documentation is really useful. Yeah. So thank you. If you want to contact me, you can find me on Twitter. I will publish a blog post after the conference with some more details. And yeah, we also have a stand. So you can find me there if you have any questions. OK. Thank you. Thank you. All right. Thank you very much, Seager. That was very informative. I just realized I need to go back to the office tomorrow and rewrite all my queries.
 Thank you.  Can everyone hear me?  Yeah.  Do I have slides?  Yeah.  OK.  Hello.  I am Sigur, and I am a developer at Kroneal.no,  where we do a lot of stuff in Django.  We built an online grocery store with logistics,  and we've used the ORM for quite a lot.  So I'm going to tell you a bit about that.  And you will find the models that I'm  going to use in my examples in Django project on my GitHub  account, which you can see on that URL.  OK.  So let's get started.  Quick agenda.  I'm first going to show you some quick tips and tricks  to optimize your code, to reduce your repetition,  and various other minor things.  Then I'm going to deep dive into subqueries, which  is something you can do in the ORM now.  I will show you a bit about custom constraints and indexes.  A quick example of window functions using the ORM.  And finally, a bit about how you can actually  add your own custom stuff to the ORM.  And a quick disclaimer.  Unfortunately, my talk is quite code heavy.  So I'm sorry to those who are not developers,  but I hope you will be allowed to follow along.  OK.  So some quick tips and tricks.  The first one is you can add your own custom query  sets and managers to your models, which  is really useful if you have a lot of views or serializers that  access common objects.  For example, we use it a lot for confirmed orders.  It's a single thing that we do all the time.  So what you can do is have a subclass of models.manager  and model.queryset.  And you can implement any methods you like to do on this.  So this example, I have a method to create an order,  which will also, for example, create the lines for each product  or filter out any orders that are delivered,  even case I'm looking at undelivered orders.  And this can be instantiated on the order model  by setting the objects attribute like this.  And then you can access this through the objects reference  on your model.  And the query set method can be chained  like any other method on default query sets.  So this is useful if, like I said,  you do a lot of things repetitively.  OK.  Another quick thing, if you are ever  wondering why is something doing something weird  or just wondering how is this working under the hood,  you can inspect your queries.  So if we have a query set and we convert it to a string,  then it will actually print out the actual SQL being  run in the database.  And another new thing in Django 2.1  is that Django has support for explain, which  will run an explain query in the database, show you the output.  And this is really useful if you're  wondering why is my query slow, what is this actually doing.  And it has a lot of options.  You can specify, in this example I said verbose true, which  is giving me extra output.  You can specify what formats you want,  if you want it to be to do analyze, which is a deeper check.  And yeah, just a quick note, this output  is really database dependent.  So this is example from Postgres.  In other databases, you will most likely  get different results.  Yeah.  Another quick example, if you do something  like this, where we iterate over each order  and then print out the customer's name,  and then we have a set of lines on an order,  and we print out each line.  This will generate a lot of queries  against the database, which can be slow.  So what we can do here is use select related and prefetch  related.  The first one will, in this example, fetch all the customers.  So the first example on a topter will not  generate an extra query in the loop.  And the second one is fetching all the lines.  So the second example there will not generate extra queries.  And select related is for references where you have one  to one or one to many.  So for example, an order only has one customer.  But an order has multiple lines.  So then we have to use prefetch related,  which will generate join in Python instead of the database.  So it's a separate query, but it will be run once  for an entire query set.  OK.  And just a quick note on this.  Don't optimize prematurely.  If you do this blindly, you might actually  end up with slower queries.  So just be aware of this and know  that you actually have a problem before you optimize too much.  And another quick tip.  You can also use the database to avoid the race conditions.  For it, select for update will, for any row in the database  returned by that query, it will keep  a lock for the duration of a transaction.  So in this case, when I say product.inventory minus one,  I can be sure that no other object or no other query  has modified the same object in the database.  And then I can safely save.  And when the transaction ends, in this case,  when the with block exits, it will release in the database.  And this is really useful in some cases.  It's also a bit heavy, maybe.  So if you can model something in a different way,  maybe consider it.  But yeah, it's worth knowing that it  is an option available to you.  OK.  So let's have a deep dive into subqueries, which  is something that the Django arm got in, I think it was 1.11.  So it's been around for quite a while.  But it's really powerful, and it allows you to do a lot.  So I will now show you first an example where,  for each customer, I want the timestamp when they place  their latest order.  So I have this query.  And yeah, it's a lot of code.  There's a lot going on.  But we use the annotate method on the query set, which  allows us to add new fields, basically,  to the resulting objects.  And we use the subquery class, which  wraps separate query set, where we can basically  do anything we want.  And we can also use the special class called  outer ref, which will reference the wrapping query  outside of this.  So in this case, I am referencing the primary key  of the customer.  And then I'm, so this basically returns a query set  with all the orders of that customer.  And then ordering it by timestamp, selecting only  the timestamp, because that's the one value I'm interested in.  And subqueries only allow you to return a single value  in these cases.  And then I'm limiting it to the first one,  because it's going to be added as a column,  so I can only have one value.  And the result of this is that I can say,  latest order time on a customer.  And my example is wrong.  It should have been a timestamp.  Sorry.  Yeah.  And it's also possible to do exists queries, where you basically  replace subquery with exists.  And then you can check, basically,  does something exist in a database  without actually fetching the data.  But we can also use subqueries to do aggregation.  So say that we have a sales target,  where we each month want to sell for a certain amount.  And then we try to check if we've reached that target.  Then we want every single order that  matches the year and month specified on the sales target.  We use values list to group by those two fields,  where we extract the year from the date  and the month from the date.  These two values will be unique for everything  that matches the first filter.  So the group by is basically annulled,  but it's something we have to do in the current Django arm.  But yeah, at least this is how it's documented.  Then we annotate the sum.  This basically is a group by that does a group by,  but at two top levels, calculates  some of the total of the lines.  And then we return, like in the other example, a values list.  And because we've grouped by something,  this will only return a single value.  So we don't have to limit this to a single row.  And the result of this is that we get a gross total  on the object.  And this is currently the only way this is documented.  But say that we don't have anything unique to group by.  What do we do then?  So let's say we have a database table,  where we have three values.  And we want to select only orders placed  on a weekend date, which is 7 and 1 if you use the Django arm.  So the last one here should not be included.  So yeah, we do the same filtering,  where we say weekday is either 7 or 1.  But it can be either 7 or 1.  So we don't have anything unique to group by.  What do we do then?  Well, we could try to just select the sum  directly.  But because of how the way sum is implemented in Django,  this will add group by on the ID of the order objects.  So basically what you see is that we get the first value,  which is not really what we wanted.  Well, there is a trick we can do here, which is, sorry.  So we have the same table, same values.  And by replacing sum with just func,  and specifying that we want to use the SQL function named sum,  we get the result we want.  And the trick here is that sum is a subclass of aggregate.  And aggregate does add a group by clause,  where if we don't already have specified what to group by,  it will group by the primary key of the current query set,  which in this case is the order ID.  But func is not a subclass of aggregate.  So this will actually just calculate the sum across all rows  without grouping, which is what we wanted in the first place.  So yeah, this is not the prettiest.  It does work, though.  So it's worth knowing.  And a note on this is sometimes it's  really useful to inspect the query set's query attribute.  That's basically how I discovered this.  I discovered that, OK, sum adds a group by a group clause.  But this doesn't.  So yeah, hopefully we can implement this in Django  at some point.  But right now, this is a solution that works.  OK.  And done.  Let's have a quick look on custom constraints and indexes,  which Django has had support for unique constraints  for quite a while.  But custom constraints and indexes is new.  Starting in Django 2.0, I think, you  can specify custom indexes.  And starting in Django 2.2, you can specify either conditional  constraints or conditional indexes.  So let's have a look at that.  First, unique constraints.  So say we have a sales target, where we specify a month  and a year.  Here, we don't want to have duplicate values.  We can only have one target for a single month.  So we use unique together and specify that year and month  should be those two in combination should be unique.  So yeah, this has been around for a long time.  New now is that we can have a partial unique constraint.  So say that on an order, we don't want  to allow customers to actually have multiple unshipped orders.  Now we can set, why did my slide disappear?  OK, sorry.  OK, so we can set, instead of setting unique together,  we actually set constraints to a list of constraints.  So in this case, we say we want to field customer,  and it's shipped to be unique together,  but only in the case where it's shipped is set to false.  This will do basically the same as the other example  on the previous slide.  But it will do it only on rows where that condition is true.  So this is useful for a lot of things.  If you have any rows with null values,  one row with null is considered different  from another row with null.  So if you want to have unique constraint saying only one row  should be allowed to be null, this is basically  the only way to do it.  You say, you filter by the row that is null  and say it has to be unique on some other way,  and you can have a separate constraint constraining  other values where it's not null.  Yeah.  And another new type of constraint in Django 2.2  is check constraints.  This is similar to what we've had in Django for quite a while,  where we have validators on fields.  That's basically when you set a value,  it will check around some validators  to check if the value you have entered  is within some constraint.  But new here is that you can make the database to this.  So you can say, for example, on our monthly sales targets,  we can say the month must be in the range 1 till 12 inclusive,  which basically it's in the Julian and Gregorian calendars  are the valid months.  And we don't want any rows with values outside of that.  And this is unlike the normal validators in Django.  This is validated by the database.  So this will protect you if you have bulk inserts, bulk updates.  If you access the database outside of Django,  it will still be checked.  So it can be really useful.  OK.  And then we have partial indexes.  This is similar.  In Postgres, it's actually the same basically  as the partial constraints, except it doesn't limit you.  But this will help you maybe speed up some indexes,  some queries.  Or if you have a table with a lot of null values  that you don't want to have in your index,  you can specify a condition on the index  that just index the values that don't have null.  Or in this case, just index the unshipped orders.  So those are the ones that we most often access.  So we want the queries on that to be fast.  OK.  Window functions is another new thing in Django 2.0.  My quick example of this is for each order,  I want to fetch the idea of the previous order  from the same customer.  So yeah, our output here will be 1.  So we use the window class from the arm.  And we use a function called lag, which  is referencing a value in a different row in the query  set.  So in this case, since I specified 1,  it's the previous row.  I could specify 2, and it would go two steps back and similar.  And then we say partition by the customer ID,  because we wanted an order from the same customer.  And then I specify that you order it  by the created time to get the previous one.  And this results in a query set where  we have annotated an ID on it.  And this is a really basic example,  but it can be really powerful.  If, for example, we want to calculate a cumulative sum  of gross amounts for each order where it grows,  or we want to compare the total sales  amount on an order to the previous order, for example.  So yeah, this has a lot of uses.  If we want to extend the ORM with our own functionality  in cases where we don't really find what we need,  it's also possible.  So one example is, OK, the database  has a function that I want to use,  but the ORM does currently not expose it.  Well, we can create our own subclass of fun, which you've  seen earlier, and specify that the function used should  be, in this example, round, which  is doing a rounding of a number in the database  instead of having to do it in Python code.  So this is everything that's required to use that function.  Use it like any of the other annotations  that we have from Django.  And you can also do more complex examples.  So in this case, I'm implementing a function that  takes a separate date and time column  and combines them together to get a date time.  If, for example, I want to compare this  to another table where I have the date time  as a proper date time value.  So we take in two inputs, RIT2, the two columns,  and we output a date time.  And this one is implemented separately  for the different database backends.  So in this example, I'm going to show you the Postgres code.  So we implement this function.  It takes in arguments.  And we annotate the given values with some extra information.  So in this example, we want to add the time zone  to be sure that we get the value in the intended time zone.  We specify a template, which Django will use  to generate the actual SQL.  In this case, we have the two values we get in,  and we append the time zone.  And then we do SQLCast to a timestamp, TC.  And then we will allow this to be done by Django.  And we can use it like this at the bottom here.  So basically, we specify the two fields that  have the date and time.  And the output of this will be at date time.  OK.  Last example, I want to write some custom ORM.  Custom SQL, sorry.  OK.  First example, I want to use the age function in Postgres.  So I annotate my query set with raw SQL class.  Or this same thing can be done using extra.  This has a lot more options.  So yeah, check out the documentation if you are  interested.  Or if I actually want to write the entire SQL myself,  I can do that using raw on the query set.  Or I can actually build the entire thing myself using just  a database cursor where I can run my own SQL  and get any output.  So we execute SQL and use fetch1, which will return one row.  In this case, it will be a tuple where the value is just 2.  OK.  And there's so much more to Django ORM  that I've been able to fit into 30 minutes.  So please have a look at the documentation.  Specifically, the ORM query set API documentation  is really useful.  Yeah.  So thank you.  If you want to contact me, you can find me on Twitter.  I will publish a blog post after the conference  with some more details.  And yeah, we also have a stand.  So you can find me there if you have any questions.  OK.  Thank you.  Thank you.  All right.  Thank you very much, Seager.  That was very informative.  I just realized I need to go back to the office tomorrow  and rewrite all my queries.
DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__0_143.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__144_239.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__240_1391.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__1392_2807.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__2808_3167.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__3168_3215.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__3216_4931.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__4932_7079.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__7080_9479.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__9480_11123.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__11124_11663.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__11664_14567.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__14568_14723.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__14724_17135.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__17136_19151.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__19152_21191.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__21192_22019.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__22020_22091.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__22092_22859.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__22860_22979.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__22980_27167.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__27168_27275.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__27276_28511.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__28512_28691.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__28692_30839.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__30840_31151.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__31152_32339.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__32340_34847.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__34848_36563.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__36564_37019.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__37020_37487.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__37488_37595.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__37596_37643.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__37644_37727.png DjangoCon_2019_-_Pushing_the_ORM_to_its_limits__Englisch_ASR__37728_37852.png
Thank you. Can everyone hear me? Yeah. Do I have slides? Yeah. OK. Hello. I am Sigur, and I am a developer at Kroneal.no, where we do a lot of stuff in Django. We built an online grocery store with logistics, and we've used the ORM for quite a lot. So I'm going to tell you a bit about that. And you will find the models that I'm going to use in my examples in Django project on my GitHub account, which you can see on that URL. OK. So let's get started. Quick agenda. I'm first going to show you some quick tips and tricks to optimize your code, to reduce your repetition, and various other minor things. Then I'm going to deep dive into subqueries, which is something you can do in the ORM now. I will show you a bit about custom constraints and indexes. A quick example of window functions using the ORM. And finally, a bit about how you can actually add your own custom stuff to the ORM. And a quick disclaimer. Unfortunately, my talk is quite code heavy. So I'm sorry to those who are not developers, but I hope you will be allowed to follow along. OK. So some quick tips and tricks. The first one is you can add your own custom query sets and managers to your models, which is really useful if you have a lot of views or serializers that access common objects. For example, we use it a lot for confirmed orders. It's a single thing that we do all the time. So what you can do is have a subclass of models.manager and model.queryset. And you can implement any methods you like to do on this. So this example, I have a method to create an order, which will also, for example, create the lines for each product or filter out any orders that are delivered, even case I'm looking at undelivered orders. And this can be instantiated on the order model by setting the objects attribute like this. And then you can access this through the objects reference on your model. And the query set method can be chained like any other method on default query sets. So this is useful if, like I said, you do a lot of things repetitively. OK. Another quick thing, if you are ever wondering why is something doing something weird or just wondering how is this working under the hood, you can inspect your queries. So if we have a query set and we convert it to a string, then it will actually print out the actual SQL being run in the database. And another new thing in Django 2.1 is that Django has support for explain, which will run an explain query in the database, show you the output. And this is really useful if you're wondering why is my query slow, what is this actually doing. And it has a lot of options. You can specify, in this example I said verbose true, which is giving me extra output. You can specify what formats you want, if you want it to be to do analyze, which is a deeper check. And yeah, just a quick note, this output is really database dependent. So this is example from Postgres. In other databases, you will most likely get different results. Yeah. Another quick example, if you do something like this, where we iterate over each order and then print out the customer's name, and then we have a set of lines on an order, and we print out each line. This will generate a lot of queries against the database, which can be slow. So what we can do here is use select related and prefetch related. The first one will, in this example, fetch all the customers. So the first example on a topter will not generate an extra query in the loop. And the second one is fetching all the lines. So the second example there will not generate extra queries. And select related is for references where you have one to one or one to many. So for example, an order only has one customer. But an order has multiple lines. So then we have to use prefetch related, which will generate join in Python instead of the database. So it's a separate query, but it will be run once for an entire query set. OK. And just a quick note on this. Don't optimize prematurely. If you do this blindly, you might actually end up with slower queries. So just be aware of this and know that you actually have a problem before you optimize too much. And another quick tip. You can also use the database to avoid the race conditions. For it, select for update will, for any row in the database returned by that query, it will keep a lock for the duration of a transaction. So in this case, when I say product.inventory minus one, I can be sure that no other object or no other query has modified the same object in the database. And then I can safely save. And when the transaction ends, in this case, when the with block exits, it will release in the database. And this is really useful in some cases. It's also a bit heavy, maybe. So if you can model something in a different way, maybe consider it. But yeah, it's worth knowing that it is an option available to you. OK. So let's have a deep dive into subqueries, which is something that the Django arm got in, I think it was 1.11. So it's been around for quite a while. But it's really powerful, and it allows you to do a lot. So I will now show you first an example where, for each customer, I want the timestamp when they place their latest order. So I have this query. And yeah, it's a lot of code. There's a lot going on. But we use the annotate method on the query set, which allows us to add new fields, basically, to the resulting objects. And we use the subquery class, which wraps separate query set, where we can basically do anything we want. And we can also use the special class called outer ref, which will reference the wrapping query outside of this. So in this case, I am referencing the primary key of the customer. And then I'm, so this basically returns a query set with all the orders of that customer. And then ordering it by timestamp, selecting only the timestamp, because that's the one value I'm interested in. And subqueries only allow you to return a single value in these cases. And then I'm limiting it to the first one, because it's going to be added as a column, so I can only have one value. And the result of this is that I can say, latest order time on a customer. And my example is wrong. It should have been a timestamp. Sorry. Yeah. And it's also possible to do exists queries, where you basically replace subquery with exists. And then you can check, basically, does something exist in a database without actually fetching the data. But we can also use subqueries to do aggregation. So say that we have a sales target, where we each month want to sell for a certain amount. And then we try to check if we've reached that target. Then we want every single order that matches the year and month specified on the sales target. We use values list to group by those two fields, where we extract the year from the date and the month from the date. These two values will be unique for everything that matches the first filter. So the group by is basically annulled, but it's something we have to do in the current Django arm. But yeah, at least this is how it's documented. Then we annotate the sum. This basically is a group by that does a group by, but at two top levels, calculates some of the total of the lines. And then we return, like in the other example, a values list. And because we've grouped by something, this will only return a single value. So we don't have to limit this to a single row. And the result of this is that we get a gross total on the object. And this is currently the only way this is documented. But say that we don't have anything unique to group by. What do we do then? So let's say we have a database table, where we have three values. And we want to select only orders placed on a weekend date, which is 7 and 1 if you use the Django arm. So the last one here should not be included. So yeah, we do the same filtering, where we say weekday is either 7 or 1. But it can be either 7 or 1. So we don't have anything unique to group by. What do we do then? Well, we could try to just select the sum directly. But because of how the way sum is implemented in Django, this will add group by on the ID of the order objects. So basically what you see is that we get the first value, which is not really what we wanted. Well, there is a trick we can do here, which is, sorry. So we have the same table, same values. And by replacing sum with just func, and specifying that we want to use the SQL function named sum, we get the result we want. And the trick here is that sum is a subclass of aggregate. And aggregate does add a group by clause, where if we don't already have specified what to group by, it will group by the primary key of the current query set, which in this case is the order ID. But func is not a subclass of aggregate. So this will actually just calculate the sum across all rows without grouping, which is what we wanted in the first place. So yeah, this is not the prettiest. It does work, though. So it's worth knowing. And a note on this is sometimes it's really useful to inspect the query set's query attribute. That's basically how I discovered this. I discovered that, OK, sum adds a group by a group clause. But this doesn't. So yeah, hopefully we can implement this in Django at some point. But right now, this is a solution that works. OK. And done. Let's have a quick look on custom constraints and indexes, which Django has had support for unique constraints for quite a while. But custom constraints and indexes is new. Starting in Django 2.0, I think, you can specify custom indexes. And starting in Django 2.2, you can specify either conditional constraints or conditional indexes. So let's have a look at that. First, unique constraints. So say we have a sales target, where we specify a month and a year. Here, we don't want to have duplicate values. We can only have one target for a single month. So we use unique together and specify that year and month should be those two in combination should be unique. So yeah, this has been around for a long time. New now is that we can have a partial unique constraint. So say that on an order, we don't want to allow customers to actually have multiple unshipped orders. Now we can set, why did my slide disappear? OK, sorry. OK, so we can set, instead of setting unique together, we actually set constraints to a list of constraints. So in this case, we say we want to field customer, and it's shipped to be unique together, but only in the case where it's shipped is set to false. This will do basically the same as the other example on the previous slide. But it will do it only on rows where that condition is true. So this is useful for a lot of things. If you have any rows with null values, one row with null is considered different from another row with null. So if you want to have unique constraint saying only one row should be allowed to be null, this is basically the only way to do it. You say, you filter by the row that is null and say it has to be unique on some other way, and you can have a separate constraint constraining other values where it's not null. Yeah. And another new type of constraint in Django 2.2 is check constraints. This is similar to what we've had in Django for quite a while, where we have validators on fields. That's basically when you set a value, it will check around some validators to check if the value you have entered is within some constraint. But new here is that you can make the database to this. So you can say, for example, on our monthly sales targets, we can say the month must be in the range 1 till 12 inclusive, which basically it's in the Julian and Gregorian calendars are the valid months. And we don't want any rows with values outside of that. And this is unlike the normal validators in Django. This is validated by the database. So this will protect you if you have bulk inserts, bulk updates. If you access the database outside of Django, it will still be checked. So it can be really useful. OK. And then we have partial indexes. This is similar. In Postgres, it's actually the same basically as the partial constraints, except it doesn't limit you. But this will help you maybe speed up some indexes, some queries. Or if you have a table with a lot of null values that you don't want to have in your index, you can specify a condition on the index that just index the values that don't have null. Or in this case, just index the unshipped orders. So those are the ones that we most often access. So we want the queries on that to be fast. OK. Window functions is another new thing in Django 2.0. My quick example of this is for each order, I want to fetch the idea of the previous order from the same customer. So yeah, our output here will be 1. So we use the window class from the arm. And we use a function called lag, which is referencing a value in a different row in the query set. So in this case, since I specified 1, it's the previous row. I could specify 2, and it would go two steps back and similar. And then we say partition by the customer ID, because we wanted an order from the same customer. And then I specify that you order it by the created time to get the previous one. And this results in a query set where we have annotated an ID on it. And this is a really basic example, but it can be really powerful. If, for example, we want to calculate a cumulative sum of gross amounts for each order where it grows, or we want to compare the total sales amount on an order to the previous order, for example. So yeah, this has a lot of uses. If we want to extend the ORM with our own functionality in cases where we don't really find what we need, it's also possible. So one example is, OK, the database has a function that I want to use, but the ORM does currently not expose it. Well, we can create our own subclass of fun, which you've seen earlier, and specify that the function used should be, in this example, round, which is doing a rounding of a number in the database instead of having to do it in Python code. So this is everything that's required to use that function. Use it like any of the other annotations that we have from Django. And you can also do more complex examples. So in this case, I'm implementing a function that takes a separate date and time column and combines them together to get a date time. If, for example, I want to compare this to another table where I have the date time as a proper date time value. So we take in two inputs, RIT2, the two columns, and we output a date time. And this one is implemented separately for the different database backends. So in this example, I'm going to show you the Postgres code. So we implement this function. It takes in arguments. And we annotate the given values with some extra information. So in this example, we want to add the time zone to be sure that we get the value in the intended time zone. We specify a template, which Django will use to generate the actual SQL. In this case, we have the two values we get in, and we append the time zone. And then we do SQLCast to a timestamp, TC. And then we will allow this to be done by Django. And we can use it like this at the bottom here. So basically, we specify the two fields that have the date and time. And the output of this will be at date time. OK. Last example, I want to write some custom ORM. Custom SQL, sorry. OK. First example, I want to use the age function in Postgres. So I annotate my query set with raw SQL class. Or this same thing can be done using extra. This has a lot more options. So yeah, check out the documentation if you are interested. Or if I actually want to write the entire SQL myself, I can do that using raw on the query set. Or I can actually build the entire thing myself using just a database cursor where I can run my own SQL and get any output. So we execute SQL and use fetch1, which will return one row. In this case, it will be a tuple where the value is just 2. OK. And there's so much more to Django ORM that I've been able to fit into 30 minutes. So please have a look at the documentation. Specifically, the ORM query set API documentation is really useful. Yeah. So thank you. If you want to contact me, you can find me on Twitter. I will publish a blog post after the conference with some more details. And yeah, we also have a stand. So you can find me there if you have any questions. OK. Thank you. Thank you. All right. Thank you very much, Seager. That was very informative. I just realized I need to go back to the office tomorrow and rewrite all my queries.
about custom constraints and indexes. A quick example of window functions using the ORM. And The first one is you can add your own custom this example, I have a method to create an order, in Django 2.1 is that Django has support for You can specify what formats you want, if you from Postgres. In other databases, you will most will generate a lot of queries against the second example there will not generate extra separate query, but it will be run once for an queries. So just be aware of this and know that it's worth knowing that it is an option available subqueries, which is something that the Django arm got in, I think it was 1.11. So it's been also use subqueries to do aggregation. So say we extract the year from the date and the month constraints and indexes is new. Starting in Django 2.0, I think, you can specify custom indexes. And starting in Django 2.2, you can and you can have a separate constraint here is that you can make the database to this. in the Julian and Gregorian calendars are the most often access. So we want the queries on that it by the created time to get the previous one. cases where we don't really find what we need, have from Django. And you can also do more compare this to another table where I have the values with some extra information. So in this Postgres. So I annotate my query set with raw SQL Django ORM that I've been able to fit into 30