I Didn't Know Querysets Could do That by Charlie Guo

Speaker 1: Come on, no, yeah, yeah, yeah.

Speaker 2: Hey everybody, thanks for coming. I didn't know query sets could do that is something that I've thought or said to myself a number of times over the last few years while learning the ins and outs of Django. And I was hoping to share some of those moments with you guys today But first, you know, why is this important? Why should you care about the words coming out of my mouth? If you're a devout reader of the documentation, then to be honest, this talk might not be that interesting for you But if you're more like me in that you read just enough of the documentation and or stack overflow in order to solve the problem you're currently facing and then you put it back down, then it might be worth a listen. Right. A lot of the stuff that, a lot of these solutions that I've encountered, I've only encountered them because they were relevant to what I was working on.

Speaker 2: And Simply knowing that I could have done something in native Django would have gone a long way towards figuring out how to do it in Native Django. And even if you're fairly advanced, a lot of these concepts have only been fleshed out in the last few versions. I know for a fact that at least a couple of these examples could not have been done before 1. 8 because in order to do them I was hacking around private internal functions. I want to offer practical examples in the hopes that you can use them towards your own code and achieve if not best practices, at least better practices. That all having been said, let's dive in. Uh just as a sanity check for everybody, query sets are the things that are returned when you do a model query. Django, right? So every time you say article or user.

Speaker 2: objects. all or article. objects. filter, you're working with query sets Later on in the talk, I'm going to run through some examples of lesser-known query set behavior, and those revolve around a pretty basic e-commerce app, right? Everybody likes making money. Just to be on the same page, I'm going to take a look at some of the rough model references that we're going to be using. We've got products with names, prices, and sellers. um orders, right? And each order has many items. So each item has two foreign keys, a quantity, and a float unit price. Hopefully that all makes sense. Now if you've done a Django app that's even slightly more complex than a to-do list, you've probably run into some of these methods

Speaker 2: The ones on the left return query sets, meaning that you can chain them. You can filter and then exclude and then reverse. The ones on the right return something else. That could be a model instance, that could be a Boolean value, that could be a dictionary full of stuff. I don't want to spend too much time here, but uh there are a couple of things I want to point out about annotate and aggregate. They are somewhat similar. They both accept an expression. Here we're using the count and sum classes to uh define the expression that we want to compute. But annotate will compute that expression for each item in the set, right? And it returns each item with a new property. storing the result, whereas aggregate will compute the expression across all of the items in the set.

Speaker 2: And it returns a dictionary with the final value. You know, with annotate you can add some new value and then use it later on in the filter expression. If you provide a keyword argument, then that becomes the name of the property or dictionary key. But if you don't provide one, uh Django will automatically generate one by using the name of the field, uh double underscores, and then the name of the function. Right. Um kind of a subtle point to note is that actually in the top example um we are counting all of the items, despite the fact that the product class, if you recall, didn't have an explicit foreign key for an item on it. Right, which means that here Django is implicitly traversing the reverse relationship, counting up all of the items, and then adding that as a field on our final

Speaker 2: query set of products. And of course, if you aren't using these functions to count stuff, to sum stuff, to average stuff, you absolutely should. It varies a little bit based on your underlying database management system, but they tend to be significantly faster than writing a for loop by hand or using even Python's built-in sum function Cool. So beyond those more common ones, there are some lesser-known query set methods, right? These generally revolve around optimizations or you know interacting with the underlying database table structure. You may or may not know that when you access a foreign key on an object, Django goes back to the database, there's an extra query

Speaker 2: uh in order to populate the foreign keys information, right? So when we print the seller ID on a product, it takes not one but two queries in order to execute that print statement. But and I should note the same thing happens you know with many to many and uh many-to-one reverse relationships, right? Not just porn But we can use select related to compress that down to one. Select related takes in a list of arguments that it will go ahead and join in the underlying SQL allowing you to save time later when accessing it. The big limitation of Select Related is that it uh it's limited to foreign keys because it's doing sort of the joining in the underlying SQL But it is very similar to prefetch related, right?

Speaker 2: Which allows you to do the same thing with reverse lookups and many fields Right, prefetch, rather than doing it all in the SQL, it will do the joining in Python. But you get to do it up front once rather than many times ad hoc like we might do when rendering a template. But what if you have the opposite problem, right? What if you don't want more information from the database but less? This could be, for example, because you have a model with hundreds of fields on it and you only need to access one or two. Then you can use only, which as the name implies, will only fetch the explicitly listed fields. If you know which ones you're going to need, you can optimize your lookups by only grabbing those. Obviously though if you access other fields outside of the ones that you

