Django: How to select a limited amount of rows for each foreign key?

Question

class Comment (models.Model):
        user = models.ForeignKey(User, related_name="comments")
        title = models.TextField(max_length=256)
        comment = models.TextField(max_length=1286)
        created = models.DateTimeField(db_index=True)

Now how do I get latest comments but limit the results to only (say 3) comments per same user?

Here is how its done in pure SQL: How to select a limited amount of rows for each foreign key?

Answer 1

I guess, this would work:

Comment.objects.filter(*[
    ~Q(id__in=user.comments.order_by('-created')[3:].values_list('id', flat=True))
    for user in User.objects.all()
])

However, it seems like neither pythonic nor efficient way to solve the problem. (Efficiency problem could be somewhat solved by using cache, but still.)

What are you trying to achieve, anyway? You could just fetch all user's comments and use only the latest three. Assuming that your comments are ordered by -created field:

{% for user in users %}
    {% for comment in user.comments.all|slice:"3" %}{{ comment }}{% endfor %}
{% endfor %}

Slicing will be converted to LIMIT clause in SQL query, so you won't be getting all the comments anyway. Of course, there would be more that one query executed to get all the comments, so using template caching can help.

Again, this may not make sense in your case. If it doesn't, it would be better if you clarify the requirements.

Raw SQL

If you know how to do what you want with raw SQL ([1] may help), then you need to just find a way to put it into extra() somehow ([2] is an example of complex extra query which may give you an idea).

[1] How to select a limited amount of rows for each foreign key?

[2] django's .extra(where= clauses are clobbered by table-renaming .filter(foo__in=... subselects