Fix N+1 Query on Django Community page

2 files changed, 24 insertions, 7 deletions

diff --git a/aggregator/urls.py b/aggregator/urls.py
index 2dbe6c57..c8cec9d5 100644
--- a/aggregator/urls.py
+++ b/aggregator/urls.py
@@ -5,7 +5,7 @@ from . import views
 urlpatterns = [
     path(
         '',
-        views.index,
+        views.IndexView.as_view(),
         name='community-index'
     ),
     path(
diff --git a/aggregator/views.py b/aggregator/views.py
index f1991041..c4ebae3e 100644
--- a/aggregator/views.py
+++ b/aggregator/views.py
@@ -1,21 +1,38 @@
 from django.contrib import messages
 from django.contrib.auth.decorators import login_required
+from django.contrib.postgres.aggregates import ArrayAgg
 from django.shortcuts import get_object_or_404, redirect, render
+from django.views.generic import TemplateView
 from django.views.generic.list import ListView
 
 from .forms import FeedModelForm
 from .models import APPROVED_FEED, Feed, FeedItem, FeedType
 
 
-def index(request):
+class IndexView(TemplateView):
     """
     Displays the latest feeds of each type.
     """
-    feeds = []
-    for ft in FeedType.objects.all():
-        feeds.append((ft, ft.items()[0:5]))
-    ctx = {'feedtype_list': feeds}
-    return render(request, 'aggregator/index.html', ctx)
+    template_name = 'aggregator/index.html'
+
+    def get_context_data(self, **kwargs):
+        context = super().get_context_data(**kwargs)
+        # Get the latest feed item ids for each feed type
+        feed_item_ids = FeedType.objects.annotate(
+            feed_item_ids=ArrayAgg(
+                'feed__feeditem__id',
+                distinct=True,
+            )
+        ).values_list('feed_item_ids', flat=True)
+        # Merge the lists of feed item ids and get the latest 5 for each feed type
+        cleaned_ids = [
+            item for sublist in feed_item_ids for item in sublist[:5] if item
+        ]
+        context['feed_items'] = FeedItem.objects.filter(
+            id__in=cleaned_ids
+        ).select_related('feed__feed_type')
+
+        return context
 
 
 class FeedListView(ListView):