Speaker 2: asked for, you kind of lose the performance benefit because you have to go back to the database anyway. Defer is the other side of this coin. It uh retrieves all of the fields on a model except for the given ones. You might want to do this because you have a field type that's particularly expensive to convert to native Python. Maybe it's Some geospatial data, maybe it's something even more custom than that. And so defer lets you punt on uh having to do that processing The last one of these methods I want to touch on is in bulk, mostly because I recently discovered it and I think it's super cool. You pass it a list of IDs and it returns a dictionary mapping each ID to the associated model rings.

Speaker 2: To me this is interesting because there's a type of query that I run into a lot, right? Suppose you want to get all of the products ordered in a given month You'd probably start by filtering for all of the items ordered in a given month. You would use values list, right? But equals true. and distinct in order to distill that down to a list of product IDs. And then you can pass those to in bulk in order to just rapidly generate a mapping between it The other way I've done this is using filter. You can say filter product ID in list, that also works. As far as I know, these are kind of the cleanest ways to do this lookup, but if anybody knows a better way to do it, please come talk to me after the talk. Uh for whatever reason I end up writing this style of query a lot and I would love to reduce my lines of code.

Speaker 2: Now an interesting thing about the filter and exclude methods that we saw earlier is that um When the underlying SQL is generated, it's always generated using AND pod, right? So if we look at this example, the resulting SQL is that we select all of the orders where the status is shipped And it was ordered yesterday. That begs the question, what happens if I want to get all of the orders where the status is shipped or it was ordered yesterday? All right. Um to answer that question, I want to look at not searching orders, but searching users. Suppose uh your customer support team says uh we really want a flexible search view where we can just type in a keyword term and it will return all of the users you know across

Speaker 2: First name, last name, email, and we can use them for our tickets, right? The Brew 4 solution is to write three queries. First name, last name, email. Convert them to lists and then concatenate them together, right? Um and this solution, while it does work, is not the most efficient You're hitting the database not once but thrice, once for each field. And then you are spending time not just converting each of those query sets to lists, but then putting those lists all together. And lastly, you know, your final object type is a list, not a query set, which means that you can't filter on it further, you can't order it, you can't remove duplicates. easily.

Speaker 2: And this starts to break down pretty quickly, right? Requests will start timing out without you know too many results that you're trying to return. And like I said, if you add extra constraints, that just adds to the response time even more. Unfortunately, we have Q uh and Q are these great objects. They encapsulate query constructions. And you can combine them with various operators like AND, or and NOT. And so if we were to rewrite that previous query, we would do it by combining, creating three Q objects, right? One for each of our fields, one for each of our constraints. And then joining them with a bitwise OR operator. And you can see what the underlying SQL is. So we get a query set, we can order it by last name, we can you know ask for distinct.

Speaker 2: And best of all, it all reduces down to a single SQL. Theoretically you can create as complex of Q expressions as you want, as long as they can be combined with bitwise ors, ands, and nots. I suppose sales comes to you and they say, uh we really want you to build us some dashboards, right? We need to track our metrics, our KPIs. So you decide to write a function called itemTotal that takes in a set of line items and returns the total. At first you might be saying, oh, totally know how to do this Easy peasy. Let's just use a sum aggregate across our query set, right? And then get the value out of the resulting dictionary. But let's recall for a second how we defined that model

Speaker 2: The above approach totally works if we had a total price field rather than a unit price. But because we structured it this way, we are not taking the quantity into account for each of our line items. It might be tempting to just say, screw it, you know, we'll write a total price method, and then we'll use a list comprehension and a sum. to get that total. And this works, right? Totally works. But if we're taking our own advice about using annotate and aggregate where possible, we can do better. That's where F comes in. While Q represented a query constraint, F represents an implicit reference on a model field or database column. If that uh if you don't know what that means, that's fine. It it took me forever to understand what that means.

Speaker 2: But let's look at it concretely. For this example, um We're rewriting that product and sum as an expression, right? But rather than saying, so for example, if we did it with a for loop, we would say for item in items item sum plus equals item dot unit price plus item dot quantity, right? Or times item dot quantity, excuse me. But we can rewrite that using a sum class. uh and using f in order to implicitly create this expression and without having to access any individual item instances compute the aggregate sum hopefully that makes sense uh And let's look at one more F example, you know, this time just to

