Building effective Django queries with expressions by Vanessa Barreiros

Speaker 1: Um I'm very happy to be here today and also this is a subject I've stumbled upon not long ago and I'm eager to share with all of you here. Um this talk um actually came up from a challenge with a project I work on um which has some tables with more than millions of records, so more than 10 million records So performance was required and we have to do we've had to do some optimizations and one of the most things we use it are query expressions So, okay, just some context about me. I am Vanessa. Um I'm from Recife, Brazil. The red mark to illustrate better, so I've come a long way to be here today. And also I'm a jungle

Speaker 1: girls organizer at my hometown, Recife. And I also was part of the first female majority um hackathon in my hometown Hey girl. And the the box in blue is my Twitter handle and I'm gonna post the slides there um after this talk. And also if you have any questions, comments, or general feedback, please feel free to reach me there As I said, I work as a software developer at Vinta Software. We are a product consultancy company from Recife, Brazil. Which works with our clients mostly from the Bay Area in New York to evolve the products using top-notch UX and development techniques. And we was we work mostly with Django and React.

Speaker 1: We also love open source, so we have a lot of tools on GitHub that you can check at github. com slash pinta software. And we've got a playbook as well with all our processes learned so far, so you can check it out on our website. It's pretty cool. So, okay, on to the topic. We're gonna talk about career expressions today, but first I want to ask Paul a question. Have you ever tried to fetch complex data in an application and got really confused? It's a rhetorical question, almost that. But it's happened a lot to me, so I can only imagine uh how many times it has happened to all of you here And as I use Django in my day-to-day, I thought some time ago that this framework is 14 years old, so a lot of people use it, so it must have something to help me get whatever I want in the best possible way.

Speaker 1: And I also uh I've worked with applications that grew large over time and uh with changes on business rules and context, um it became very common to create some redundancies along the way And uh we we end we ended up creating more and more fields in a database than we actually needed. And that's a reality of a lot of systems out there. And uh to mitigate some uh problem problems like this, sometimes people follow paths such as caching or denormalization. And by the way, if you're interested in denormalization, there's a great talk about it by Flávio Juvenal, he's one of Vinta's partners. You can check it out on YouTube. But if your application isn't really complex and doesn't it dimension in advanced techniques

Speaker 1: You don't have any excuse not to use your M and to do your queries, and we're gonna learn why and how today But we can't start talking about queer expressions without explaining what the ORM means. This powerful tool called the ORM. As you may know, in Django we have the ORM, the object relational mapping. And for those who don't know, it's in few words, it's a tool to translate or most of the time relational database dialect into Pythonic objects, namely models, so they become more readable and manipulatable. And it has lots of useful things that we can use to search for something in our database. So for example, we have uh Future, uh which we're gonna be using a lot today. And As I said, it has lots of abstractions to help

Speaker 1: to gather data much more easily than writing the pure SQL. But today we're not going to talk about simple filters. We're going to talk more about and dive into some concepts about the query expressions, such as Q and F objects, database functions, conditional expressions. And present the many ways in which you can appease your advanced query needs with Python and nothing more. With Django Buildings also. And all of that, we're gonna be using Q expressions. Which are smart yet straightforward functions that can be used to compute values on query execution. So for example, if you want to do queries with R statements, um Uh string manipulations, calculations, and so on.

Speaker 1: Everything at a time Django hits the database. We're gonna be using queer expressions for that So this removes the necessity of having extra columns there in our database and helps you generate readable and optimized queries. So for instance, um with a sample database of 500,000 records to get people who were born in a leaper, for example, instead of writing the SQL on top or maybe um the naive code on the left which run by the way with an average of almost four seconds. We're gonna write the code on the right, which run in an average of 0. 32 seconds, and for that we're gonna use the then query expressions learn it today. And uh to start to start with them, we're gonna understand more, we need to understand more about Q

