QuerySet Filters on Many-to-many Relations. Django ORM (Object-relational mapping) makes querying the database so intuitive, that at some point you might forget that SQL is being used in the background. This year at the DjangoCon Europe Katie McLaughlin was giving a talk and mentioned one thing that affects the SQL query generated by Django ORM. Aggregation is a source of confusion in any type of ORM and Django is no different. The documentation provides a variety of examples and cheat-sheets that demonstrate how to group and aggregate data using the ORM, but I decided to approach this from a different angle. In this article I put QuerySets and SQL side by side. If SQL is where you are most comfortable, this is the Django GROUP BY cheat-sheet.
This cheat sheet will introduce Django developers to the API of Elasticsearch DSL. Use it for a Django website when you need to search or filter data fast. The cheat sheet compares the syntax of Django QuerySets with the syntax of Elasticsearch DSL.
You will get these questions answered:
- How to define search queries out of an index document?
- How to count elements in a search query?
- How to read through the elements of a search query?
- How to see what query the domain-specific language will produce?
- How to filter the index records by a field that contains a value?
- How to filter the index records by a field that is exactly equal to a value?
- How to filter by any of the given conditions (OR)?
- How to filter by all given conditions (AND)?
- How to filter the index records by values less than or equal to certain value?
- How to filter by a single value in a nested field?
- How to filter by one of many values in a related model?
- How to sort search results by the values of a specific field?
- How to create queries dynamically?
- How to paginate the results using Django's default pagination?
And all of that is compressed on a single sheet of paper!
Get it now and master your craft like a charm!
Current document revision is 05. Last modified in March, 2019. PDF in color. Syntax highlighted.
Information gather from original post at:
https://gist.github.com/levidyrek/6db1cf88b953f3f006bf678a0f09da8e#file-django_orm_optimization_cheat_sheet-py
Caveats:
- Only use optimizations that obfuscate the code if you need to.
- Not all of these tips are hard and fast rules.
- Use your judgement to determine what improvements are appropriate for your code.
1. Profile
Use these tools:
- django-debug-toolbar
- QuerySet.explain()
2. Be aware of QuerySet’s lazy evaluation.
2a. When QuerySets are evaluated
Iteration
Slicing/Indexing
Pickling (i.e. serialization)
Evaluation functions
Other
2b. When QuerySets are cached/not cached
Not Cached
Not reusing evaluated QuerySets
Slicing/indexing unevaluated QuerySets
Printing
Cached
Reusing an evaluated QuerySet
Slicing/indexing evaluated QuerySets
3. Be aware of which attributes are not cached.
Not initially retrieved/cached
Foreign-key related objects
Never cached
Callable attributes
4. Use select_related() and prefetch_related() when you will need everything.
DON’T
DO
5. Try to avoid database queries in a loop.
DON’T (contrived example)
DO (contrived example)
6. Use iterator() to iterate through a very large QuerySet only once.
Save memory by not caching anything
7. Do work in the database rather than in Python.
7a. Use filter() and exclude()
DON’T
DO
7b. Use F expressions
DON’T
DO
7c. Do aggregation in the database, if possible
DON’T
DO
8. Use values() and values_list() to get only the things you need.
8a. Use values()
DON’T
DO
8b. Use values_list()
DON’T
DO
- Use defer() and only() when you know you won’t need certain fields.
- Use when you need a QuerySet instead of a list of dicts from values().
- Really only useful to defer fields that require significant processing to convert to a python object.
9a. Use defer()
9b. Use only()
- Use count() and exists() when you don’t need the contents of the QuerySet.
* Caveat: Only use these when you don’t need to evaluate the QuerySet.
10a. Use count()
DON’T
DO
10b. Use exists()
DON’T
DO
11. Use delete() and update() when possible.
11a. Use delete()
Django Orm Cheat Sheet Pdf
DON’T
DO
11b. Use update()
DON’T
DO
- Use bulk_create() when possible.
* Caveats: https://docs.djangoproject.com/en/2.1/ref/models/querysets/#django.db.models.query.QuerySet.bulk_create
Bulk Create
Bulk add to many-to-many fields
13. Use foreign key values directly.
Django Orm Count
DON’T
DO