Code

Opened 23 months ago

Closed 23 months ago

Last modified 19 months ago

#18437 closed Bug (invalid)

Incorrect double JOIN when using multiple .filter() calls on the same table

Reported by: vdboor Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Attachments (0)

Change History (4)

comment:1 Changed 23 months ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to invalid
  • Status changed from new to closed

This is a feature. See: https://docs.djangoproject.com/en/1.4/topics/db/queries/#spanning-multi-valued-relationships.

Yes, the behavior can be confusing, but there is no better alternative available for the multi-valued relationships.

Last edited 23 months ago by akaariai (previous) (diff)

comment:2 Changed 23 months ago by vdboor

Ah, I understand, this has a use case too.

Perhaps a ..note:: would be good to add to the docs.
I didn't notice it before, only now that I know it exists I see which sentence makes a subtle reference to it.

comment:3 Changed 23 months ago by akaariai

There might be room for improvement here. If you can provide a patch that would be great (or, at least where in the docs you would like to see an addition, and what to add there).

comment:4 Changed 19 months ago by anonymous

I just ran into this issue. If you are doing filtering and later add some annotations like Count, Sum, etc. this is a unpleasant surprise and giving you wrong results.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.