Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

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

58ad2c69ea5176eb9059984ada42b182?s=128

Django: Under The Hood

November 15, 2014
Tweet

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)