Making smarter queries with advanced ORM resources

Hi, I'm Mariana. Uh I work at LabCode Software Studio in Recife. Those are the great people that work with me. Uh uh as I have a pretty different name, that's my Nickname are everywhere, so anywhere you're gonna find me with Maribedrã. So I'm from Brazil. I'm come from this town right there Niterói near Rio de Janeiro they say the best thing about Niterói is the view from Rio and well it's probably true because

Yeah. I think I get all the technical problems from the conference. So if other speakers can be okay, they nothing gonna happen to you. So, well, then I'm come from Niterói, that little city around there in Rio. I recently moved to Recife, that's up there north. both very beautiful places if you ever come to Brazil come visit us lots of beaches and uh warm and music great places so then uh that's me and that's talk about why I came here So uh

you know when you go to Django documentation and you go through the or I am stuff and all of those pages you never get to. Well I used to look at them and say hmm never use those steps let's make uh a talk to talk about them and learn them on the way. That's my biggest motivation to be here. Have a reason to learn all those things I didn't know about. Then uh yeah, I just wanted to test all of those things I only saw on the documentation and never got to use. So well I

a while ago I worked at a in on a PHP pro project, a legacy PHP project, terrible. But uh Good thing about it is that I we as we had to just write raw SQL all the time, I ended up learning some interesting SQL stuff And uh at some point I got to a place where I could implement some things in CPL directly that I had had no idea how to do in Django. So I was I've been wondering for a long time. Wow, how do I

get those things to get things together. How do I implement the things I already know in SQL with the ORM? Because while we're writing Django software, we want to use Django So could I put the two of them together? Uh and when Would be the good situation to have that knowledge to use those things. Well uh you know in Django documentation has a whole section about uh improving your queries so about uh optimization strategies. So well

you've already added indexes to your tables. You have already paginated your requests. You have added SELECT-related and prefect related to your query sets Uh you have tried using values, values list only differ on your presets uh as you might know all of those are methods that reduce the number of columns they we get from the database. uh you have set up your database cache. Uh you can use assert num queries on your methods on your test to guarantee that you're not doing extra queries

that you don't have to. You have done all of that, but still sometimes that's not enough and we still have to improve our database queries. So how do we do that? There are some interesting tricks uh that we could use. So How did I choose to organize it? Choose a big data set because well I can't I could just try to implement code on fake data, but then I wouldn't get the real feel of knowing if what I was doing was actually working or it was just pretty cold

so I wanted to have real data. Then I would come up with some interesting questions and try to answer that questions using those methods I never got to use. Not all of that. came i as i expected to. So well what from the beginning I just Took too long to start it. That was my biggest problem. And while dealing with real data uh means that you don't have the perfect data. So if you the data wasn't created with Django

, great chance that it's not gonna be perfect for your Django modus. So I had to validate the data while importing it. And my machine, well as you can see, uh it's not a very great one. So uh I had to create an importing script. that at the same time didn't kill my machine, consuming all of its memory and I like it killed my machine hundreds of times. I was just got used to rebooting it all the time And at the same time didn't take forever to import all the data. I was already using Book Create And it's still like I was creating

hundred thousand entries each time and it was still not enough to imp implem to import all of all of of the sun. So And then well after I had the data that putting all of this Things together are not quite easy. So when you look at the documentation everything looks pretty simple, but when you try to put it together with real data then Well things get a little bit scarier. But in in the end I got some results I gun wanna show you. So what's the data I'm gonna talk about?

uh open data from Brazil. We have a law in Brazil that says that all public data must be available. There's a information access bill Uh but what's the problem with that? In real life things don't work that well Uh though the data is there, it's not that accessible. So not every government agency complies. Sometimes you people have to go to justice to get the data. Uh well each government agency has its own data, so you have to like run around asking each one of them.

They 're not always in open formats, so some of them provide the data in PDFs or such things. And the biggest problem is that while people that could benefit from from the data don't have the technical skills to analyze it to get it. Then a pro a friend of mine called Avalu. He has a great project, Brasilio. to gather all this data and make it available, clean it and so that people can go to a single source and get clean data so they can analyze it Then while as I was preparing my talk, I asked Calvary, well, do you have any interesting data for me so I could like

