id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
18437	Incorrect double JOIN when using multiple .filter() calls on the same table	Diederik van der Boor	nobody	"When using `.filter()` twice, Django generates a different SQL then using the filters in one call.

In the sample below, both statements filter at the same fields, but return a different query:

{{{
    Message.objects.filter(usermessage_set__user=user).filter(usermessage_set__is_read=False)
    Message.objects.filter(usermessage_set__user=user, usermessage_set__is_read=False)
}}}

The first results in a double join at the same table:

{{{
    SELECT ""test_message"".""id"", ""test_message"".""from_user_id"" FROM ""test_message""
    INNER JOIN ""test_messagereadstate"" ON (""test_message"".""id"" = ""test_messagereadstate"".""message_id"")
    INNER JOIN ""test_messagereadstate"" T4 ON (""test_message"".""id"" = T4.""message_id"")
    WHERE (""test_messagereadstate"".""user_id"" = 12345  AND T4.""is_read"" = False )
}}}

Using everything in one `.filter()` avoids the second join:

{{{
    SELECT ""test_message"".""id"", ""test_message"".""from_user_id"" FROM ""test_message""
    INNER JOIN ""test_messagereadstate"" ON (""test_message"".""id"" = ""test_messagereadstate"".""message_id"")
    WHERE (""test_messagereadstate"".""is_read"" = False  AND ""test_messagereadstate"".""user_id"" = 12345 )
}}}

This is the whole example, that can be executed directly as a script:

{{{
#!/usr/bin/env python

# Inline settings file
from django.conf import settings
settings.configure(
    DEBUG = True,
    DATABASES = { 
        'default': {'ENGINE': 'django.db.backends.sqlite3', 'NAME': ':memory:'}   
    },  
)



from django.contrib.auth.models import User
from django.db import models

class Message(models.Model):
    from_user = models.ForeignKey(User, related_name='sent_messages')
    to_users = models.ManyToManyField(User, related_name='received_messages')

    class Meta:
        app_label = 'test'


class MessageReadState(models.Model):
    user = models.ForeignKey(User, related_name='usermessage_set')
    message = models.ForeignKey(Message, related_name='usermessage_set')
    is_read = models.BooleanField(default=False)

    class Meta:
        app_label = 'test'


if __name__ == '__main__':
    user = 12345
    print Message.objects.filter(usermessage_set__user=user).filter(usermessage_set__is_read=False).query
    print Message.objects.filter(usermessage_set__user=user, usermessage_set__is_read=False).query

}}}

This happens in both Django 1.3 and 1.4"	Bug	closed	Database layer (models, ORM)	1.4	Normal	invalid			Unreviewed	0	0	0	0	0	0