Opened 10 years ago

Closed 10 years ago

#10086 closed (duplicate)

select_related and annotate weird behaviour

Reported by: panni@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: select_related, annotate
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Considering the following Models:

class BlogEntry(models.Model):
    user = models.ForeignKey(User, related_name="BlogEntryUser")
    datetime = models.DateTimeField(auto_now_add=True)
    datetimeModified = models.DateTimeField(null=True, blank=True)
    title = models.CharField(max_length=255)
    titleSlug = models.SlugField(editable=False)
    text = models.TextField()
    hitCount = models.IntegerField("Zugriffe", default=0, blank=True, null=True, editable=False)
    visitors = models.ManyToManyField(User, null=True, blank=True, related_name="BlogEntryVisitorsUser")

class BlogEntryComment(models.Model):
    comment = models.ForeignKey(Comment, verbose_name="Comment", related_name="BlogEntryCommentComment")
    motherModel = models.ForeignKey(BlogEntry, verbose_name="Blog Eintrag", related_name="BlogEntryCommentBlogEntry")

class Comment(models.Model):
    text     = models.TextField("Inhalt")
    author   = models.ForeignKey(User, related_name="CommentAuthorUser", verbose_name="Autor")
    datetime = models.DateTimeField("Geschrieben um", editable=False)
    lastedit = models.DateTimeField("Zuletzt editiert am", editable=False, null=True, blank=True)
    lastedit_by = models.ForeignKey(User, related_name="CommentLastEditUser", verbose_name="Autor", editable=False, null=True, blank=True)

# Using the following annotated query:
lastWeek = - datetime.timedelta(days=7)
bestBlogs = BlogEntry.objects.select_related("user", "BlogEntryCommentBlogEntrycomment", "BlogEntryCommentBlogEntrycommentauthor").filter(datetimegte = lastWeek).annotate(Count("BlogEntryCommentBlogEntry")).order_by("-BlogEntryCommentBlogEntrycount")[:settings.NUM_HOME_SIDEBAR_COMMUNITY_TOPBLOGS]

# then doing
print dir(bestBlogs[0].user), bestBlogs[0].user.username

# results in
['AdminNoticeUser', 'AdminUserNoteUser', 'BlogEntryUser', 'BlogEntryVisitorsUser', 'CommentAuthorUser', 'CommentLastEditUser', 'DoesNotExist', 'ForumThreadUser', 'MultipleObjectsReturned', 'PickRoomClient1User', 'PickRoomClient2User', 'PickRoomMasterCreator', 'PickRoomMasterUser', 'PlayerFanClubMemberUser', 'PublicGalleryItems', 'TeamFanClubMemberUser', 'UserBetUser', 'UserFlatPage', 'UserNoteAdmin', 'class', 'delattr', 'dict', 'doc', 'eq', 'getattribute', 'hash', 'init', 'metaclass', 'module', 'ne', 'new', 'reduce', 'reduce_ex', 'repr', 'setattr', 'str', 'unicode', 'weakref', '_collect_sub_objects', '_default_manager', '_get_FIELD_display', '_get_next_or_previous_by_FIELD', '_get_next_or_previous_in_order', '_get_pk_val', '_meta', '_set_pk_val', 'article_set', 'check_password', 'date_joined', 'delete', 'email', 'email_user', 'first_name', 'get_absolute_url', 'get_all_permissions', 'get_and_delete_messages', 'get_full_name', 'get_group_permissions', 'get_next_by_date_joined', 'get_next_by_last_login', 'get_previous_by_date_joined', 'get_previous_by_last_login', 'get_profile', 'groups', 'has_module_perms', 'has_perm', 'has_perms', 'has_usable_password', 'id', 'is_active', 'is_anonymous', 'is_authenticated', 'is_staff', 'is_superuser', 'last_login', 'last_name', 'logentry_set', 'message_set', 'objects', 'password', 'pk', 'registrationprofile_set', 'save', 'save_base', 'serializable_value', 'set_password', 'set_unusable_password', 'user_permissions', 'username', 'userprofile_set'] 3

Notice the "3" in user.username.