use on my talk. He provided me with some nice data data sets. I choose some of them and like why not prepare a talk and then in the end come up with a neat nice database that could be used somewhere else to like give something interesting to other people. Then well I chose two data sets. One of them is from all the partners from companies in Brazil Uh it contains basically the CNPG that's a unique in the identifier for companies. The partners

name, the category of the partners, if it's a legal entity, if it's another person or it's a foreign. The pet partnership category, there are many of them And the partners C NPJ or it means if the the partner is a company then I we have the number identifying the company that owns that other company. That's one that is set and it became those tables. So I have a partnership table that relates comp companies to their partners. So the company can have a foreign partner a natural personal partner

or another company as a partner. And they all belong to states. Then uh another data set is the Chamber of Deputies uh expenses. So we have a national congress, each deputy has the right to spend a certain amount of money on their activities. Uh and the dataset describes how they spent that money. So I have a unique identifier for the deputies , the party, uh the his name, the date. The reference for the month and year, the amount, description, and the company they spend the money on

and some other fields. Okay. And those become my tables. Oh, I can I have an expense. It's related to a deputy. The deputy is related to a party and a state and the expense can be related to a company that can by its turn be related to a state too. Good. And then let's go to the interesting part, the queries. First thing I want to talk about is perfection. I just discovered it like two months ago and it like changed my life. I've been using them at work uh

for retrieving related objects uh given a certain context. Uh I'm specifically using that on a project right now Where we have like a small system for us to control the finances of the company, uh so that Fernando can pay the other partners their salaries. So when we retrieve the data from a user, we want to all the related data from that user. So how much time he worked on that month, uh the expenses he had on that month, uh the days he didn't work, etc. So We have an API, I get the user information from that API and all the related data.

Well, but when I get all the related data from a user, I don't want the data from last year. That doesn't interest me. So I have an app that uh calculates the salary for a month So I only the only thing that interests me is the data in the context of that specific month So the best way I found out till now to get those related data in a context is with the perfetto objects And so in our case here with the data I presented to you, how would we do that? Imagine we have a serializer. Pretty simple with model serializer. I want to describe the depute

of his fields with the depth of two. That means I go one two levels of relations from that model. Okay? So then I create a list view to list all my deputies. Uh what would we do normally on a pre reset? I get a select related to get all the the party to which this deputy belongs to, the state where he was elected from with a select related so then this select related will create uh joins uh with on statement on the database and then the prefretch related

will create another query that will get all the expenses uh filtered by the deputies IDs. Okay. That's what you need to show related models But then I would get all the deputies and all the expenses from my database. And it would be a huge query. I don't want all the the data. So how would I do to get all only the data specific to a context? So I'm gonna refactor my view. Well first I'm gonna set a month and a year as a default parameter, so you

only allow to get data from a year uh from a month. Okay. Then I get uh first my uh deputies query query set. So I get all my deputies on and all the parties and and states related to it I set the default month and year. And then what I do is there's a last line missing there, but I think it's okay. So I'm gonna get all of the query parameters that the user passes to me and pass to that

uh to that metal called prefetch expenses. Uh I'm gonna like you should never do that in real life. Don't get the query parameters from your user and just put them in on your model. That's just for example you you would normally use Django filter or something like that. I just created that method here to show how it would work. So I get all those filters, pass them directly to to my query set. And that's when I prefetch the data. So uh yeah, it's a bit of unformatted here, but it's okay. Well

the prefetch object uh creates a prefetch for me, so instead of prefetching by the related name, I prefetch with a prefetch object object. and I pass a query for that prefecture object. So I will filter all the expenses by the pip parameters I got and after this What uh the expenses are filtered, I pet I pass them as a prefecture. That means when my user types in that he wants all the deputies

and he wants all the deputies with his their expenses on the month third on the third month of twenty eighteen. And they want the v the expenses that was that are bigger than uh one hundred. Instead of each deputy with all the expenses from four years ago, I only get the expenses that match that exact uh parameter. So It creates a single query. I only fetch the data I have. I never had to once uh make uh

