QuerySet.filter() demystified by Anssi Kääriäinen

This is a slidedeck of a talk that was presented at Django: Under The Hood

Transcript

1. QuerySet.filter() demystified Anssi Kääriäinen
2. The ORM Query builder Query to object mapper Object persistence
3. ORM operations higher level than SQL by design no .join() or .group_by() operations NoSQL?
4. Operation B o o k . o b j e c t s . f i l t e r ( a u t h o r _ _ b i r t h _ d a t e _ _ y e a r _ _ l t e = 1 9 8 1 ) Produces S E L E C T . . . F R O M b o o k I N N E R J O I N a u t h o r O N b o o k . a u t h o r _ i d = a u t h o r . i d W H E R E E X T R A C T ( y e a r F R O M a u t h o r . b i r t h _ d a t e ) < = 1 9 8 1
5. How does it work?
6. Example models c l a s s A u t h o r ( m o d e l s . M o d e l ) : n a m e = m o d e l s . C h a r F i e l d ( m a x _ l e n g t h = 2 5 5 ) b i r t h _ d a t e = m o d e l s . D a t e F i e l d ( ) c l a s s B o o k ( m o d e l s . M o d e l ) : t i t l e = m o d e l s . C h a r F i e l d ( m a x _ l e n g t h = 2 5 5 ) a u t h o r = m o d e l s . F o r e i g n K e y ( A u t h o r )
7. Example query Book.objects.filter(author__birth_date__year__lte=1981)
8. Classes involved models.Manager models.QuerySet models.sql.Query models.sql.SQLCompiler
9. to QuerySet.filter() author='anssi' Q(author='anssi')
10. Query.add_q() handles nested Q() objects participate in join promotion HAVING/WHERE split
11. Example call chain A u t h o r . o b j e c t s . f i l t e r ( n a m e = ' A n s s i ' ) - M a n a g e r . f i l t e r ( n a m e = ' A n s s i ' ) - Q u e r y S e t . f i l t e r ( n a m e = ' A n s s i ' ) - Q u e r y S e t . _ f i l t e r _ o r _ e x c l u d e ( n a m e = ' A n s s i ' ) - Q u e r y . a d d _ q ( Q ( n a m e = ' A n s s i ' ) ) - Q u e r y . _ a d d _ q ( Q ( n a m e = ' A n s s i ' ) , . . . ) - Q u e r y . b u i l d _ f i l t e r ( n a m e = ' A n s s i ' , . . . )
12. Query.build_filter() value preparation (for example for F-objects) fetch source field (including join generation) fetch transforms and lookup from source field
13. Actual implementation of build_filter() travel the relation and field parts to get the final source field value preparation (F-expressions, corner cases) check if the lookup string is a reference to an aggregate call setup_joins(), and consider the need for subquery join trimming and join reuse handling call build_lookup() finally, a bit of isnull special case handling
14. Essential methods setup_joins() relation and field references to joins and source field build_lookup() rest of lookups to transforms and final lookup
15. Join generation in build_filter b u i l d _ f i l t e r ( ) : s e t u p _ j o i n s ( [ ' a u t h o r ' , ' b i r t h _ d a t e ' ] ) : n a m e s _ t o _ p a t h ( n a m e s = [ ' a u t h o r ' , ' b i r t h _ d a t e ' ] ) : m o d e l . _ m e t a . g e t _ f i e l d ( ) f i e l d . g e t _ p a t h _ i n f o ( )
16. PathInfo structure from and to model Options (AKA model._meta) join_field, target fields is the join multivalued, is it direct?
17. Field names to PathInfo structs # n a m e s = [ ' a u t h o r ' , ' p u b _ d a t e ' ] , o p t s = B o o k . _ m e t a d e f n a m e s _ t o _ p a t h ( n a m e s , o p t s , . . . ) : # l o t s o f d e t a i l s o m i t t e d p a t h = [ ] f o r n a m e i n n a m e s : f i e l d = o p t s . g e t _ f i e l d ( n a m e ) i f i s _ r e l a t e d ( f i e l d ) : p a t h i n f o s = f i e l d . g e t _ p a t h _ i n f o ( ) p a t h . e x t e n d ( p a t h i n f o s ) f i n a l _ f i e l d = p a t h i n f o s [ - 1 ] . j o i n _ f i e l d o p t s = p a t h i n f o s [ - 1 ] . t o _ o p t s e l s e : f i n a l _ f i e l d = f i e l d r e t u r n p a t h , f i n a l _ f i e l d
18. PathInfo structures to joins # n a m e s = [ ' a u t h o r ' , ' b i r t h _ d a t e ' ] , o p t s = B o o k . _ m e t a , a l i a s = " b o o k " d e f s e t u p _ j o i n s ( n a m e s , o p t s , a l i a s , . . . ) : # d e t a i l s a g a i n o m i t t e d p a t h , f i n a l _ f i e l d , t a r g e t s = s e l f . n a m e s _ t o _ p a t h ( n a m e s , o p t s ) j o i n s = [ ] f o r j o i n i n p a t h : c o n n e c t i o n = a l i a s , o p t s . d b _ t a b l e , j o i n . j o i n _ f i e l d . g e t _ j o i n i n g _ c o l u m n s ( ) a l i a s = s e l f . j o i n ( c o n n e c t i o n , j o i n . j o i n _ f i e l d ) j o i n s . a p p e n d ( a l i a s ) r e t u r n f i n a l _ f i e l d , o p t s , j o i n s , p a t h
19. ManyToManyField?
20. Transforms and final lookup # l o o k u p s = [ ' y e a r ' , ' l t e ' ] , l h s = C o l ( ' a u t h o r ' . ' b i r t h _ d a t e ' ) , r h s = 1 9 8 1 d e f b u i l d _ l o o k u p ( l o o k u p s , l h s , r h s ) : # Y o u g u e s s e d i t , t h i s d o e s n ' t c o n t a i n a l l t h e d e t a i l s . w h i l e l o o k u p s : n a m e = l o o k u p s . p o p ( ) n e x t = l h s . g e t _ t r a n s f o r m ( n a m e ) i f n o t n e x t : l o o k u p = l h s . g e t _ l o o k u p ( n a m e ) r e t u r n l o o k u p ( l h s , r h s ) l h s = n e x t ( l h s )
21. Special cases join trimming join promotion join reuse subqueries
22. filter(friends__age__gte=10).filter(friends__age__lte=20) filter(friends__age__gte=10, friends__age__lte=20) Join reuse vs
23. qs.exclude(friends__age=10) Subqueries
24. sql.Query structures Query.alias_map, tables, alias_refcount Query.where Plus many more
25. SQLCompiler input: sql.Query output: rows from the DB setup execute return transformed rows
26. filter() demystified build_filter() setup_joins() names_to_path() model._meta.get_field() field.get_path_info() build_lookup()
27. Book.objects.filter(author__birth_date__year__lte=1981)