Expressions (No Speaker Notes)

A copy of the slides used in my talk on expressions without speaker notes attached.

Transcript

1. DJANGO EXPRESSIONS UNDER THE HOOD
2. ME JOSH SMEATON ▸ https://github.com/jarshwah and jarshwah on IRC ▸ Software Engineer at Engage (a LivePerson company) ▸ SQL Nerd ▸ Primary author of Expressions ▸ Django core developer
3. EXPRESSIONS? http://www.schizophreniaresearch.org.au/bank/wp-content/uploads/2013/12/Reading-facial-expressions_article.jpg
4. EXPRESSIONS EXPRESSIONS IN DJANGO ▸ F('price') + shipping_fee ▸ Self contained parcels of SQL
5. EXPRESSIONS EXPRESSIONS IN DJANGO ▸ Deep ORM integration
6. EXPRESSIONS EXPRESSIONS IN DJANGO ▸ Multiple database backend support
7. EXPRESSIONS EXPRESSIONS IN DJANGO ▸ Eliminates most uses of .extra() and .raw()
8. ALTERNATIVES .RAW() AND .EXTRA() ▸ .raw() for writing entire query in SQL ▸ .extra() for appending bits of SQL ▸ Escape hatches
9. ALTERNATIVES .RAW() AND .EXTRA() PROBLEMS ▸ Database backend specific ▸ 3rd party libraries and apps ▸ Unknown table aliases ▸ Broken GROUP BY
11. EXPRESSIONS WHERE CAN THEY BE USED? ▸ .update(price=F('price') + tax) ▸ .create(..., username=Lower(username)) ▸ .annotate(total=F('price') + shipping) ▸ .aggregate(sum_total=Sum('total')) ▸ .order_by(Coalesce('last_name', 'first_name')) ▸ .filter(name=Lower(Value(user_input)))

12. EXPRESSIONS BATTERIES INCLUDED ▸ Functions ▸ Coalesce(), Concat(), Lower(), Upper() ▸ Hidden complexity ▸ F(), Case(), When() ▸ Building blocks ▸ Aggregate(), Func(), Value()
13. OLD EXPRESSIONS AGGREGATION PRE 1.8 Sum() Aggregate Public Interface Query sql.Sum sql.Aggregate Implementation nonrel.Sum nonrel.Aggregate Implementation Rendered SQL
14. OLD EXPRESSIONS F() EXPRESSIONS PRE 1.8 F() ExpressionNode Public Interface Query Rendered SQL evaluate recursively sql.Evaluator Implementation nonrel.Evaluator Implementation prepare recursively
15. NEW EXPRESSIONS Sum() or F() Expression Public Interface Query Render Children Rendered SQL Implementation EXPRESSIONS 1.8
16. SO, WHAT'S NEW? EXPRESSIONS IN 1.8 ▸ Public API with Documentation ▸ Composable ▸ Sum(F('talk') + F('hold')) + Sum('wrap') ▸ Simplified internals
17. https://xkcd.com/927/
18. WHO CARES? GREAT FOR USERS
19. WHO CARES? GREAT FOR LIBRARY AUTHORS
20. WHO CARES? PRETTY GOOD FOR 3RD PARTY BACKENDS ▸ DB2 uses Date() so would work with standard implementation. ▸ Monkey patch support for builtins. ▸ Backends can create their own types for obscure functions. See GIS backend.
21. WHO CARES? THE NOT SO GREATS ▸ Not able to add custom joins. Must resort to .extra(). ▸ Still early days and a few bugs still to fix. ▸ Documentation and examples. Too many people using ExpressionWrapper().
22. EXAMPLES
23. EXAMPLE - APPEND TEAM TO CALLRECORD
24. EXAMPLE - AVERAGE HANDLE TIME (AHT) PER TEAM
25. EXAMPLE - COUNT OF CALLS PER DAY
26. EXAMPLE - CONDITIONAL AGGREGATION
27. EXPRESSIONS API Combinable Resolvable Expression F('field') + F('other') :: Combined(left, "+", right) F('field').resolve_expression() :: Col("table"."field") Col("table"."field").as_sql() :: "table"."field"
28. EXPRESSIONS API RESOLVE_EXPRESSION ▸ Validates against the current query ▸ Returns a new expression or a copy ▸ Sets the new query state
29. EXPRESSIONS API AS_SQL ▸ as_sql() outputs default SQL ▸ as_{connection.vendor}() outputs vendor specific SQL ▸ Compile protocol. compiler.compile()
30. EXPRESSIONS API AS_SQL ▸ as_sql() outputs default SQL ▸ as_{connection.vendor}() outputs vendor specific SQL ▸ Compile protocol. compiler.compile()
31. EXPRESSIONS API OUTPUT_FIELD ▸ What field will the database return? ▸ convert_value() and db_converters() like to_python() ▸ lookups and transforms
32. EXPRESSIONS API AGGREGATE SUPPORT ▸ contains_aggregate() ▸ get_group_by_cols() ▸ refs_aggregate()
33. EXPRESSIONS API REUSING EXPRESSIONS ▸ relabeled_clone()
34. THE FUTURE OF EXPRESSIONS GREATER QUERYSET SUPPORT ▸ order_by() ▸ filter() ▸ values()
35. THE FUTURE OF EXPRESSIONS ALTERNATIVE SYNTAXES
36. THE FUTURE OF EXPRESSIONS WHAT ELSE CAN WE BUILD? ▸ Analytical/Window functions? ▸ GROUP BY CUBE/ROLLUP ▸ Aggregate FILTER WITH ▸ CTE (Common Table Expressions)
37. JOSH SMEATON THANK YOU! ▸ jarshwah on most of the medias ▸ Slides @ https://speakerdeck.com/jarshwah ▸ Code @ https://github.com/jarshwah/expressions_talk/