If you’ve seen that line before, you know the vibe: everything looks fine until it isn’t. I hit this while wiring up comments on a Django forum and thought I’d document the root cause and the clean, production-friendly fix I shipped.
The Setup (and the Headache)
I wanted each Post to render with its comments. I tried to “pack” the comments into an annotate() call big mistake.
The original view (exactly as I wrote it)
def index(request):
# check if there is any incomming data (comments) and make sure the user is authenticated
POST = request.POST
if POST != {} and request.user.is_authenticated:
# a comment has been recived, time to move forward with creating it
# figure out if the post even exists
get_object_or_404(Post, id = POST['post_id'])
# grab the user object
user_active = User.objects.get(id = POST['user'])
# grab the post object
post_active = Post.objects.get(id = POST['post_id'])
# make and save the comment
n_comment = Comment(user = user_active, comment = POST['comment'], post = post_active)
n_comment.save()
posts = Post.objects.order_by("-published_date").annotate(
num_likes=Count("likes"),
com=Comment.objects.order_by("-published_date")
)
return render(request, 'home.html', {'posts': posts})
The model
class Comment(models.Model):
comment = models.CharField(max_length=500)
post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name='comments')
user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='comments')
published_date = models.DateTimeField('date_published')
The template hiccup
I also had a tiny typo that made debugging more confusing:
<li><span>{{ u ser.username }}</span>…which of course should be:
<li><span>{{ user.username }}</span></li>Why the Error Happen
annotate() must produce single values per row things like Count(...), Sum(...), Exists(...), or a Subquery that returns exactly one column (and usually one row via [:1]).
I passed a whole queryset into annotate():
.annotate(
num_likes=Count("likes"),
com=Comment.objects.order_by("-published_date") # ❌ not a scalar expression
)
Django tried to treat that queryset as a subquery, but because it returns multiple columns/rows, the database balked with:
ProgrammingError: subquery must return only one column
Rule of thumb I now follow
- If I need a single scalar (count, latest value, boolean flag), I use
Count,Exists, orSubquery(...values('field')[:1]). - If I need related objects (e.g., comment objects), I use
prefetch_related(optionally withPrefetchto control ordering andto_attr).
The Right Patterns
Show all comments per post
Use prefetch_related (not annotate) to fetch related records efficiently:
from django.db.models import Count, Prefetch
posts = (
Post.objects.order_by("-published_date")
.annotate(num_likes=Count("likes"))
.prefetch_related(
Prefetch(
"comments",
queryset=Comment.objects.order_by("-published_date"),
to_attr="ordered_comments", # access as post.ordered_comments
)
)
)
Template:
{% for post in posts %}
<h2>{{ post.title }}</h2>
<div>Likes: {{ post.num_likes }}</div>
<ul>
{% for c in post.ordered_comments %}
<li>
<strong>{{ c.user.username }}</strong> — {{ c.comment }}
<small>{{ c.published_date }}</small>
</li>
{% empty %}
<li>No comments yet.</li>
{% endfor %}
</ul>
{% endfor %}
Get one scalar
Use Subquery + OuterRef and slice to a single row:
from django.db.models import OuterRef, Subquery, Count
latest_comment_qs = (
Comment.objects
.filter(post=OuterRef("pk"))
.order_by("-published_date")
)
posts = (
Post.objects.order_by("-published_date")
.annotate(
num_likes=Count("likes"),
latest_comment=Subquery(latest_comment_qs.values("comment")[:1]),
latest_commented_at=Subquery(latest_comment_qs.values("published_date")[:1]),
)
)
Now each Post has latest_comment and latest_commented_at as scalar fields.
What I Shipped A Practical, Production Ready View
Along the way I also fixed a few real-world concerns:
- Use
request.method == "POST"instead of checkingPOST != {}. - Don’t trust
POST['user']; userequest.userto prevent spoofing. - Validate the input, show friendly messages, and redirect after POST (PRG pattern).
- Add pagination.
- Keep
prefetch_relatedfor ordered comments +Countfor likes. - Fix the template typo.
Final view (core/views.py)
from django.contrib import messages
from django.core.paginator import Paginator
from django.db.models import Count, Prefetch
from django.shortcuts import get_object_or_404, render, redirect
def index(request):
# Handle creation of a new comment safely
if request.method == "POST":
if not request.user.is_authenticated:
messages.error(request, "You must be logged in to comment.")
return redirect("home")
post_id = request.POST.get("post_id")
text = request.POST.get("comment", "").strip()
post = get_object_or_404(Post, id=post_id)
if not text:
messages.error(request, "Comment cannot be empty.")
return redirect("home")
Comment.objects.create(
user=request.user, # ← do not trust POST['user']
post=post,
comment=text,
)
messages.success(request, "Comment added!")
return redirect("home")
# Query posts with useful aggregates and prefetches
posts_qs = (
Post.objects.order_by("-published_date")
.annotate(num_likes=Count("likes"))
.prefetch_related(
Prefetch(
"comments",
queryset=Comment.objects.select_related("user")
.order_by("-published_date"),
to_attr="ordered_comments",
)
)
)
# Pagination
paginator = Paginator(posts_qs, 10) # 10 posts per page
page_number = request.GET.get("page")
page_obj = paginator.get_page(page_number)
return render(request, "home.html", {"page_obj": page_obj})
Template fixes and pagination (home.html)
{# auth snippet #}
{% if user.is_authenticated %}
<li><span>{{ user.username }}</span></li>
<li><a href="{% url 'logout' %}">Log Out</a></li>
{% else %}
<li><a href="{% url 'signup' %}">Sign Up</a></li>
<li><a href="{% url 'login' %}">Log In</a></li>
{% endif %}
{# posts with comments and a simple comment form #}
{% for post in page_obj %}
<article>
<h2>{{ post.title }}</h2>
<div>Likes: {{ post.num_likes }}</div>
<section>
<h4>Comments</h4>
<ul>
{% for c in post.ordered_comments %}
<li>
<strong>{{ c.user.username }}</strong> — {{ c.comment }}
<small>{{ c.published_date }}</small>
</li>
{% empty %}
<li>No comments yet.</li>
{% endfor %}
</ul>
{% if user.is_authenticated %}
<form method="post">
{% csrf_token %}
<input type="hidden" name="post_id" value="{{ post.id }}">
<textarea name="comment" rows="2" placeholder="Write a comment..."></textarea>
<button type="submit">Comment</button>
</form>
{% endif %}
</section>
</article>
{% endfor %}
<nav class="pagination">
{% if page_obj.has_previous %}
<a href="?page={{ page_obj.previous_page_number }}">Previous</a>
{% endif %}
<span>Page {{ page_obj.number }} of {{ page_obj.paginator.num_pages }}</span>
{% if page_obj.has_next %}
<a href="?page={{ page_obj.next_page_number }}">Next</a>
{% endif %}
</nav>
Extra Practice Handy Annotations I Use
Count comments per post
from django.db.models import Count
posts = Post.objects.annotate(
num_likes=Count("likes"),
num_comments=Count("comments"),
)
Flag whether the current user
from django.db.models import Exists, OuterRef
user_liked = Like.objects.filter(post=OuterRef("pk"), user=request.user)
posts = Post.objects.annotate(
user_has_liked=Exists(user_liked)
)
Template:
{% if post.user_has_liked %}You liked this{% endif %}
Pull the latest comment text/date as scalars
from django.db.models import OuterRef, Subquery
latest_qs = Comment.objects.filter(post=OuterRef("pk")).order_by("-published_date")
posts = Post.objects.annotate(
latest_comment=Subquery(latest_qs.values("comment")[:1]),
latest_commented_at=Subquery(latest_qs.values("published_date")[:1]),
)
Final Thought
The error message looked intimidating at first, but the solution turned out to be straightforward once I kept the right mental model in mind: annotate() is for scalars per row, while prefetch_related() is for lists of related objects. After applying that distinction, my pages loaded more efficiently thanks to proper prefetching, and the view logic became safer by preventing user spoofing and following clean POST handling. If you face the same issue, just match your goal to the right tool scalars go with annotate, objects go with prefetch.