Issuing the query without "user" in the select_related() clause results in:
['AdminNoticeUser', 'AdminUserNoteUser', 'BlogEntryUser', 'BlogEntryVisitorsUser', 'CommentAuthorUser', 'CommentLastEditUser', 'DoesNotExist', 'ForumThreadUser', 'MultipleObjectsReturned', 'PickRoomClient1User', 'PickRoomClient2User', 'PickRoomMasterCreator', 'PickRoomMasterUser', 'PlayerFanClubMemberUser', 'PublicGalleryItems', 'TeamFanClubMemberUser', 'UserBetUser', 'UserFlatPage', 'UserNoteAdmin', 'class', 'delattr', 'dict', 'doc', 'eq', 'getattribute', 'hash', 'init', 'metaclass', 'module', 'ne', 'new', 'reduce', 'reduce_ex', 'repr', 'setattr', 'str', 'unicode', 'weakref', '_collect_sub_objects', '_default_manager', '_get_FIELD_display', '_get_next_or_previous_by_FIELD', '_get_next_or_previous_in_order', '_get_pk_val', '_meta', '_set_pk_val', 'article_set', 'check_password', 'date_joined', 'delete', 'email', 'email_user', 'first_name', 'get_absolute_url', 'get_all_permissions', 'get_and_delete_messages', 'get_full_name', 'get_group_permissions', 'get_next_by_date_joined', 'get_next_by_last_login', 'get_previous_by_date_joined', 'get_previous_by_last_login', 'get_profile', 'groups', 'has_module_perms', 'has_perm', 'has_perms', 'has_usable_password', 'id', 'is_active', 'is_anonymous', 'is_authenticated', 'is_staff', 'is_superuser', 'last_login', 'last_name', 'logentry_set', 'message_set', 'objects', 'password', 'pk', 'registrationprofile_set', 'save', 'save_base', 'serializable_value', 'set_password', 'set_unusable_password', 'user_permissions', 'username', 'userprofile_set'] Rudi

Notice the real username returned

Change History (2)

comment:1 Changed 10 years ago by panni@…

Sorry for the bad formatting, here are the queries reformatted:

bestBlogs = BlogEntry.objects.select_related("user", "BlogEntryCommentBlogEntry__comment", "BlogEntryCommentBlogEntry__comment__author").filter(datetime__gte = lastWeek).annotate(Count("BlogEntryCommentBlogEntry")).order_by("-BlogEntryCommentBlogEntry__count")[:settings.NUM_HOME_SIDEBAR_COMMUNITY_TOPBLOGS]
    print dir(bestBlogs[0].user), bestBlogs[0].user.username

bestBlogs = BlogEntry.objects.select_related("BlogEntryCommentBlogEntry__comment", "BlogEntryCommentBlogEntry__comment__author").filter(datetime__gte = lastWeek).annotate(Count("BlogEntryCommentBlogEntry")).order_by("-BlogEntryCommentBlogEntry__count")[:settings.NUM_HOME_SIDEBAR_COMMUNITY_TOPBLOGS]
    print dir(bestBlogs[0].user), bestBlogs[0].user.username

comment:2 Changed 10 years ago by Russell Keith-Magee

Resolution: duplicate
Status: newclosed

First off - could I please request that for your next bug report, you please trim your submitted code down to a minimal example. I'm guessing that this is an example drawn from your own application, so you're in the best position to strip this down to a minimal test case. This means removing everything that isn't required in order to demonstrate the problem. In this case, I strongly suspect that the filter clause, order by, [] subscripting, printing the dir() of the user instance, and most of the fields on the model are not required to demonstrate or describe the problem. Including some sample data is also a good idea - anything that makes a bug easier to reproduce is helpful.

Secondly - I don't think select_related works quite like you think it does. Your second query is _exactly_ the same as

bestBlogs = BlogEntry.objects.filter(datetime__gte = lastWeek).annotate(Count("BlogEntryCommentBlogEntry")).order_by("-BlogEntryCommentBlogEntry__count")[:settings.NUM_HOME_SIDEBAR_COMMUNITY_TOPBLOGS]

That is - in the second case, the select_related clause does nothing. select_related() only works with foreign keys defined on the model you start from. You can't just add arbitrary joins. In your specific case, you should probably be getting an error telling you that your select_related clauses are effectively no-ops.

Lastly, it is generally a good idea to include a version number or SVN revision number of the code you were using when you found the problem. This is especially important when you are reporting a bug in a new feature, where bugs are getting fixed regularly. In this case, I suspect that this bug is a version of #10064, which was resolved in [9777]. As it stands, I have no way of knowing if this is a new bug or just a manifestation of an old bug.

Since I can't confirm either way, I'm going to close this as a duplicate of #10064. However, if you can verify that the problem still exists on a post [9777] revision of trunk, and you can provide a simplified example of the problem, feel free to reopen this ticket.

Note: See TracTickets for help on using tickets.
Back to Top