On Fri, 2009-04-10 at 05:45 -0700, nikita kozlovsky wrote:
> On Mar 9, 3:21 am, Malcolm Tredinnick <malc...@pointy-stick.com>
> wrote:
>
> Hello, Malcolm.
>
> > > Again, the correct syntax would be:
> > > Message.objects.filter(student__isnull=True)
>
> Why ORM uses LEFT OUTER JOIN on this query ? Why not "... WHERE
> student_id IS NULL" ?
Django's SQL is going exactly what you suspect and not using any outer
join here. Using a simplified version of the original two models:
class Student(models.Model):
...
class Message(models.Model):
title = models.CharField(max_length=50)
student = models.ForeignKey(Student)
... the SQL that is generated for Message.objects.filter(student=None)
is:
SELECT `outer_message`.`id`, `outer_message`.`title`,
`outer_message`.`student_id` FROM `outer_message` WHERE
`outer_message`.`student_id` IS NULL
(I have put it into an application called "outer", hence the table
prefix and I happen to be using MySQL in my directory of test
applications at the moment, hence the backticks instead of normal single
quotes.)
The ORM tries to trim any unnecessary table joins, particularly towards
the end of the join sequence. Any times that it includes unnecessary
tables, or uses outer joins when we can reliably see it should be an
inner join is pretty much a bug. Here, for example, it knows that the
student_id field is a reliable indicator of whether there is a related
object, so it does exactly what you expect.
Sometimes people see extra table joins that they aren't expecting
because they are ordering by a field in the related model. For example,
using Meta.ordering = ("student",) here would require an extra join.
But, the simplest won't need more than the message table.
Does that answer your question?
Regards,
Malcolm
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
