diff options
| -rw-r--r-- | django/db/models/query.py | 9 | ||||
| -rw-r--r-- | django/db/models/sql/query.py | 12 | ||||
| -rw-r--r-- | docs/ref/models/querysets.txt | 38 | ||||
| -rw-r--r-- | tests/regressiontests/queries/models.py | 7 |
4 files changed, 59 insertions, 7 deletions
diff --git a/django/db/models/query.py b/django/db/models/query.py index 5b9f504d2f..2f9f9e24ec 100644 --- a/django/db/models/query.py +++ b/django/db/models/query.py @@ -641,6 +641,15 @@ class QuerySet(object): """ pass + def as_sql(self): + """ + Returns the internal query's SQL and parameters (as a tuple). + + This is a private (internal) method. The name is chosen to provide + uniformity with other interfaces (in particular, the Query class). + """ + obj = self.values("pk") + return obj.query.as_nested_sql() class ValuesQuerySet(QuerySet): def __init__(self, *args, **kwargs): diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py index 61c1e51557..432e5e6332 100644 --- a/django/db/models/sql/query.py +++ b/django/db/models/sql/query.py @@ -313,6 +313,18 @@ class BaseQuery(object): params.extend(self.extra_params) return ' '.join(result), tuple(params) + def as_nested_sql(self): + """ + Perform the same functionality as the as_sql() method, returning an + SQL string and parameters. However, the alias prefixes are bumped + beforehand (in a copy -- the current query isn't changed). + + Used when nesting this query inside another. + """ + obj = self.clone() + obj.bump_prefix() + return obj.as_sql() + def combine(self, rhs, connector): """ Merge the 'rhs' query into the current one (with any 'rhs' effects diff --git a/docs/ref/models/querysets.txt b/docs/ref/models/querysets.txt index 3525865ac6..2334cf0754 100644 --- a/docs/ref/models/querysets.txt +++ b/docs/ref/models/querysets.txt @@ -1035,12 +1035,27 @@ SQL equivalent:: SELECT ... WHERE id IN (1, 3, 4); You can also use a queryset to dynamically evaluate the list of values -instead of providing a list of literal values. The queryset must be -reduced to a list of individual values using the ``values()`` method, -and then converted into a query using the ``query`` attribute:: +instead of providing a list of literal values:: - q = Blog.objects.filter(name__contains='Cheddar').values('pk').query - e = Entry.objects.filter(blog__in=q) + inner_qs = Blog.objects.filter(name__contains='Cheddar') + entries = Entry.objects.filter(blog__in=inner_qs) + +This queryset will be evaluated as subselect statement:: + + SELECT ... WHERE blog.id IN (SELECT id FROM ... WHERE NAME LIKE '%Cheddar%') + +The above code fragment could also be written as follows:: + + inner_q = Blog.objects.filter(name__contains='Cheddar').values('pk').query + entries = Entry.objects.filter(blog__in=inner_q) + +.. versionchanged:: 1.1 + In Django 1.0, only the latter piece of code is valid. + +This second form is a bit less readable and unnatural to write, since it +accesses the internal ``query`` attribute and requires a ``ValuesQuerySet``. +If your code doesn't require compatibility with Django 1.0, use the first +form, passing in a queryset directly. .. warning:: @@ -1048,9 +1063,18 @@ and then converted into a query using the ``query`` attribute:: It's fine to use it like above, but its API may change between Django versions. -This queryset will be evaluated as subselect statement:: +.. admonition:: Performance considerations - SELECT ... WHERE blog.id IN (SELECT id FROM ... WHERE NAME LIKE '%Cheddar%') + Be cautious about using nested queries and understand your database + server's performance characteristics (if in doubt, benchmark!). Some + database backends, most notably MySQL, don't optimize nested queries very + well. It is more efficient, in those cases, to extract a list of values + and then pass that into the second query. That is, execute two queries + instead of one:: + + values = Blog.objects.filter( + name__contains='Cheddar').values_list('pk', flat=True) + entries = Entry.objects.filter(blog__in=values) gt ~~ diff --git a/tests/regressiontests/queries/models.py b/tests/regressiontests/queries/models.py index 94f2045fe7..a3247bb460 100644 --- a/tests/regressiontests/queries/models.py +++ b/tests/regressiontests/queries/models.py @@ -1008,6 +1008,13 @@ cases). # optimise the inner query without losing results. >>> Annotation.objects.exclude(tag__children__name="t2") [<Annotation: a2>] + +Nested queries are possible (although should be used with care, since they have +performance problems on backends like MySQL. + +>>> Annotation.objects.filter(notes__in=Note.objects.filter(note="n1")) +[<Annotation: a1>] + """} # In Python 2.3 and the Python 2.6 beta releases, exceptions raised in __len__ |