Hi folks,
I have a problem with N+1 querys when not using the Django ORM, where the solution isn’t obvious to me, and I figured asking here might help.
I have a sort of “shared address book” application in Django, with contains a Profile model, which has a photo attribute like so:
# abridged for readability
class Profile(models.Model):
user = models.OneToOneField(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
website = models.URLField(_("website"), max_length=200, blank=True, null=True)
organisation = models.CharField(
_("organisation"), max_length=254, blank=True, null=True
)
bio = models.TextField(_("bio"), blank=True, null=True)
visible = models.BooleanField(_("visible"), default=False)
location = models.CharField(_("location"), max_length=254, blank=True, null=True)
photo = models.ImageField(
_("photo"), blank=True, null=True, max_length=200, upload_to="photos"
)
# let users tag themselves, to show up in searches to find folks with similar interests / skills
tags = TaggableManager(blank=True)
# short_id is a unique identifier for a profile, used in the URL
short_id = ShortUUIDField(length=8, unique=True, blank=True, null=True)
# for tracking where this profile was imported from
import_id = models.CharField(
_("import_code"), max_length=254, blank=True, null=True
)
# we have these as cache columns because when we use object storage to
# store photos end up making loads of requests to AWS
# just to return an url
_photo_url = models.URLField(blank=True, null=True)
_photo_thumbnail_url = models.URLField(blank=True, null=True)
_photo_detail_url = models.URLField(blank=True, null=True)
This model has two property methods like so, for convenience:
@property
def thumbnail_photo(self):
if not self.photo:
return None
if self._photo_thumbnail_url:
return self._photo_thumbnail_url
return get_thumbnail(self.photo, "100x100", crop="center", quality=99).url
@property
def detail_photo(self):
"""
A photo, designed for showing on a page, when viewing a profile,
with a user's details
"""
if not self.photo:
return None
if self._photo_detail_url:
return self._photo_detail_url
return get_thumbnail(self.photo, "250x250", crop="center", quality=99).url
In this case here, get_thumbnail is the method imported like this from the sorl library:
from sorl.thumbnail import get_thumbnail
The get_thumbnail method is documented on read the docs.
With me so far? Now for the view - I have a function based view which populates the view context with a bunch of profiles, using a method called fetch_profile_list.
The code looks like this:
def fetch_profile_list(request: HttpRequest, ctx: dict):
"""
Fetch the list of profiles for the given set of params, and
populate the provided context dictionary
"""
filtered_profiles = ProfileFilter(
request.GET, queryset=Profile.objects.all().prefetch_related("tags")
)
ctx["profile_filter"] = filtered_profiles
pager = paginator.Paginator(filtered_profiles.qs, NO_PROFILES_PER_PAGE)
page = request.GET.get("page", 1)
try:
ctx["paginated_profiles"] = pager.page(page)
except paginator.PageNotAnInteger:
ctx["paginated_profiles"] = pager.page(1)
except paginator.EmptyPage:
ctx["paginated_profiles"] = pager.page(paginator.num_pages)
return ctx
@login_required
def homepage(request):
current_site = get_current_site(request)
logger.info(f"{current_site=}")
ctx = {"is_authenticated": request.user.is_authenticated, "site": current_site}
ctx = fetch_profile_list(request, ctx)
if request.htmx:
template_name = "pages/_home_partial.html"
else:
template_name = "pages/home.html"
return render(request, template_name, ctx)
Finally, in the template hierarchy, we end up calling this template where we iterate through a list of profiles on a page:
{% for profile in paginated_profiles %}
<div>
<div>
<div class="profile-thumb">
{% if profile.photo %}
<img class="profile-thumb" src="{{ profile.thumbnail_photo }}" />
{% else %}
{% gravatar profile.user.email 150 %}
{% endif %}
</div>
<div>
<p>
{% for tag in profile.tags.all %}
<span class="badge">{{ tag.name }}</span>
{% endfor %}
</p>
</div>
</div>
</div>
{% endfor %}
How do I get rid of the N+1 query when fetching the photos?
I think this line with is the problem, causing a N+1 query:
<img class="profile-thumb" src="{{ profile.thumbnail_photo }}" />`
Sentry at least is flagging this up as such, and I see the same tell-tale signs of the N+1 behaviour in django debug too.
The problem is, I’m not sure of the idiomatic way to get rid of N+1 queries in this scenario.
The select_related() and prefetch_related() methods don’t seem to be designed for this (they work with the ORM), and while I know it’s probably better to fetch this in one go in the view, I’m a little unsure of the approach to take.
Should I be trying to force the query to be executed in the view ahead of time for all the images, using some call to values() on a queryset, and relying on it being cached somehow?
# maybe something like this?
filtered_profiles = ProfileFilter(
request.GET, queryset=Profile.objects.all().prefetch_related("tags").values(["fields, "I", "expect_to_use"])
)
pager = paginator.Paginator(filtered_profiles.qs)
Or is there another approach that’s more idiomatic?
Thanks in advance.
For those curious the link to the unabridged project code
The project is called constellate - it’s designed to support networking and skill discovery within communities which are not entirely private, but not entirely public either, like https://climateAction.tec.
The main use case it covers right now is creating a “shared address book” for people who are in the same slack channel, allowing them to sign into the app using their slack credentials, and see other extended profiles, of members who choose to make themselves visible.
