I Didn't Know Querysets Could do That

Video in TIB AV-Portal: I Didn't Know Querysets Could do That

Formal Metadata

I Didn't Know Querysets Could do That
Title of Series
Part Number
Number of Parts
CC Attribution - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this license.
Release Date

Content Metadata

Subject Area
QuerySets and object Managers are a core part of Django, and can be extremely powerful. But I didn't always know about some of their more advanced capabilities. BASIC METHODS You have likely used filter(), exclude(), and order by(). You've even probably used an aggregation method like Sum() or Count(). Less common, however, are query(), only()/defer(), and select related(). F EXPRESSIONS / Q OBJECTS For some more complex queries, those basic functions and filters won't cut it. How do you construct a query that needs to check for field A or field B? What do you do if you need to multiply two fields together and then sum them? Look no further than F() and Q(). RAW SQL / THE EXTRA() METHOD As a last resort, it's entirely possible to use raw SQL queries to get the database results that you need. The sky's the limit, but there are definitely downsides to this approach; pitfalls include SQL injections and database backend portability issues. MANAGERS A talk on QuerySets would be incomplete without mentioning Managers, and how to leverage Manager customization to make your life easier. Writing methods on existing Managers, and creating custom ones can go a long way towards being DRY and reducing the potential for errors.
Intelligent Network Computer animation Query language Multiplication sign Moment (mathematics) Shared memory Expert system Number
Complex (psychology) Code Multiplication sign Source code 1 (number) Set (mathematics) Water vapor Real-time operating system Insertion loss Parameter (computer programming) Function (mathematics) Mereology Usability Neuroinformatik Web 2.0 Array data structure Sign (mathematics) Endliche Modelltheorie Extension (kinesiology) Social class Physical system Email Mapping Relational database Wrapper (data mining) Electronic mailing list Fitness function Bit Database transaction Instance (computer science) Functional (mathematics) Connected space Category of being Type theory Field (agriculture) Order (biology) Self-reference Right angle PRINCE2 Black body Resultant Reverse engineering Spacetime Point (geometry) Web page Purchasing Trail Dataflow Functional (mathematics) Game controller Sequel Letterpress printing Distance Theory Product (business) Revision control Object-oriented programming Average Term (mathematics) Operator (mathematics) Electric field Integer Mathematical optimization Form (programming) Condition number Information Key (cryptography) Forcing (mathematics) Expression Counting Database Total S.A. Line (geometry) System call Particle system Word Computer animation Query language Personal digital assistant Customer relationship management Network topology Iteration Table (information)
Computer animation Query language Personal digital assistant Equaliser (mathematics) Customer relationship management Right angle Endliche Modelltheorie Table (information) Functional (mathematics) Portable communications device Physical system
Functional (mathematics) Sequel Multiplication sign Execution unit Parameter (computer programming) Number Attribute grammar Programmer (hardware) Goodness of fit Latent heat Causality Object-oriented programming Different (Kate Ryan album) Single-precision floating-point format String (computer science) Selectivity (electronic) Traffic reporting Email Scaling (geometry) Relational database Projective plane Expression Counting Database Functional (mathematics) Computer animation Personal digital assistant Query language Network topology Order (biology) Formal grammar Right angle Table (information)
Code Multiplication sign 1 (number) Mereology Number Neuroinformatik Frequency Mathematics Different (Kate Ryan album) Program slicing Selectivity (electronic) Endliche Modelltheorie Noise (electronics) Artificial neural network Sound effect Bit Instance (computer science) Entire function Category of being Query language Predicate (grammar) Logic Statement (computer science) Right angle Pattern language Musical ensemble Quicksort
probab off at at the end to end it so if a if we were to to to everybody
expert I don't know queries that could do that is something that i've thought or said myself a number of times over the last years while learning the ins and outs of Django in was holding a share some those moments with you as that that the 1st you know why is this
important why should you care about the words coming out of my mouth and if you're a devout reader of the documentation and to be honest this talk might not be that interesting but if you're more like me in that you read just enough of the documentation and or StackOverflow in order to solve the problem you currently facing and you put it back down and then it might be worth worthless like a lot of the stuff that all these solutions are counted I will encounter them because the relevant was working on and simply knowing that I could have done something in native jangle would have gone a long way toward figuring out how to do it in the and even if you're fairly advanced a lot of these concepts within fleshed out in the last 2 I know for a fact that at least a couple of examples could not have been done before 1 . 8 because in order to do that I was hacking around private health and I what offer practical examples in the hope that you can use them toward your own and achieve if not best practices at least federal practice and and instead and at this is a sanity check for everybody query sets are the things that are returned when you do a model the thing right but every time you say article argues about at all or particle dark object that filter if you're working with creates later on in the talk on here and do some examples of known theories that behavior and those revolve around and pretty basic common that I really like me money uh just to be on the same page a minute take look at some of the rough model references that we're using we've got products with their names prices and sellers and waters right and each order has many items that item has to point he's quantity them and afloat we fulfillment now if you done a Django out that's even slightly more complex than the list you probably run into some of these that like the ones on the left Return queries meaning that you can change you can filter and then exclude and then the right the ones on the right return something that the model instance at the boolean value that the dictionary full of stuff and I will spend too much time here but there are a couple of things that what we know about annotated and added right they are somewhat similar they both except in expression here were using the count and some classes to define the expression what you and but annotate will compute that expression for each item in the set right and it returns each item with the new property uh storing the result whereas abrogate will compute the expression across child like that and in turn the dictionary with the final value and you would annotate you've been adding uh the new value and use later on in the filter expression if you provide a keyword argument then that becomes the name of the property or dictionary he but if you don't provide 1 the general will automatically generate 1 by using the name of the field that double underscores in the name and kind of a subtle point to note is that actually pop example and we our counting all the items despite the fact that the product class if you call didn't have an explicit form he 1 I want right which means that here dingoes implicitly traversing the reverse relationship counting up all the items and Manhattan that as a field of final in part and in the end of course and if you are using these functions to count up to some stuff average step and you absolutely should it varies a little bit based on the underlying database management system but they tend to be significantly faster than writing afforded by hand or using even higher than the in of and in full to beyond those more common ones there are some lesser-known recently right and these generally revolve around optimizations or interacting with the underlying database table you may or may not know that when you access a foreign key an object and Django goes back to the data base that match the query in order to populate the point he's information by the web Prince print the seller and the iPod and it's not 1 but 2 queries in order to fit text that and things the blood and I should have the same thing happened you know it makes many and many to 1 over right and the on time wikinews select related to compress that down right so related takes in a list of arguments so that it will go ahead and joining the underlying sequel come along you to save time later when access and the big limitation of select is that it is limited point he because it means that the join in the lines people but it is very similar to prevent related right and which allows you to do the same thing with a reverse lookup and many things right prevention rather than doing it and all the sequel it would you doing by bond like you get to do it up front once rather than make timed ad-hoc like might you when entering what if you have the opposite problem by way if you don't want more information from the database that last this could be for example because you have a model with hundreds of fields on it and you only need access to 1 and then you can use only which as the name implies will only the explicitly list and you know it when you need you can optimize because by having nodes and obviously no if you access other fields outside of the ones that you ask you kind of lose the performance benefit because you have to select the database and differ with the other centers if and the trees all the fields are model except for the 1 you might wanna do this because you have a real time that's particularly expensive to convert to me I thought maybe it geospatial data and maybe something more Pasternak into different point on that having to do that path and blast 1 these methods on on is involved because I recently discovered by having its purple uh you pass it a list of ideas and it returns a dictionary mapping each ID to the associated model to me this is interesting because it's a type of weird irony is a lot like suppose you wanna get all of the products ordered in a given and you stop by filtering for all the items ordered in even 1 you would use values list right but it is true and things in order to distill that down to a list of chronically them any pass those involved in order to do that we generate a mapping and yet we done is he's using filtered by a filter in this also work and as far as I know these are kind of these places useful but if anybody knows a better way to do it we use from talking at the thought that for that reason and of writing this style theory a lot and I would love to win these now the
interesting thing about the filter and with that that star here is that an when the underlying signal is generated it's always generated using an right so if we look at this example the resulting signal is that we select all the water where the status of the ship and it was awarded if that begs the question what happens if I want to get all the orders where the status for it was over that there's a question on a not searching waters but searching in it the suppose you are your customers supporting right there is a we really want a flexible search you or we can just type in a keyword term and it will return all of their users know across 1st name last name and e-mail and we can use them for friends the brute force solution is to right 3 queries in person and you know convert them into lists and and you getting make them together and and this solution while it does work is not the most efficient you're hitting the database not once but twice once for each field on and then you are spending time not just converting each those queries that list but then bring list altogether and lasting you your final object type is a list of the which means that you can build further you can't have water it can move to that usually In this source recovered quick right and requests will start coming out without you too many results that can turn on and ideas that connection constraints that just adds to the time and the 1st thing we have you uh and you by these great objects and they impact the query right and you can combine them with various operators like and or not right so we want to rewrite the previous query we would do it by mining creating 3 q objects and 1 for each of our field on the part of and then joining them would be wise for operators and you can see with the light and so you we get reset begin work by last name in you ask for this thing I'm in best ball it already is down to a single theoretically you can create as complex of 2 expressions as you want and as long as they can be combined with it wise words and not the the this to is ASPO sales comes to you and they say that we really want you to build us some national right in fact a metric in the eyes and the decides to write a function call I'm told that takes in a set of line items and returned right person might think of going on this usability and let's just use a some aggregated across countries that and and and get the value out of the little things which 1 but let's recall for a 2nd how we define about the above approach for the work if we had a total price field rather than a unified and but because we structured this way we're not taking the quantity in the town it might be tempting to just say spread you know all right it'll price method and and then when using this convention in some way to get that this works but if we're taking our own advice about using annotate and aggregate where possible we do better as raft comes in while Q represented a query constraints f represents an implicit reference time of the Davis calls if that know that means that's fine it into forever to understand what that means but let's look at it hi this example I we're rewriting that product and some has in fact right rather than saying for example we did it with a pollutants they were item I am some plus equals item that you price was signed up for time and on the we rewrite that using a some classes and using at in order to implicitly create this expression and without having access to any individual item instances compute the added some of the the and that's the 1 more example this time distance through a tree that that self-referential but now your manager says you know Charlie so they're all these great but I want you to add a fields product so that we can keep track of how many times each 1 right and and 1st like that's Tom why would you do that and but is the loss those of in and and the full of you're right is extremely simple it's nice we can't really do much better than this term but while we can save much time we can save space now and using after in this case and actually avoid arrays like what's happening is we're using the of the method to update all of the values at once and then using after implicitly refer to the purchases yield on each of the 5 In many by 1 of the things that this converts down to a single atomic theory and that right and so in the above example it is possible you had to cross ceasefire and ones that 1 can clobber removal the and the use of data and after we avoid that way now as it turns out that objects are just a single example of a much more general general class of query as you might know some of these from the aggregate function by and but they actually have a very close cousin in our favor so the ones on the right uh you know while performing query you include award in line concatenative fields together compared to return the smaller convert to upper lower that the and some of you might be thinking hate those look like a lot of people that's because the article that you getting subclass the database function class and which simply takes a list of arguments and then the corresponding single function by the top example and without having iterate over any products with converting all the names to case and adding that right to each individual product and in the bottom example we're taking all of our users using this with park functions equal to break the e-mail based on the at sign delimiter and and just grab the 2nd of that like filming the the value of classification and the wrapper around Robert by healthy something that makes sense and so some more young query expression subclasses as and that's function values just saw an expression wrappers will touch on in a minute and conditionals actually are a really powerful about allowing to implement conditional logic inside of and you can say and add this computation and this extension if a certain cases to otherwise a 2nd completely different we and these are these are pretty powerful but if you find that you need to write your own theory version it's actually not that that you only have to define or as people can look up it transformed you that if you need a right if you customize the sequel output propose Greece or my sequel like you can define and has been the name function by where you replace the name with the name of it is I that since writing your own expression is a fairly advanced exercise and not all of the things that was exactly entail have rather I going to point you to the excellent Django documentation and leave it as an exercise but it is this going back I do have to admit 1 of our previous example wasn't exactly where the and by the and technically speaking this code will actually work if you have price and quantity of thing I right there will be an interesting of OK integer function major we probably want in that house but because you're price is the flow we have to explicitly tell Django what feels like the 1 on the as you can see where invoking the expression of a class to do this by the final and the thing little bit more verbose but gets more fine-grained control we're specifying how the field the flow field the and that we can cost of now hopefully ideally
you're convinced that you can execute 90 something per cent of the queries that you need in general native but on the chance that these haven't solved your problem you can always leads all the way up and start writing some bare bones the as a side note I just wanna say 1 would think yeah if you're writing last equal
we should probably re-think watches out now this isn't a say that you should never write gossypol right there are plenty of that use cases the right thing to do and they're absolutely performance in the benefits we have however there are probably different more maintainable ways of reading the same benefits if you're looking for speed increases consider refracting encoding morphogen right using some of the subjects we by the north introducing caching when it comes convenience you could write loss equal or you could consider restructuring of model the tables and to take advantage of more than a functionality uh writing on the wall you know while great in some situations means that you lose portability up when it comes to to changing the management system and of course uh if using dynamic values you start opening yourself up the possibility of you and now might be a single and the 1st way that you can start
writing sequel is the act thank that lesson Jack specific clauses into that's generates equal tree you should be aware that this method and 1 not only deprecated his planned for deprecation and so you really need to use it to make sure you file take it with the Django project so that the orders are where the use cases and can ideally tribal some new functionality to take and so in this example right we're introducing in his recent attribution to ah select clause and the generated sequel think that the report there bunch different causes the work that you've got select where tables were by and if you need to start escaping dynamic grammars and for the select clause and use of plants and for all the others you can use regular grammar and I have no idea why they're 2 different were arguments these and but there are a number of single orders going around so I would suggest that uh and if this really and truly has not all the problems and you can use rights must find that rolaids just take a string and idea out with the need and and you that the the in this example is very real and should not all the time using was equal for a couple reasons 1 I was too lazy to come up with a more complex 1 and 2 I'm employed as a Python programmer and idea and so as we get right we had our basic methods we and annotate and they were possible or advanced methods that we can reduce joins with selected pre-populated in use only and over the course of their are you objects which were encapsulated query constraints Prof objects which were implicit in your references are database functions some average count my no capital over you might not query expressions which a crazy powerful then ensued after the lecture and was equal thank goodness and rock the by its Charlie and you can find me around the unit usually Charley Aug well please note the or there is another Charlie who is not use the R and he gets a lot of my e-mails and I write stuff but this one-time her but it's called and scalable it's the collection of interviews with stop founders of the the things that don't scale that of question blood and thank you
few behind him yes so we have but by but it's request and so you have been to use make into the into the aisles of aided good question about a
query that we had some problem something we we agree that you do so changes that we're going through we we have to introduce more statement into an already complex query and so we have a nausea which then we fed into a filter with a bunch of other ends and by the time we're done with this query was so long running because the datasets are so large and that we had to break it up into multiple smaller queries to be able to have a run effectively and not time out of um would you have any suggestions for how we might deal and handle that the of the view that in a single queries yeah but again there emphasize that I'm not going mediate but I would say that and it is possible to do something where you essentially yeah like music music in that filter along and there would really were doing and so we we took good cues and forward them into its 1 logic you essentially and then use that in a filter with other I period as you know and in all of that together and it was this mass along and said on of like we did something wrong there if there is a way we could reorganize that so it would have created artificial query of government amateur and carbon where provisions prize Gray said you can print up here and it will stay out like so that generating the up there what we factor the the yeah thank you that was great um maker semi sigh and it up in a situation where I needed to query the same model imagined that different histories right but they wanted to have 2 slices it's 8 first one 10 items instances on and the other 1 5 he said to be impossible and of that you need 1 more than the imagined article an idea II II meet 10 items from 1 tutori many-to-many right 5 patterns from another category in 1 precept also if you have to create a new 1 joining together the were you know so that you know it for example would be to that you can can generate 10 and then you can generate 5 I and if you have the code that you could put them both in acute right in and I think I was actually Resnick you for deterrence but Sergiu she was like eat the 1 st you take care of slice you can't actually have from other slots the worst problem I noise that the selection slice the and and yeah I would figure would you like and if you know which I'm using it right like that when they were by the and then I think the 1st and and this would be a good place where the fact that they come from the come and right the know if this is greater than the 9 other ones that have stop but that I was just you identical that have been determined by it just that it seems to be a bit weird the having 2 different Kwisatz for same model you know just a gets items different from different stories that things so I mean I know that having to quiescent so the entire has thank you the I just wanted to clarify and your last example there on so when you draw query you can add any number of computed columns on and it'll just return them back as if they were part of a model so if you I believe you yet interest the by and use of new properties under model better sort temporary properties just from the work the beliefs so that the effect that the put we think it regrettable + predicates but if it is to my
car for the purpose