Django - Subquery, OuterRef and Exists objects for Database SQL Subqueries

in this video we're going to learn how to perform sub queries with Django I'm going to see how to build up nested select statements using Django orm tools and notably we're going to look at two expressions in Django we're going to look at the sub query expression which allows us to add a sub query to an existing query set and we'll also see the outer ref expression and this is used when I query set and a sub query needs to refer to a field that exists in the outer query we'll see examples of that later on don't forget to like this video And subscribe to the channel if you've not already done so and if you want to support the channel we have a coffee Link in the description let's get started now I have the Django documentation open here and I'll link this below the video we have a page on query expressions and if we look at the sidebar here we've already seen these F objects in this series but if we go down we have a section on sub query Expressions so let's click that and we're taken to this page now the sub query expression is what we're going to look at in this video but I'm going to go to this page here which I'll also leave in the description this is a page that basically tells us what our sub query is now a sub query is a complete select command that's used within another select update insert or delete command and the only difference to the normal simple select statement is that it's enclosed in parenthesis and we can see an example of this if we scroll down on the page here when we're selecting some columns here from a table called person and we have the standard reference to columns like ID last name and also weight here but we have this additional sub query for another expression that we want to bring out as part of that selection and in this case the sub query is getting the average of the weight column from that table and then aliasing that result as average weight so this sub query here uses the average function and as it says below the SQL compiler knows that that will return a single value because it's an aggregation it's going to return a number and that's called a scalar value sub query we don't need to worry about that terminology but we are going to see how to perform these types of queries with Django so let's go back to the doc documentation and you can see we have here some imports of the outer ref and sub query object we're going to see how to use them just a bit later in the video but what we're going to do to start with is we're going to open the database browser for SQL Lite I'm going to bring that onto the screen now and these are the tables that we've been working with throughout this series I'm going to look at one table right now and that's the sale table so we can browse that table and we can see the data that we have in the table we have a column that is a foreign key column to the restaurant that contains the foreign key ID to that restaurant table that we also have in this database now what I'm going to do in order to demonstrate a sub queries I'm going to write two sub queries here and the first one is going to look at their sale table let's say that we wanted to get all of the sales for only Italian and Chinese restaurants if we go to the other table restaurant in this database you can see that there's a restaurant type I.T for Italian CH for Chinese in for Indian and so on now if we go back to the sale table here you can see that we have a restaurant ID column but let's see we only wanted to get the rose for those Italian and Chinese restaurants so I'm going to write an SQL query here and I hope you can see this query on the screen the text is a bit small so apologies for that we're going to select all of the columns from the sale table and we're going to define a sub query in the where Clause so we're going to look at the restaurant ID and we're only going to get restaurants whose ID is in the results of this sub query that we're about to Define so the sub query I'm going to move that to a new Lane and tab it over it's going to be another select statement and because we're defining the where clause on the restaurant ID I'm going to return the ID column from the core restaurant table and what we're doing is we're going to use another where Clause within the sub query and we're going to check that the restaurant type is one of these values so I'm going to use the N operator and it's Italian which is it and Chinese which is CH and once we've done that we can close off the sub query and that is the query we are about to execute so let's execute that query and we get back some results from the data database here but you can see we don't have as many rows as we had before this time we have 51 rows in the table and this subset is only the sales that represent sales made for Italian and Chinese restaurants so let's look at the query we're selecting all of the columns from the sale table you can see that here in the result set and then within our where Clause that's going to filter the rows in that table we're checking that the restaurant ID is in the set of IDs that's returned from the sub query and again the sub query as it said in the Wiki page that can be any kind of Select statement the only difference is it's enclosed in parenthesis and there's an outer query which you can see here now let's try and write this query with a Django you can see at the bottom that we have 51 rows keep that in mind because we're going to try and get those same 51 rows from the database using Django now I have vs code open here and this is a script that we've been working with in this series what we're going to do now is try and write some code that's going to get back the sales for Italian and Chinese restaurants and it's very easy to do this and dangle we don't actually need to use the construct sub query that we have imported at the top let's show the very simple way to run this query it's going to be sale.objects.filter and the sale model has a foreign key to restaurant and we can then follow the reference to that foreign key and look at the restaurant type column and we can check if that's in a set of values using the N lookup and Django now the values we're looking for here I'm just going to hard code the strings it's Italian and Chinese so that's the query that's going to get back those sales so let's store that in a variable called sales and then we're going to print the length of these sales or the number of sales that are returned from the database and we can execute the script at the bottom so let's run that script and you can see when that completes we get back 51 rows and that matches what we had in sqlite database browser here and that's the same set of rows it's only rows for Italian and Chinese restaurants now we can verify that we only have Italian and Chinese restaurants in this query set by taking this statement here and we're going to write from on the sales query set that we got back we're going to use the values list function to extract the restaurants restaurant type value and then we can call Dot distinct on that to get back the distinct values from the database on that column and we're going to print those to the terminal and let's run this script and see what we get back get back a query set containing Italian and Chinese but the other restaurant types are not part of that query set so we can see that this has worked the sales that we've extracted here with this line of code are only for Italian and Chinese restaurants so I'm going to remove this print statement and this is the query that we can write the very simple djangle query that follows the foreign key reference to the restaurant model but what we can also do is write this using the sub query object that we've imported at the top so let's see how to do this and remember this might not be the best way to do it but it's going to introduce these objects now I'm going to change this statement up here we're going to look at the restaurant type but we're going to use the restaurant model here in order to do that so let's store the results of that in restaurants what we're doing here is we're filtering the restaurants to only Italian and Chinese restaurants and the restaurants that are returned from this query set imagine they form the inner select statement the nested select statement and we're going to look at the IDS that are returned from this query set and the outer expression so let's now write the outer expression where we look at the sales and we're going to use the sale model in this case and it's going to be dot objects dot filter and we're going to look at the restaurant foreign key on the sale and we're going to look for restaurants that are in the sub query that we've defined above so we've got this restaurant sub query but we want to take the primary key values that's the foreign key value from that sub query so we can use the dot values function and we pass in the primary key column there this will give us back the primary key values for all restaurants that meet this condition that are Italian or Chinese and for all of these primary keys that are returned we use those values as a list and we use the end lookup to check that for each sale the restaurant foreign key is one of those primary keys so we're using the sub query construct to do that and to verify that we're getting the same results here let's print the length of the query set that we're getting back the sales and we can re-execute the script and we're going to see 51 here as before now that's one sub query what we're going to do also here is Define another one now you can see in this SQL statement the sub query is part of the weird expression but the sub query can actually be part of the selection itself and that's in this part of the query here we're going to see that now and the problem that we're going to look at here if we go back to the database structure and go to the restaurant table we have a set of restaurants let's imagine we wanted to add an extra column to this table and that column Returns the income generated from the restaurant's most recent sale now the sale model has that foreign key to the restaurant and we also have a date time column here so for each restaurant we want to get the set of sales for that restaurant we want to order them by the date time column in descending order and get back the most recent sale and annotate each restaurant with that most recent sale so what I'm going to do is go back to the SQL tab you and just to save some time in this video I'm going to paste this in here this is the query that we're going to execute we're getting from the restaurant table the ID of the restaurant the name of the restaurant and its type and as well as those three columns we have a sub query here that defines another value so let's look at the sub query we're selecting the income from the course sale table now the sale table is not the same table as we're doing the initial selection over so getting the income from that table and we're looking at the restaurant ID which is the foreign key in that table and we're checking if that's equal to the outer queries restaurant ID so when the query first runs and we get a restaurant with id1 the inner query here is only going to return the sales where the sale restaurant ID is one so in other words with the foreign key is equal to one and when we get back those sales for that restaurant we're ordering them by the date time field in descending order and to get the most recent one we're limiting that to a single result and aliasing that column or that output value as last sale so let's execute this query and we can see the results that we're getting back we have the three columns from the restaurant table but we now have this extra column that's been generated by the sub query here and that gives us back the value of the income of the last sale that was made for each restaurant in this database and this is exactly what we do when we annotate objects in Django when we add an extra piece of information an extra field to The Returned data set in this case that extra information is the income generated by the last sale so we're now going to go back to vs code and we're going to see how we can write this query with Django so I'm going to remove the code that we wrote before and let's start by fetching all restaurants from the database with the restaurant.objects.all query and for each one of those restaurants what we want to do is annotate each one of them with the income generated from its most recent sale so we're gonna have to use these sub queries in order to do that we've imported sub query and also outer ref at the top we're now going to use both of these objects so let's define the query below we're going to start with the inner query or the sub query itself now the sub query for each restaurant is going to fetch the sales associated with that restaurant and when we have those sales we can look at the most recent one if we order it in a particular way in order to get the most recent sale so let's Define a variable for sales and we're going to use sale.objects.filter here and we're going to filter over the restaurant foreign key here and we're going to use this outer ref construct which refers to the value in the outer part of the query now the outer part is going to be each restaurant that we're iterating over and we want to filter the sales by that restaurant's ID basically its primary key so we're going to refer to the primary key field in this outer riff object the outer riff object works very similarly to the F object in Django we provide it with a field but that field refers to a column in the outer part of the query we'll come back to that idea in a second but once we've filtered out the sales down to only those for the restaurant and the outer part of the query we're going to order those results by that date time column in descending order and to get it in descending order we can use the minus in front of the field name Django is smart enough to know that that means get the most recent first so what we've written there that refers to the sub query or the nested select statement that we're going to look at and then we need to define the outer query here so let's write a comment this is the outer query and we're going to take the restaurants that we fetched above and we're going to annotate each restaurant with its most recent sale by using this sub query so let's refer to the restaurants that we've already fetched and we're going to use restaurant store annotate here and let's call the annotated field last sale income and we're going to set that equal to a sub query now the sales that we've defined on line 11 that's a Django query set we can pass that in to the sub query expression here and what we want to do is because we're getting the income from the sales that are associated with this restaurant what we want to do is use the dot values function again because we're only interested in a single column from that sub query we're going to pass the income column in there and in order to get the most recent sale we can use a python slice here and we can get back only the first value in that particular query set so this slice here is going to remove all but the very first object in that query set and then we are going to look at the income field there and this is basically going to return the income generated from the restaurant's most recent sale and it's going to do that for every restaurant in the query set now to verify we're getting something back here I'm going to iterate over each restaurant that we have in this query set and we're going to print out that restaurants annotated the value so it's going to be restaurant dot last sale income now once we've done that I'm going to rerun this script I'm going to make the terminal bigger here so we can see the output of this script for each restaurant we get back the value from the last sale and if we go back to the print statement and I'm going to turn this into an if expression here we're going to print out the restaurants primary key here so we can refer to in the database as well and then we can close the the F statement off here and rerun the script at the bottom so let's have a look at the values back here we have for the primary key of 4 a value of 67.63 let's go back to the database browser and for the restaurant with idea4 you can see that that matches the value we're getting back from this raw SQL expression so let's go back to the code in Django and just quickly go over this one last time we're fetching all restaurants from the database and then we Define the sub query that we're going to use here in order to get back all sales that are associated with each restaurant and we're annotating each restaurant with the value from that sub query by looking at one particular column and that's the income column and we're slicing in here at the very first value and that is going to give us the most recent sale because we've ordered the sub query by the date time field so this is the inner query or the sub query that refers to the outer value of the restaurant's primary key to filter these sales down and then that sub query is used within the outer really and in this case it's where we are annotating each restaurant with that last sale income now we can also add additional Expressions to this annotate object and these additional Expressions can also use the sub query construct so I'm going to copy this line of code down twice here and we're going to see something else here we have as well as the income and expenditure column in the table so let's refer to the expenditure column in that sub query and what we're going to do is change the name of the annotated field here to expenditure and at the bottom I'm going to Define another annotation called profit and we don't need the sub query for this one I'm going to remove this code here and instead of that we're going to use f objects in Django to refer to the previously annotated values so in order to get the profit we need to take the income from the last sale and we can subtract the expenditure from that last sale as well so again we'll use an F object and we're going to refer to last sale expenditure and don't forget the commas in this expression here because we're passing multiple annotations we need to to separate them with a comma we also need to import the F object from django.db.models at the top so once we have the F object imported we can look at this statement again we're taking all of the restaurants and we're annotating them this time with multiple values one for the last sale income here that we saw before but now we also have the expenditure from the last sale as well and again in order to get that we use the sub query and we're referring to that query set sales that we generated above but this time the sub query is going to extract the values from the expenditure column and it's going to get the most recent one again by slicing in and getting that first element and then we have two annotated values we can use the F objects below for a third annotation for the profit and we can do this subtraction here between the last sale income and the last sale expenditure so that's covered some Concepts here on sub queries and outer riffs let's go back to the Django documentation on sub query expressions and actually I'm going to scroll to the top here and we're going to go to the sidebar again and there's a section on exists sub query so let's click that now there's a class in Django called exists and that is a sub query subclass and this will translate to the SQL exists statement and in many cases as it says here this will perform better than a sub query since the database is able to stop evaluation of the sub query when a first matching row is found and that's because exists returns a Boolean value so if you have a single match you know it's true otherwise you might have to perform a full table scan in order to find out that it's false sub query can help you though because if it does find a match it's going to instantly return the result so let's use this within our code here I'm going to clear out all of the code that we have here and I'm going to change the statement for the restaurants and we're going to use the filter function and let's add a comment above here to explain what we're going to do we want to filter the restaurants down to only restaurants that have any sales with an income of greater than 85. so let's say this is some magical number that we want to get restaurants that have sales greater than that value for whatever reason let's see how to do that now using the restaurant.objects.filter function and at the top I'm going to import the exists object from Django and we're going to use that here within this query and remember that's a subclass of the sub query expression that we've been seeing in this tutorial we're going to Define that now using exists and instantiate that and we can pass another query set in there as the sub query so the query set we're going to pass in is going to check if there are any sales that are greater than 85 for that restaurant so in this filter statement we're going to refer to the foreign key on the sale model to the restaurant model and in order to filter down to the restaurants that are in the outer query again we need to use this outer riff construct and the key that we're going to use here is the primary key so the field is going to be the primary key here we're checking if the restaurant ID is equal to that restaurant's ID that we're iterating over in the outer query and we also want to add another filter here we're not just checking to see if the sales foreign key matches the outer restaurant ID we're also checking if the income and the sale table is greater than 85 and that was part of this Clause here we're checking to see any restaurants that have sales with an income of greater than 85 and that is all we need to do in that query so let's evaluate that query and we're going to get the number of rows that are returned using the dot count function and let's clear the terminal at the bottom and rerun the script you can see we get back 10 rows from that restaurant table so there are 10 restaurants in the database where there exists a sale for that restaurant with an income of greater than 85. so the sub query here is checking for any sales with an income of greater than 85 and also filtering those sales to whatever restaurant we are at as we iterate over all restaurants in the database we're checking for the existence of any sales that match those conditions for each restaurant and the restaurants are defined in the outer query here which is restaurant.objects.org filter and if we increase this value here to let's say 115 and save this let's see what we get back here we get back zero and I think that's because the maximum income in the script that was generating this data was a hundred so if we change it to let's say 93 here and rerun this script we should see hopefully a couple of values but less than 10. and if we scroll down we have a connection.query statement here we can actually uncomment that and we're going to check what kind of SQL is being generated by this query so we're getting the count of all rows that are returned from this query we need to look at the where Clause here where we use the SQL exists statement and we have the sub query here where we're selecting one as a from the sale table where that sale table's income is greater than 93 and also where the sales restaurant ID is equal to the restaurant tables ID now this is the key line of code that's defined by the outer ref expression so if we look at the outer ref here we have the primary key being referenced from the outer table which is the red restaurant table that's exactly what's going on in this query here the restaurant ID here is the one in the sale table and that's the foreign key to that restaurant table and the second part of this equality is checking for the outer table which is the core restaurant table and you can see that referenced here in the normal from statement in SQL that restaurant table has an ID and that's how the outer riff is constructed by looking at the inner table and comparing the values in that column for the inner table with the value on the outer riff or the outer query and I think it's worth analyzing some of these SQL statements and trying to write your own both with SQL and Django in order to fully understand these objects and also the concept of a sub query and particularly how to write them with the Django it can take a little while to sink in how to do this but it does make sense in the end now we can quickly show another example here I'm going to comment out connection dot queries and we're going to change this query here that's using the exist statement and this time instead of sales let's say we wanted to pay find all restaurants that had a five-star rating So within the exists sub query we're going to change the model to rating and we're going to check for all ratings where the restaurant has a rating of 5 Stars so we can remove the income part of the sub query we need to keep the outer ref because we need to get the ratings foreign key to the restaurant and make sure that that matches once we have that we also need to check for the actual rating itself we need to check if it's equal to 5 and that exist clause and we can execute that and again we can see the output at the bottom here and if we make this a little bit bigger you can see that we have four results in that query set so there are four restaurants who have at least one rating with 5 stars in the database now an interesting thing that we can do with the exists operator is we can actually invert that because it's a Boolean operator rather than exists we can use the tilde before that and that means that we are going to check if this does not exist so in other words we're going to get all restaurants from the database where that does not exist at a rating of 5 stars for that particular restaurant so let's again save this file and rerun the script below and we get back 10 this time now there are 14 restaurants in the database the first query returned full results this one is the inversion the opposite of that query and it's returning 10 results so we can negate this exists operator and that will change the underlying SQL to not exist and again the expression that we're passing into this exists object that's the sub query that we're defining and sending to the database now let's do one final example in this video we want to get all restaurants that had sales in the last five days so I'm going to remove this code here and at the top you can see we've imported the time zone object from django.utels what I'm going to do is Define a variable here that represents five days ago by taking the current time with time zone dot now and subtracting a Time Delta of five days what we're now going to do is Define our inner query here and get back all restaurants that had sales in the last five days so let's define that sub query now it's going to be seal.objects.filter and again we're going to be looking at that restaurant foreign key here and we're using the outer riff over the primary key column here to look for the sales that match the restaurant and the outer part of this expression and again we need to Define another part of this filter we're going to look at the date time column and we're only going to get back sales whose date time is greater than equal to five days ago so this is the inner query that's going to give us back all sales that were made in the past five days for each restaurant as we do a table scan over each restaurant in the database we can now Define the outer part of the query by setting a variable here called restaurants that's going to be restaurant.objects.filter and this time we're going to pass the exists object and to the exist subject we can pass the query set that we defined above here that contains for that restaurant all of the sales where the foreign key matches that restaurant and also sales that were made in the past five days that's the query set is provided and then exists is going to return a billion so for each restaurant if that billion evaluates the true it will be kept in the resulting query set otherwise it will be filtered out so now that we've written that let's print out the number of restaurants that we're getting back and execute this script this time we're getting back five restaurants that have sales that were made in the past five days so that's the last example in this video we've seen how to perform sub queries using SQL and also with the Django orm where we have a sub query an outer riff and an exists object that we can use to define and perform these sub queries and these can be very powerful if you want to annotate or filter your objects based on more complex conditions sub queries are very common in SQL it's very useful to know about as a back-end programmer so thank you for watching this video in the next video we're going to look at database transactions with Django if you've enjoyed this video please like And subscribe to the channel and we'll see you in the next video

Channel: BugBytes

Views: 4,371

Keywords:

Id: QvaScsn6S6E

Length: 26min 0sec (1560 seconds)

Published: Thu Sep 28 2023