Speaker 2: really illustrate that self-referential aspect. Suppose now your manager says, you know, Charlie, sales are up, everything's great, but I want you to add a field to our product so that we can keep track of how many times each one has been purchased. And at first you're like that's dumb, why would you do that? Um but it's your boss so you have to do it anyway. Um and the for loop to do it right is extremely simple. Uh and to be honest, we can't really do much better than this in terms of performance. But while we can't save much time, we can save space. And using F in this case can actually avoid erase condition. Right, so what's happening is we're using the update method on a query set to update all of the values at once, and then we're using F to implicitly refer

Speaker 2: to the purchases yield on each of the products, incrementing it by one and putting it back. This converts down to a single atomic database transaction, right? So in the above example, it's it is possible if you had two processes trying to do this at once that one could clobber the results of the other But if we use update and f, we avoid that race condition. Now as it turns out, F objects are just a single example of a much more general Django class called query expression. You might know some of these from the aggregate functions, right? But they actually have a very close cousin in Database functions. So the ones on the right, you know, while performing a query you can include and they will inline, concatenate two fields together, compare two and return the smaller, convert to upper or lower case.

Speaker 2: Right. Um and some of you might be thinking, hey, those look like a lot of SQL functions that I know. Right. Um that's because they are SQL functions. You can subclass the database function class, which simply takes a list of arguments and then the corresponding SQL function to apply them to. Right. So in the top example, um without having to iterate over any products, we're converting all of the names to lowercase and adding that right to each individual product And in the bottom example, we're taking all of our users using the split part function in SQL to break the email based on the at sign delimiter and just grab the second of that split. Hopefully that all makes sense. The value class that you're seeing there is simply a wrapper around raw Python value, right, to help the function class make sense of everything that's coming in.

Speaker 2: Um so some more you know Ferry expression subclasses, Fs, aggregates, functs, and values we just saw. Expression wrappers we'll touch on in a minute. And conditionals actually are a really powerful subclass that allow you to implement conditional logic inside of a query. So you can say add this computation, add this extension if a certain case is true, otherwise add a second, completely different computation. And these are these are pretty powerful, but if you find that you need to write your own query expression, it's actually not that bad. You only have to define four or so methods. As SQL, get lookup, get transform, and output view. If you need to write, if you need to customize the SQL output for PostgreSe or MySQL or SQLite, you can define an as

Speaker 2: vendor name function, right, where you replace vendor name with the name of your desired backend. Since writing your own expression is a fairly advanced exercise, I'm not going to delve too deep into what exactly these entail. But rather I'm going to point you to the excellent Django documentation and leave it as an exercise for the listener. It is at this point that I do have to admit one of our previous examples wasn't exactly correct. That's my B. Technically speaking, this code will absolutely work if unit price and quantity are the same type of field. If they're both integers, Django can say, okay, an integer times an integer, we probably want an integer back out. But because unit price is afloat, we have to explicitly tell Django what field type we want to come back out

Speaker 2: And as you can see, we're invoking the expression wrapper class to do this, right? The final code ends up being a little bit more verbose, but you get some more fine-grained control over it. We're specifying our output field as a float field in this case, so that we can coerce it to a float Now hopefully ideally you're convinced that you can execute 90 something percent of the queries that you need in Django natively Alright, but on the same chance that these haven't solved your problems, you can roll your sleeves all the way up and start writing some bare bones sequel. As a side note, I just want to say one quick thing If you're writing raw SQL, you should probably rethink what you're about to do. Now this isn't to say that you should never write raw SQL, right?

Speaker 2: There are plenty of valid use cases where it's the right thing to do And there are absolutely performance and convenience benefits to be had. However, there are probably different, more maintainable ways of reaping those same benefits. If you're looking for speed increases, consider refactoring your code to be more efficient, right? Maybe using some of those query set tricks we just learned, you know, or introducing caching. When it comes to convenience, you could write raw SQL or you could consider restructuring your models and your tables to take advantage of more of the native functionality Writing your own SQL, you know, while great in some situations, means that you lose portability options when it comes to changing database management system. And of course, if you're using dynamic values, you start opening yourself up to the possibility of SQL

Speaker 2: injection. Anyway, now that my PSA is over, the first way that you can start writing SQL is the extra method, right? It lets you inject specific clauses into a query set 's generated SQL. You should be aware that this method um while not currently deprecated is planned for deprecation. So if you really need to use it, make sure you file a ticket with the Django project so that the core devs are aware of your use case and can ideally try to build some native functionality to take care of it. So in this example, right, we're introducing an is recent attribute into our Select clause and the generated SQL looks like that. Pretty straightforward. There are a bunch of different clauses that you can work with using action.