Speaker 1: objects. So, because they are the most common solution, and if you are familiar with Django, um you must have used it a lot They are usually the first contact one has with complex fearing because its API is not too difficult to understand. So they serve as a nice call encapsulator. uh for queries with different conditions so if you want to do queries with or statements or negative statements Q objects are what you're looking for So to start with a simple example, if we want to fetch um people who were born in different generations, um by the way, birth date birth date is a field in our personal model. Um we need to change a queue objects like this, passing each one of our conditions into a queue object and then uh separating them using the OR

Speaker 1: oper operator. And Django is smart enough to generate Desk L and filter everything for us. So in case you want to do to add more conditions, It's possible to compose complex filters with queue objects as well. So remember our first filter by years now we've added a location filter inside a queue object as well So this filter this will filter by anyone who was born in one of these ranges, uh range of years, and lives in California. So you can mix um queue objects using the operators and uh the impetus rand or the vertical bar. So to include more conditions you can just um add more Q

Speaker 1: objects and separate them And it's all good. And just a tip, because um it's very extensible, complex figures with Q objects can get huge over time. So and I know some people say that good code is self-documenting, but it's advised to comment just a little bit so you won't lose time trying to understand um the code you wrote a year ago, how it works today And as I said before, you can add as many filters as you want, um, such as bullet type, job, as I did here And uh so moving on, let's say that um our application has an interface that users can search stuff. Um and the user can input whatever they want and we're gonna search for all possible features f fields using insensitive contains for example, um

Speaker 1: the lockup. And that will work, but What if the user wants to search just for the first name, for example, or a job title? We can give our user the freedom to search for whatever things they want. And that's possible if we mount a queue object before doing the query by using an approach with some ifs in Q. add. There are some other approaches, but I've chosen chosen this one. So it will check which fields the user has selected, for example, in our um filter, the the UI, and add conditionals accordingly. So for example, email field was not included in the search, so it won't be added to our final filter.

Speaker 1: And at the end you're just gonna pass the mounted filter, the mounted queue object, to the RM filter, and it's gonna work. It's gonna find, for example, people with mark as name, first or last, people which works which work as a chief marketing officer, trademark, attorney, and so on And just a quick off-topic, if you are more familiar with SQL and want to see what your database reads, you can print your query without query. um the yellow arrow and you're gonna see the output below in this case for Postgres databases. I've omitted some info just for simplification as well If you also want to format it, you can use a lib called SQLParse. It's a jungle dependency, so it already comes when you install

Speaker 1: on Django. The point is that it's important to understand that at the end everything is SQL. It took me some time to realize that. And as you can see, Django handles everything for us, so we don't have to worry about writing super long SQL statements. Which is not quite the case now, since we have a very simple example, but that becomes a reality once your application scales Another base resource which is very useful for queries is called F expressions or F objects. And to start with it, I want to show an example first. According to the American Red Cross, people can donate blood every 56 days. So we have our personal model here that you already know about with

Speaker 1: your field less donated and can donate on. And theoretically speaking, uh when less donated gets updated, we should donate uh we should update uh they can donate on as well with fifty six uh days ahead. Uh if you want to get people who can already donate You could do something like this using Future, um, the list then or equal the today's um Date. And but what happens if the lessonated field is updated but can't donate on is not for any reason. Um it's critical because uh we can guarantee that this field is gonna have a reliable value. And uh it's also critical because we can let people donate blood before the 56 days. So

Speaker 1: this code uh could cause serious inconsistencies And to solve this problem, you would have to use F objects, um, because the value would always uh be calculated at query execution. So it will always be correct. NF objects are used when you want to reference model fields or reference annotations without having to load the values into memory. So you can do basic rhythm medic operations on query execution. For example, as I said, people can donate blood every 56 days. So to know when they can donate again You can annotate the date using F to get a lessonated value and add um 56 days using time data, for example. And without it, we will

Speaker 1: you would have to write the raw SQL because uh or feeder with Python. um which we saw in the beginning of the SOLC decreases performance a lot because there's no such way to do operations like this um in an effective way without easy knife as far as I know So we can also use F to do operations with different fields or to reference annotations as I said. So from our last example, we annotated the exact date in which pen uh in which people can donate blur again Use an F and time delta, but now we're gonna use F for everything. In this example, uh we run a blood bank and want to check how many bags are missing for our goal And to do that, we can use a notation of how many

