Pushing the ORM to its limits

Speaker 1: Thank you. Can everyone hear me? Yeah. Do I have slides? Yeah. Okay, hello. Uh I am Sigur and I am a developer at uh Coroneal. 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

Speaker 1: URL. Okay. Um 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 yeah various other minor things. Then I'm going to deep dive into subqueries, which is something you can do in the RM 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.

Speaker 1: 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. Okay, uh so uh some quick tips and tricks. The first one is uh you can add your own custom query sets and managers to your models Uh 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 uh confirmed orders is a single thing that we do all the time. So what you can do is have

Speaker 1: 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 in case I'm looking at undelivered orders. And this can be instantiated on the order model by setting the object's attribute like this. And then you can access this through the objects reference on your model. And The query set

Speaker 1: uh 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 Okay, another quick thing. Uh if you are ever wondering why is something doing something weird or just wondering what 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

Speaker 1: 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? Yeah, and it has a lot of options. You can specify in this example I've said verbose true, which is giving me extra output. You can specify what formats you want if you want it to be Uh to do analyze, which is a deeper check. Um 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, um

Speaker 1: another quick example uh 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 the top there will not generate an extra query in the loop. And the second one is fetching all the lines. So

Speaker 1: 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 uh join in Python instead of the database. So it's a separate query, but it will be run once for an entire query set. Okay. And uh 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

Speaker 1: be aware of this and know that you actually have a problem before you optimize too much. Yeah. And another quick tip, um, you can also use the database to avoid race conditions. Select for update will uh 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

Speaker 1: and when the transaction ends, uh in this case when the width block exits, it will release in the database. And this is really useful in some cases. Um 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. Okay, so let's have a deep dive into subqueries. Which is something that the Django Orm uh got in I think it was one point eleven, so it's been around for quite a while. But it's really powerful and allows you to do a lot.

Speaker 1: 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, annotate method, sorry, uh on the query set, which allows us to add new fields basically to the resulting objects And we use the subquery class, which wraps a separate query set where we can basically do anything we want. And we can also use a special class called Outer

Speaker 1: 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 I'm 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 write 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 uh the result of this is that I can say

Speaker 1: latest order time on a customer. And my example is wrong. It should have been a timestamp. Sorry. Yeah. And um it's also possible to do exists queries where you basically replace uh subquery with exists. And then you can check basically does something exist in the database without actually fetching the data Yeah. Um 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

Speaker 1: 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 eight. This uh 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 orm. But yeah, this is the or at least this is how it's documented. Then we annotate the sum. This basically is a group

Speaker 1: by that does a group by with the two top levels, uh calculates the sum 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 uh 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

Speaker 1: where we have three values. Um And we want to select only orders placed on a weekend date, which is uh seven and one if you use the Django RM. So the last one here should not be included. Okay, so yeah, we do the same filtering where we say weekday is either seven or one. But it can be either seven or one, 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 uh sum is implemented in Django, this will

Speaker 1: add a 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 the same table, same values. And by replacing some with just funk 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 uh

Speaker 1: 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 funk 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 it's not the prettiest. Um it does work though. So it's worth knowing and Yeah, and a note on this is uh sometimes it's really useful to inspect the query sets query attribute. Uh that's basically how I discovered this. I discovered that okay

Speaker 1: some ads are group by group clause, but this doesn't So yeah, hopefully we can implement this in Django at some point, but right now, uh this is a solution that works. Okay, um and then let's have a quick look on custom constraints and indexes Which uh Django has had support for unique constraints for quite a while, but um custom constraints and indexes is new. Uh Starting in Django 2. 0, I think, you can specify custom indexes. And starting in Django 2. 2, you can specify either conditional uh constraints or conditional indexes.

Speaker 1: So let's have a look at that. First unique constraint. 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 uh unique together and specify that year and month should be thou those two in combination should be unique. Um so yeah, this is 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

Speaker 1: multiple unshipped orders. Now we can set Why did my slide disappear? Okay. Okay, so we can set uh instead of setting unique together, we actually set constraints to a list of constraints. So in this case we say we want the field's customer and it's shipped to be unique together. but only in the case where its shipped is set to false. So 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

Speaker 1: this uh is useful for a lot of things. Um 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 is uh 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. Um 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,

Speaker 1: where we have validators on fields That's basically when you set a value, it will check run some validators to check if the value you have entered is within some constraint. Uh but new here is that you can make the database do this. So you can say, for example, on our monthly Sales target. Uh we can say the month must be uh in the range one till twelve inclusive. Uh 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 uh the normal validators in Django, this is validated by the database.

Speaker 1: 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. Okay, um and then we have partial indexes. Uh this is uh it's similar uh in Postgres it's actually the same basically uh as the partial constraints uh except it doesn't limit you. But is will help you maybe speed up some Indexes, uh some queries, um or if you have like if you have a table with a lot of

Speaker 1: 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. Okay, uh window functions is another new thing in Django two point oh. A quick example on this is for each order I want to fetch the ID of the previous order from the same customer. So Yeah, our output here will be

Speaker 1: one. So we use the window class from the uh orm, um and we use a function called lag Which is referencing uh an value in a different row in the query set So in this case it's you since I specified one, it's the previous row. I could specify two 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.

Speaker 1: And this uh 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 goes, 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. Yeah. 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 okay the database has a function that I want to use, but the RM does currently not expose it.

Speaker 1: Well We can create our own subclass of func, 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. Yeah. 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.

Speaker 1: 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, RT2 , 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

Speaker 1: time zone to be sure that we get the value in the In uh 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 a SQL cast to a timestamp TC. So we will 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. Okay, uh

Speaker 1: last example. Um want to write some custom ORM uh custom SQL, sorry. Uh okay, uh First example, uh I want to use the age function in Postgres. So I annotate my query set with a 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

Speaker 1: the entire thing myself using just the 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 two. Okay, and there's so much more uh to the Django RM that I would uh would have 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, okay. So thank you. If you want to contact me, you can find me on Twitter.

Speaker 1: 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. Okay, thank you.

Speaker 2: All right. Thank you very much, Sigur. That was very informative. I just realized I need to go back to the office tomorrow and rewrite all my queries