Suppose I have the following models:
---
class Container(models.Model):
pass
class Item(models.Model):
container = models.ForeignKey(Container)
previous = models.ForeignKey('self', null=True)
current = models.BooleanField()
flag = models.BooleanField()
---
Item represents a chain of items, all grouped by a container (like a
linked list). The field "current" represents the most recent item
(front of the list). The field "flag" is simply something to query on.
Suppose I perform the following query:
---
Item.objects.filter(current=True,
container__item__previous__isnull=True, container__item__flag=True)
---
That is, I'm looking for all current items such that the first item in
the chain has flag = true. Django 1.6 produces the following SQL for
this query:
---
SELECT ...
FROM "myapp_item"
INNER JOIN "myapp_container"
ON ( "myapp_item"."container_id" = "myapp_container"."id" )
LEFT OUTER JOIN "myapp_item" T3
ON ( "myapp_container"."id" = T3."container_id" )
WHERE ("myapp_item"."current" = True AND T3."previous_id" IS NULL AND
T3."flag" = True )
---
The OUTER JOIN is the problem. Why is this not a more efficient INNER
JOIN? This query is very inefficient as the the database gets larger.
This causes slow downs on pages.
Interestingly, if I try this on 1.7, this query is apparently not even
supported. I get the following error:
---
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/home/jon/djtest/djtest/test.py", line 3, in <module>
qs = Item.objects.filter(current=True,
container__item__previous__isnull=True, container__item__flag=True)
File
"/home/jon/djtest/venv/lib/python2.7/site-packages/django/db/models/manager.py",
line 92, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File
"/home/jon/djtest/venv/lib/python2.7/site-packages/django/db/models/query.py",
line 689, in filter
return self._filter_or_exclude(False, *args, **kwargs)
File
"/home/jon/djtest/venv/lib/python2.7/site-packages/django/db/models/query.py",
line 707, in _filter_or_exclude
clone.query.add_q(Q(*args, **kwargs))
File
"/home/jon/djtest/venv/lib/python2.7/site-packages/django/db/models/sql/query.py",
line 1287, in add_q
clause, require_inner = self._add_q(where_part, self.used_aliases)
File
"/home/jon/djtest/venv/lib/python2.7/site-packages/django/db/models/sql/query.py",
line 1314, in _add_q
current_negated=current_negated, connector=connector)
File
"/home/jon/djtest/venv/lib/python2.7/site-packages/django/db/models/sql/query.py",
line 1181, in build_filter
lookups, value)
File
"/home/jon/djtest/venv/lib/python2.7/site-packages/django/db/models/fields/related.py",
line 1506, in get_lookup_constraint
raise exceptions.FieldError('Relation fields do not support nested lookups')
FieldError: Relation fields do not support nested lookups
---
A slow query is better than no query at all. Any particular reason
this was removed? How would one model and query what I'm trying to
achieve moving forward to avoid inefficient queries and exceptions?
Should I file this as a bug?
Cheers,
Jon
--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/CADhq2b7%3DcsYSOU2gSCL%3DwTD8Mztc9On%2ByRLWmUef%2B-y-fWryuA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
