unjoinify: a module to tame the SQL beast

From DjangoCon Europe 2011: introducing unjoinify, a module to assist in working with deeply-nested data sets

Transcript

1. unjoinify: a module to tame the SQL beast Matt Westcott DjangoCon Europe, 7th June 2011
2. The n+1 query problem
3. The n+1 query problem Eager loading
4. The n+1 query problem Eager loading "that thing that really bugs me about select_related"
6. Festival
7. Festival ↳ Award
8. Festival ↳ Award ↳ Nomination
9. Festival ↳ Award ↳ Nomination ↳ Movie
10. Festival ↳ Award ↳ Nomination ↳ Movie ↳ Person (director)
11. Festival ↳ Award ↳ Nomination ↳ Movie ↳ Person (director) ↳ Company
12. def show(request, festival_id): festival = get_object_or_404(Festival, id = festival_id) return render(request, 'festivals/show.html', { 'festival': festival} ) <h1>{{ festival.name }}</h1> {% for award in festival.awards %} <h2>{{ award }}</h2> <table> {% for nomination in award.nominations %} <tr> <td>{{ nomination.movie.name }}</td> (...) </tr> {% endfor %} </table> {% endfor %}
13. def show(request, festival_id): festival = get_object_or_404(Festival, id = festival_id, select_related('award__nomination__movie')) return render(request, 'festivals/show.html', { 'festival': festival} ) <h1>{{ festival.name }}</h1> {% for award in festival.awards %} <h2>{{ award }}</h2> <table> {% for nomination in award.nominations %} <tr> <td>{{ nomination.movie.name }}</td> (...) </tr> {% endfor %} </table> {% endfor %}
16. people = Person.objects.filter( movies_directed__nominations__award__festival_id = festival_id) {% regroup people by award as award_list %} {% for award in award_list %} <h2>{{ award.grouper }}</h2> <table> {% regroup award.list by movie as movie_list %} {% for movie in movie_list %} <tr> <td>{{ movie.grouper }}</td> … </tr> {% endfor %} </table> {% endfor %}
17. SELECT imdb_award.id, imdb_award.name, imdb_nomination.id AS nomination__id, imdb_nomination.ranking AS nomination__ranking, imdb_movie.id AS nomination__movie__id, imdb_movie.title AS nomination__movie__title, imdb_person.id AS nomination__movie__directors__id, imdb_person.first_name AS nomination__movie__directors__first_name imdb_person.surname AS nomination__movie__directors__surname FROM imdb_award LEFT JOIN imdb_nomination ON (imdb_award.id = imdb_nomination.award_id) LEFT JOIN imdb_movie ON (imdb_nomination.movie_id = imdb_movie.id) LEFT JOIN imdb_movie_directors ON (imdb_movie.id = imdb_movie_directors.movie_id) LEFT JOIN imdb_person ON (imdb_movie_directors.person_id = imdb_person.id) WHERE imdb_award.festival_id = ? ORDER BY imdb_award.name, imdb_nomination.ranking
18. id | name | nomination_id | rank | movie_id | movie_title | director_id | director_name ---+---------------+---------------+------+----------+-------------------+-------------+--------------- 2 | Best Director | 4 | 1 | 1 | The King's Speech | 1 | Iain Canning 2 | Best Director | 4 | 1 | 1 | The King's Speech | 2 | Emile Sherman 2 | Best Director | 4 | 1 | 1 | The King's Speech | 3 | Gareth Unwin 2 | Best Director | 5 | 2 | 2 | 127 Hours | 4 | Danny Boyle 2 | Best Director | 5 | 2 | 2 | 127 Hours | 5 | Christian Colson 2 | Best Director | 6 | 3 | 4 | True Grit | 10 | Joel Coen 2 | Best Director | 6 | 3 | 4 | True Grit | 9 | Ethan Coen 2 | Best Director | 6 | 3 | 4 | True Grit | 11 | Scott Rudin 1 | Best Picture | 1 | 1 | 1 | The King's Speech | 1 | Iain Canning 1 | Best Picture | 1 | 1 | 1 | The King's Speech | 2 | Emile Sherman 1 | Best Picture | 1 | 1 | 1 | The King's Speech | 3 | Gareth Unwin 1 | Best Picture | 2 | 2 | 2 | 127 Hours | 4 | Danny Boyle 1 | Best Picture | 2 | 2 | 2 | 127 Hours | 5 | Christian Colson 1 | Best Picture | 3 | 3 | 3 | Black Swan | 6 | Scott Franklin 1 | Best Picture | 3 | 3 | 3 | Black Swan | 7 | Mike Medavoy 1 | Best Picture | 3 | 3 | 3 | Black Swan | 8 | Brian Oliver
19. id | name | nomination_id | rank | movie_id | movie_title | director_id | director_name ---+---------------+---------------+------+----------+-------------------+-------------+--------------- 2 | Best Director | 4 | 1 | 1 | The King's Speech | 1 | Iain Canning 2 | Best Director | 4 | 1 | 1 | The King's Speech | 2 | Emile Sherman 2 | Best Director | 4 | 1 | 1 | The King's Speech | 3 | Gareth Unwin 2 | Best Director | 5 | 2 | 2 | 127 Hours | 4 | Danny Boyle 2 | Best Director | 5 | 2 | 2 | 127 Hours | 5 | Christian Colson 2 | Best Director | 6 | 3 | 4 | True Grit | 10 | Joel Coen 2 | Best Director | 6 | 3 | 4 | True Grit | 9 | Ethan Coen 2 | Best Director | 6 | 3 | 4 | True Grit | 11 | Scott Rudin 1 | Best Picture | 1 | 1 | 1 | The King's Speech | 1 | Iain Canning 1 | Best Picture | 1 | 1 | 1 | The King's Speech | 2 | Emile Sherman 1 | Best Picture | 1 | 1 | 1 | The King's Speech | 3 | Gareth Unwin 1 | Best Picture | 2 | 2 | 2 | 127 Hours | 4 | Danny Boyle 1 | Best Picture | 2 | 2 | 2 | 127 Hours | 5 | Christian Colson 1 | Best Picture | 3 | 3 | 3 | Black Swan | 6 | Scott Franklin 1 | Best Picture | 3 | 3 | 3 | Black Swan | 7 | Mike Medavoy 1 | Best Picture | 3 | 3 | 3 | Black Swan | 8 | Brian Oliver <a href="{{ movie.get_absolute_url }}">{{ movie }}</a>
20. my_results = cursor.execute(''' SELECT id, name, nomination.id AS nomination__id, FROM ... WHERE ... ''') awards = do_some_magic(my_results); return render(request, 'festivals/show.html', { 'festival': festival, 'awards': awards, })
21. my_results = cursor.execute(''' SELECT id, name, nomination.id AS nomination__id, FROM ... WHERE ... ''') awards = do_some_magic(my_results); return render(request, 'festivals/show.html', { 'festival': festival, 'awards': awards, }) django-unjoinify: it isn't magic.
22. from unjoinify import unjoinify query = ''' SELECT imdb_award.id, imdb_award.name, imdb_nomination.id AS nomination__id, imdb_nomination.ranking AS nomination__ranking, imdb_movie.id AS nomination__movie__id, imdb_movie.title AS nomination__movie__title, FROM imdb_award LEFT JOIN imdb_nomination ON (imdb_award.id = imdb_nomination.award_id) LEFT JOIN imdb_movie ON (imdb_nomination.movie_id = imdb_movie.id) WHERE imdb_award.festival_id = %s ''' awards = unjoinify(Award, query, (festival.id,))
23. id name nomination__id nomination__ranking nomination__movie__id nomination__movie__title nomination__movie__directors__id nomination__movie__directors__first_name nomination__movie__directors__surname nomination__movie__production_companies__id nomination__movie__production_companies__name
24. id name nomination__id nomination__ranking nomination__movie__id nomination__movie__title nomination__movie__directors__id nomination__movie__directors__first_name nomination__movie__directors__surname nomination__movie__production_companies__id nomination__movie__production_companies__name awards = (award, [nominations]) where: nominations = (nomination, movie, directors, companies)
25. {% for award in awards %} <h2>{{ award.name }}</h2> <table> {% for nomination in award.nominations %} <tr> <td>{{ nomination.movie.name }}</td> <td> {% for director in nomination.movie.directors %} {{ director.first_name }} {{ director.surname }} {% endfor %} </td> <td> {% for company in nomination.movie.companies %} {{ company.name }} {% endfor %} </td> </tr> {% endfor %} </table> {% endfor %}
26. {% for award, nominations in awards %} <h2>{{ award.name }}</h2> <table> {% for nomination, movie, directors, companies in nominations %} <tr> <td>{{ movie.name }}</td> <td> {% for director in directors %} {{ director.first_name }} {{ director.surname }} {% endfor %} </td> <td> {% for company in companies %} {{ company.name }} {% endfor %} </td> </tr> {% endfor %} </table> {% endfor %}
27. make_unpack_plan(Movie, columns, prefix = 'nomination__movie__') id name nomination__id nomination__ranking nomination__movie__id nomination__movie__title nomination__movie__directors__id nomination__movie__directors__first_name nomination__movie__directors__surname nomination__movie__production_companies__id nomination__movie__production_companies__name
28. make_unpack_plan(Movie, columns, prefix = 'nomination__movie__') id name nomination__id nomination__ranking nomination__movie__id nomination__movie__title nomination__movie__directors__id nomination__movie__directors__first_name nomination__movie__directors__surname nomination__movie__production_companies__id nomination__movie__production_companies__name => ( {model: Movie, fields: ('id','title')}, [{model: Person, fields: ('id','first_name','surname')}], [{model: Company, fields: ('id','name')}], )
29. id | name | nomination_id | rank | movie_id | movie_title | director_id | director_name ---+---------------+---------------+------+----------+-------------------+-------------+--------------- 2 | Best Director | 4 | 1 | 1 | The King's Speech | 1 | Iain Canning 2 | Best Director | 4 | 1 | 1 | The King's Speech | 2 | Emile Sherman 2 | Best Director | 4 | 1 | 1 | The King's Speech | 3 | Gareth Unwin 2 | Best Director | 5 | 2 | 2 | 127 Hours | 4 | Danny Boyle 2 | Best Director | 5 | 2 | 2 | 127 Hours | 5 | Christian Colson 2 | Best Director | 6 | 3 | 4 | True Grit | 10 | Joel Coen 2 | Best Director | 6 | 3 | 4 | True Grit | 9 | Ethan Coen 2 | Best Director | 6 | 3 | 4 | True Grit | 11 | Scott Rudin 1 | Best Picture | 1 | 1 | 1 | The King's Speech | 1 | Iain Canning 1 | Best Picture | 1 | 1 | 1 | The King's Speech | 2 | Emile Sherman 1 | Best Picture | 1 | 1 | 1 | The King's Speech | 3 | Gareth Unwin 1 | Best Picture | 2 | 2 | 2 | 127 Hours | 4 | Danny Boyle 1 | Best Picture | 2 | 2 | 2 | 127 Hours | 5 | Christian Colson 1 | Best Picture | 3 | 3 | 3 | Black Swan | 6 | Scott Franklin 1 | Best Picture | 3 | 3 | 3 | Black Swan | 7 | Mike Medavoy 1 | Best Picture | 3 | 3 | 3 | Black Swan | 8 | Brian Oliver unpack_with_plan(plan, results)
30. id | name | nomination_id | rank | movie_id | movie_title | director_id | director_name ---+---------------+---------------+------+----------+-------------------+-------------+--------------- 2 | Best Director | 4 | 1 | 1 | The King's Speech | 1 | Iain Canning 2 | Best Director | 4 | 1 | 1 | The King's Speech | 2 | Emile Sherman 2 | Best Director | 4 | 1 | 1 | The King's Speech | 3 | Gareth Unwin 2 | Best Director | 5 | 2 | 2 | 127 Hours | 4 | Danny Boyle 2 | Best Director | 5 | 2 | 2 | 127 Hours | 5 | Christian Colson 2 | Best Director | 6 | 3 | 4 | True Grit | 10 | Joel Coen 2 | Best Director | 6 | 3 | 4 | True Grit | 9 | Ethan Coen 2 | Best Director | 6 | 3 | 4 | True Grit | 11 | Scott Rudin 1 | Best Picture | 1 | 1 | 1 | The King's Speech | 1 | Iain Canning 1 | Best Picture | 1 | 1 | 1 | The King's Speech | 2 | Emile Sherman 1 | Best Picture | 1 | 1 | 1 | The King's Speech | 3 | Gareth Unwin 1 | Best Picture | 2 | 2 | 2 | 127 Hours | 4 | Danny Boyle 1 | Best Picture | 2 | 2 | 2 | 127 Hours | 5 | Christian Colson 1 | Best Picture | 3 | 3 | 3 | Black Swan | 6 | Scott Franklin 1 | Best Picture | 3 | 3 | 3 | Black Swan | 7 | Mike Medavoy 1 | Best Picture | 3 | 3 | 3 | Black Swan | 8 | Brian Oliver unpack_with_plan(plan, results)
31. Festival ↳ Award ↳ Nomination ↳ Movie ↳ Person (director) ↳ Company beware the cartesian join
32. a_very_long_column_alias_name_that_exceeds_the_limi
33. a_very_long_column_alias_name_that_exceeds_the_limi unjoinify(model, query, params, column_names)
34. the masterplan • 1. Get select_related to auto-generate the SQL • 2. Reconstruct models from the resultset ✔ • 3. 'Push' child objects into their respective RelatedManagers • 4. Profit!
35. github.com/simonw/django-queryset-transform
36. https://github.com/gasman/django-unjoinify pip install django-unjoinify Thank you! Matt Westcott http://matt.west.co.tt/ @westdotcodottt