Speaker 2: You've got select, where, tables, order by. And if you need to start escaping dynamic parameters, for the select clause, you can use select params. And for all the others, you can use regular params. I have no idea why there are two different keyword arguments for these, but there are a number of Django core devs floating around, so I would suggest that you ask them. And if this really truly has not solved your problem. You can just write some raw SQL. Raw will just take a string, pipe it out to what's beneath, and send you back the result. This example is very trivial and should not at all be you know done using RawSQL for a couple reasons. One, I was too lazy to come up with a more complex one, and two, I am employed as a Python programmer and not a DDA.

Speaker 2: So let's recap. We had our basic methods where we can annotate and aggregate where possible. Our advanced methods where we can reduce joins with select and pre-fetch related and use only and defer to partially fetch data. Our Q objects, which were encapsulated query constraints. Our F objects, which were implicit field references. Our database functions, the sum average count you might know. Compact rate or lower, you might not. Query expressions, which are crazy powerful, and see the docs if you need to write your own. And raw SQL, right, with extra and raw. My name's Charlie. You can find me around the internet, usually at CharlieRguo. Please note the R. There is another Charlie who does not use the R, and he gets a lot of my email.

Speaker 2: I write stuff. This one time I wrote a book. It's called Unscalable. It's a collection of interviews with startup founders on a theme of a few things that don't scale. That's my shameless plug, and thank you. How am I doing on time?

Speaker 3: Yes, so we have about five minutes for questions. Uh if you have them, please uh use the microphones at the ends of the aisles.

Speaker 4: Hi. Um I had a quick question about a query that uh we had some trouble with Uh something when we had to refactor due to some changes that we were going through where we had to introduce an OR statement into an already complex query. Um so we had an OR statement which then we fed into a filter uh with a bunch of other ands. And by the time we were done with this query, it was so long running because the data sets were so large that we had to break it up into multiple smaller queries to be able to have it run effectively and not time out all the time. Would you have any suggestions for how we might be able to handle that and be able to do that in a single query set?

Speaker 2: Yeah, um again, gonna uh emphasize that I'm not employed as a DVA. But uh I would say that um it is possible to do some things where you essentially uh like use the cues and then stack filters on them is that what you guys originally were doing or uh

Speaker 4: um so we we took the cues and ored them into one larger queue essentially and then use that in a filter with other criteria as you know anding all of that together And it was just massively long run. So I don't know if like we did something wrong there, if there was a way we could have reorganized that so it would have created a more efficient query.

Speaker 2: For on each query set you can print, I think it's dot query, and it will spit out what the SQL is actually generating. So figuring out from there what to refactor and what to break down.

Speaker 4: Okay, thanks.

Speaker 5: Thank you, that was great. Um my question is I ended up in a situation where I needed to query the same model. Imagine that different categories, right? But they wanted to have uh two slices. Let's say the first one ten items, instances, and and the other one five. Is that even possible?

Speaker 2: I don't know if I understand the question.

Speaker 5: So you need one model, imagine article, and I did I I need ten items from one category. menu right and five items from another category in one query set.

Speaker 2: Oh so you you have two queries and then you want to join them together essentially Or if you know so if you know um for example the two that you generate if you can generate a 10 and then you can generate the five, right? Um if you have The code do that. You could put them both into queues, right? And then use a queue to add them together. I think.

Speaker 5: I was actually using the queue for the categories. But uh the situation was like once you take a slice you can't actually have another slice There was a problem. I don't know if that is actually.

Speaker 2: Um if you know which IDs you need, right? Like if there's a limit, if you can order by ID and then just take the first ten. This would be a good place where those extra database functions would come in handy, right? Because you could say, you know, if this is greater than nine other ones, right? Then stop. Um but uh I would suggest y I can take a look at it after you want if you want after we've talked.

Speaker 5: It just uh it seems to be a bit weird that having two different query sets for the same model, you know, just to get items. to differ from different categories kind of thing. So I didn't I ended up having two query sets at the end. Thank you.

Speaker 6: Yeah, I I just wanted to clarify on your last example there. Um so when you do a raw query, you can add any number of computed columns on and it'll just return them back as if they were part of the model

Speaker 2: Uh if you I believe if you use an as, yeah, it just sort of applies them as properties.

Speaker 6: You'll just have new properties on your model that are sort of temporary properties just from your query.

Speaker 2: I believe so, but check the documentation.

Speaker 6: Okay. Thanks

Speaker 3: Um, thank you. A big round of applause for Charlie.