Speaker 1: blood bags we currently have in our bank and subtract this value using F from the goal quantity, which is a fit in our blood bank model. So we're gonna get exactly how many bags we are missing from our goal. 500 in this case So in the previous example we've used F with two fields but both were integers. So in case we we want to do operations with different fields, uh with fields of different types, I mean We have to use something called expression wrapper because when you try to, for example, in our event model um sum the event duration with the the starts uh the ends on the stars at sorry um jjango is gonna it's gonna throw uh

Speaker 1: field error because it's a um duration field and a daytime field different types So that's solved by wrapping our sum in an expression wrapper, in something called expression wrapper, and setting the output field as a daytime field. And Django is gonna is gonna be smart enough to do the operation And give the results the result to us in a daytime field. So for example, if we have an event that starts today, has a duration of one day, the end zone should be tomorrow, same time And at the end, we can mix uh F and Q objects all together to create unique queries. Um in this case we want to get people uh whose first name equals their last name and don't have mark in their job titles.

Speaker 1: I have nothing against marks, it's just uh an example. And as you can see, query expressions are very modular, just like other resources we're we're gonna see soon We've seen how to create filters, how to access and do operations with different fields, and now we're gonna use we're gonna learn and use how to manipulate data with database functions. There are basically some abstractions that to help us to get our database to access database level functions as the name suggests directly. So for example, to count how many pads we have in our system How many pet types I mean? We have to use a function called count in an annotation. In that just like most of the database functions is gonna be available using the

Speaker 1: Field you pass, the name field, the name of the field you pass, dunder the database function name. So in our case we have type dunder count so that at the end we have the distribution of pet types easily And there are more than 50 database record um records sorry uh database functions and so I won't show them all today because of the time, but you we're gonna see some of them and see how useful they are. Database functions use it with annotations are very important resource, especially when performance is needed. So another good example, we have our personal model here And we have it has two fields, first name and last name. And I want to search for a string in their full name.

Speaker 1: So adding a property would do the trick. Uh I just have to load everyone, every person um into your memory and do the search. But there's a problem here. Uh since it's a property it cannot be used in queries. So if I want to to search um do a search in everybody's name in this scenario I will have to load everyone into memory. That's not what I want And a solution to that relies on in an old function called concat, which stands for concatenation. So you can annotate the full name by calling this function with the field names. By the way, I have to put uh I had to put a value there because Django if I if I had just put the empty string, Django would try to look for a field name at

Speaker 1: empty string. And it will um throw an error. And after annotating the full name, I can use uh as many strings lookups as I want, and it's gonna work. Just so you see I wanted to bring this GIF uh uh just just to compare our solutions um with the full name property and the annotation. So at first it's very fast, but as I add more zeros to my limit, the worse it gets. So the last frame was like 500,000 records and took about four seconds to load everyone's full name And in a big application such as the one I work on with millions records, that's too long for the end user and we cannot let things like this happen. In back with our full name example, here's a tip

Speaker 1: to avoid calling the whole annotation and importing everything every time you want to load everyone's full name You can add this annotation to a query set or a manager. I picked a query set just for simplification. And add the query side to the model using DO as manager So now you don't have to worry about it. Whenever you have the person model, you can just call annotate the full name, annotate full name method, and it's gonna work. And then you can do the search Um just like you as you want. And another great example is a database function called callis. It returns the non f no f the first non-nove value given at least two field names.

Speaker 1: For example, our person person model has a nickname field and if they have one I want to show it and if they don't, I want to just show the first name. And that works, but there's a pitfall. As a sec returns the first no -nove value, if anyone has for some reason um an empty string as a nickname, the annotation will return an empty string. That's not what I want. And a solution to that is using another database function called nullif , added in Django 2. 2, which accepts two expressions and returns returns none if they are equal. So in our case, in our case we compare our field nickname with an empty string value. And again, I had to use the value

