StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POEfficiently add a custom field to a QuerySet
text
Body
copied!<p>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.</p> <p>My models are structured as follows:</p> <pre><code>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) </code></pre> <p>Now, to check if the subscribed user is a member, we must check if there's a <code>MemberProfile</code> referring to it, with a <code>WorkYear</code> in its <code>member_in</code> field with <code>current</code> set to true.</p> <p>I had a property in <code>Subscription</code> called <code>is_member</code> 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.</p> <p>I've experimented with the <code>extra()</code> function:</p> <pre><code>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') </code></pre> <p>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.</p> <p>Is there a better and more simple way for doing this? And if not, what am I doing wrong?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload