I'm trying to understand why a LEFT OUTER JOIN is being used in
queries that filter on a NULL foreign key. It seems that the same
result can be achieved without the LEFT OUTER JOIN.
Here is an example:
> cat models.py
from django.db import models
from django.contrib.auth.models import User
# Create your models here.
class Widget(models.Model):
owner = models.ForeignKey(User, null=True, blank=True)
python manage.py shell
>>> from widgets.models import Widget
>>> from django.db import connection
>>> Widget.objects.filter(owner=None)
>>> connection.queries
[{'time': '0.001', 'sql': u'SELECT `widgets_widget`.`id`,
`widgets_widget`.`owner_id` FROM `widgets_widget` LEFT OUTER JOIN
`auth_user` ON (`widgets_widget`.`owner_id` = `auth_user`.`id`) WHERE
`auth_user`.`id` IS NULL LIMIT 21'}]
It seems that this could be accomplished with a simpler query that
does not use a join:
SELECT `widgets_widget`.`id`, `widgets_widget`.`owner_id` FROM
`widgets_widget` WHERE `owner_id` IS NULL LIMIT 21'}
This becomes a bigger issue when the filter is combined with an update
command as the join forces the update to be split into two SQL
commands. This opens the door to race conditions.
>>> from django.contrib.auth.models import
>>> u = User.objects.get(pk=1)
>>> connection.queries = []
>>> Widget.objects.filter(owner=None).update(owner=u)
1L
>>> connection.queries
[{'time': '0.006', 'sql': u'SELECT U0.`id` FROM `widgets_widget` U0
LEFT OUTER JOIN `auth_user` U1 ON (U0.`owner_id` = U1.`id`) WHERE
U1.`id` IS NULL'}, {'time': '0.002', 'sql': u'UPDATE `widgets_widget`
SET `owner_id` = 1 WHERE `widgets_widget`.`id` IN (1)'}]
>>>
The behavior is the also the same with owner__isnull=True.
Why is the LEFT OUTER JOIN used? Is there a different way this filter
should be structured so that the LEFT OUTER JOIN is not used?
Thanks,
Brian
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