Speaker 1: function because if I hadn't, Django would look for a field called empty string. And that's gonna work. We've seen that we can do some conditional queries with no no leaf, for example, in our last example. But if we want to do um some complex queries with just one turn We can use conditional expressions. So they allow you to use um conditional logic um in queries, that means filters, updates, annotations, and such things And without them um you would have to use Python and the code would would be bigger. So from for our example, let's say that people can order things for some um um marketplace, commerce. And let's give them discount if these orders

Speaker 1: um if they are to this order if they are loyal customers So using the orders for today, let's get if the user joined our system this decade. In this case we're gonna give them a 20% discount In case they join it between 2000 and 1995, we're gonna give them 40% discount and so on. So by the way it's 0. 8 and 0. 6 because the discount is subtracted subtracted from the full price. And we can understand this code, but it's not really too easy to read A better way to do it would be using case and when expressions. So case is very useful because you can use like ifs, elifs, and else's in our in your query in little to no time

Speaker 1: And it's better because the value is calculated at the time the query it's executed. Um so it will be much faster and if you want, um you can You can also filter the result at database level, so you hit it only once. So and as it's an annotation, we don't lose the the original order total. And at the end we can mix everything we learn it to annotate uh with someone's um birth year is a leap year, for example. So just to give some context about how to calculate a leap year, it's very difficult um it took me some time to to understand it should be exactly divisible by four

Speaker 1: Um if it can be exactly divisible by 100, it's not unless it's exactly divisible by 400. It's a bit tricky, but just to get the idea. So we start by doing the needed annotations with database functions, cast, um, to transform the value from extract here to an integer. Because extract here at database level returns a double precision floating point that I don't know I don't know above it. I've never personally used that as well. But that's what the error said. Enough objects to do their operations with reference annotations to get the modules of the integer, for example. And then we use the previous annotations into a conditional expressions with Q

Speaker 1: objects in the when second line. We define false by default and we can do all the math to make it true. And finally, uh we filter by the annotation we just did, and from now on it's just testing if we did the right thing. So at the end we we have done um calculations and conditionals, just hitting the database once. It's pretty amazing. And uh these are the references uh that helping me inspire me to do this talk. Um It has the links that I'm gonna post I I'm gonna post the slides on the Twitter. You're gonna check it out there. And that's it. Thank you

Speaker 2: Thank you Vanessa for your talk. It was very interesting. To confirm it appears that Q objects can handle nested parentheses If this is the case, have you used Q objects to create complex filters inside a DRF? Theoretically, this would allow complex filtering on the API endpoints. Thanks again.

Speaker 1: Sorry, can you repeat? I just

Speaker 2: Can you confirm that the Q object can handle nested parentheses? So um a group inside of a group, for example.

Speaker 1: Yeah, it can. You can debug this with a um dot query if you want to to see the scroll, the output.

Speaker 2: Okay, that's cool. Now the second part, have you used Q objects uh using Django Rest framework?

Speaker 1: Yeah, but um I'm not too um proficient with that. But I use it but it's it works. Um normally it just as I I'm using job

Speaker 2: Okay.

Speaker 1: If we have if you have uh any specific um scenario we can talk after But

Speaker 2: that's good.

Speaker 1: Okay.

Speaker 2: Thanks again.

Speaker 1: Thank you.

Speaker 3: Uh I have a question about the X-track year doesn't return an integer. Uh do you know if it's in all databases like Postgres, MySQL, and what Whatever, I'll take this.

Speaker 1: Um are you talking about the flow on the double?

Speaker 3: Yes.

Speaker 1: Um I'm not sure.

Speaker 3: Okay.

Speaker 1: I'm not sure. Um I've used it Postgres for um to make this Um but I think that MySQL works um the same way.

Speaker 3: Okay.

Speaker 2: All right, do we have any others? No. Okay. In which case can we thank Vanessa once again?