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

Expressions (No Speaker Notes)

Expressions (No Speaker Notes)

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

3891f38e5165127cac92a4e9e4aba688?s=128

Josh Smeaton

November 15, 2015
Tweet

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
  10. None
  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/