Listing users with their last article with one SQL request on Django

user_13511749 0
Viewed 120
class User(models.Model):
    name = models.CharField(max_length=255)

class Article(models.Model):
    user = models.ForeignKey(User)
    content = models.TextField()
    created_at = models.DateTimeField(auto_now_add=True)

I need to list all users with their last article. How can I get them with one database request?

# Something like:
{% for user in users %}:
    {{ user.full_name }}: {{ user.last_message }}
{% endfor %}
  • John Doe: Lorem ipsum dolor sit amet, consectetur adipiscing elit.
  • Jake Carr: Lorem ipsum dolor sit amet, consectetur adipiscing elit.
  • Malcolm Gordon: Lorem ipsum dolor sit amet, consectetur adipiscing elit.
  • Ingredia Ameter: Lorem ipsum dolor sit amet, consectetur adipiscing elit.
django-queryset django-orm django
1 Answers

here is your query:

from django.db.models import OuterRef, Subquery


latest = Article.objects.filter(
    user=OuterRef('pk')
).order_by('-created_at')

users = User.objects.annotate(
    last_message=Subquery(latest.values("content")[:1])
)

for user in users:
    print(user.username, user.last_message)

it give you users with their last Articles.

P.s: it will execute only one query.

user_114878720
Related