0

I have a Django website with activities. When checking for optimisation opportunities with the django-toolbar, I discovered that the view for an activity's subscription list was really inefficient. It made five database request per subscription, just to check if the user is a member.

My models are structured as follows:

class Subscription(models.Model):
    user = models.ForeignKey(User, null=True)       
    activity = models.ForeignKey(Activity)

class MemberProfile(models.Model):
    user = models.ForeignKey(User)
    member_in = models.ManyToManyField(WorkYear, blank=True, null=True)

class WorkYear(models.Model):
    year = models.SmallIntegerField(unique=True)
    current = models.BooleanField(default=False, blank=True)

Now, to check if the subscribed user is a member, we must check if there's a MemberProfile referring to it, with a WorkYear in its member_in field with current set to true.

I had a property in Subscription called is_member which returned this information. In the template this property was called for every subscription, resulting in a massive amount of database requests. Instead of doing this, I would like to add a custom field to the QuerySet created in the view.

I've experimented with the extra() function:

subscriptions = activity.subscription_set.extra(
        select={
            'is_member': 'SELECT current FROM activities_subscription LEFT OUTER JOIN (auth_user LEFT OUTER JOIN (members_memberprofile LEFT OUTER JOIN (members_memberprofile_member_in LEFT OUTER JOIN site_main_workyear ON members_memberprofile_member_in.workyear_id = site_main_workyear.id AND site_main_workyear.current = 1) ON members_memberprofile.id = members_memberprofile_member_in.memberprofile_id) ON auth_user.id = members_memberprofile.user_id) ON activities_subscription.user_id = auth_user.id'
        },
        tables=['site_main_workyear', 'members_memberprofile_member_in', 'members_memberprofile', 'auth_user']
    ).order_by('id')

This is really complex and for some reason it doesn't work. After reloading the page, Python takes 100% CPU and no response is given.

Is there a better and more simple way for doing this? And if not, what am I doing wrong?

Josh Scholl
  • 143
  • 15
Arthur C
  • 1,274
  • 1
  • 14
  • 34
  • Have you tried `activity.subscription_set.filter(user__memberprofile__member_in__current=True)`? But I'd introduce some redundancy to your DB schema just to get rid of those joins. – Maciej Gol Oct 13 '13 at 11:38
  • That's not what I want to do. I want all subscriptions, including subscriptions of users who are not a member. For every subscription I want an extra field `is_member`. – Arthur C Oct 14 '13 at 14:11
  • Why don't you add another, redundant field to git rid of all those joins? – Maciej Gol Oct 14 '13 at 16:40
  • That's a good idea, but then I should write a custom save method for MemberProfile and then edit all subscriptions belonging to that user if the membership settings have changed. I'll use this method if no other solution exists. Thank yo for the idea, I hadn't thought about it. – Arthur C Oct 15 '13 at 19:42

0 Answers0