a loop over the data, I just retrieve them directly from the database exactly the way I want them to be. So that's the first use useful thing I've discovered recently. Then Second thing that was very funny to do, uh we can filter aggregates. Uh didn't know that like before I was planning that talk. So uh you know Django aggregate functions, we can sum, we can average, we have max variants standard deviation, we have lots of aggregate functions.

But we can also aggregate on filtered things. So I'm gonna annotate all of my deputies and I want a field on my deputy object that says uh which was how much did he spend on a month and a year? So Have you used F F strings? I love F strings. I'm like using them everywhere. Love them. Just beautiful. So I'm gonna annotate my deputy with a field with the expenses of a particular month

How am I gonna do that? I'm gonna use the sum, that's the aggregate expression, everybody knows, and that I can then feel filter that sum. So instead of summing up all the values whoa ready? No. Uh instead of summing the values of all of the expenses I'm gonna just sum the ones that match my future. So I get the sum of a specific field. Uh and when you look at the query they make, they make a filter on that sum expression uh with the join on the expenses. Okay.

Well but there's also the filtered relations. They can do the same thing. Not that beautiful, but they do the almost the same thing. I can get a filtered relation. It's the same thing. I'm gonna get my deputies Uh I'm gonna filter the expenses, use the same condition. As you see, the condition is basically the same one. Filter them by month and year. I'm gonna then create the first annotation of that that says uh all the expenses that were made in that month and year. And then I'm gonna do a second annotate that's gonna sum up all of them.

Okay They both do the same things, but if you look at the query they make the conditions of month and year instead of appearing on a filter like Here they appear inside that where statement, and here they appear inside the on condition. What's the difference? Performance. That's the first query. Uh I don't really understand all of the things they do around there, but the main thing is the number up here. Uh it shows how efficient the query is. That's the first one, and that's the second one.

It's incredibly faster. Like they do basically the same thing. Instead of filtering here and here, but the fact that this the filter is on a on condition makes a huge difference. So The code is not that beautiful like when you look at it it's like bigger and has a strange thing in the middle but makes a big difference in real life. Uh and you can also aggregate uh annotations. That's also great. So

I did the same annotation as before, but with uh with uh with uh But for all the months from 2009 to 2018 , gonna do the same annotation. So I'm gonna have uh a spends for month annotated for each deputy, and then I can aggregate those annotations So each deputy would will have a field that says how much he spent on a specific month Uh and then I can do the average of that sun. So the

That field that says gastus year-month will be aggregated so each deputy will have an attribute for each month in the last Lots of months. Uh and I'm gonna calculate the average for each one of those We end up with a huge dictionary and we could also use those to build graphs, pi plot is Beautiful. Don't know what would would I use that that thing for, but if someone is interested in how They varied their their expenses like with two three Python methods I got this beautiful plot that

okay And the nicest thing, subqueries. I've always wondered how to do that in Django, and now we can do it for two versions. So I wanna know uh which deputies have companies. We cannot do know that for sure because I don't have any unique identifier for people. Uh the only way to identify them is by the name, so that's not very accurate. They well there there can be people with the same names But anyway, I'm gonna I wanted to like at least know which deputies have uh could

have companies in their names. So I make a query uh on the companies And I wanna know uh which companies have partnerships that belong to Natural Post people Uh and I want to know the name of the people that owns those companies. And I want to compare that name to something else. What's that something else? A reference to the query that's outside of it. That's how we use the outref So the documentation uh normally shows you how to use the outref

with uh primary keys But what's the fun in using primary keys? We already have the relationships. We can use reference to any keys. So I use the reference to a name. So I have companies the partnerships they have, the name of their partners, and I want to compare them with the names that appear in the query that's outside of it. And I want to compare the state they're in. So it's probably like as I don't have the uh any unique identifier, I know I want to know if they belong to the same state. So I do the same thing with the stage. I make a reference to the query that's outside of it

so they match. And we can annotate that and I discovered I think I forgot to put here I discovered that one-tenth of the our deputies have probably companies on in their names. And that's the query they make. So it's basically a select on the companies table with uh joins to the partnerships Then uh we can reference the columns from the inner query uh and the outer query. Uh Then I think yeah, I think that's it.

Hope you get something from it