How can I output a selection of Blog objects in a ListView in Django searched via ForeignKeys of two other different models?

Question

I have three models like this:

1:

class Blog(models.Model):
    title = models.CharField()
    published = models.DateField()
    ...

2:

class AuthorList(models.Model):
    blog = models.ForeignKey(Blog)
    author = models.ForeignKey(User)
    lastTimeEdited = models.DateTimeField()
    ...

3:

class CommentsList(models.Model):
    blog = models.ForeignKey(Blog)
    commentAuthor = models.ForeignKey(User)
    commentPosted = models.DateTimeField()
    ...

My class based ListView looks like this:

class DashboardView(LoginRequiredMixin, ListView):
    model = Blog
    template_name = 'app/dashboard.html'
    paginate_by = 5

    def get_queryset(self) -> QuerySet[Blog]:
        user = self.request.user
        author = #HERE I want a queryset of all blogs, where the requested user is one of the authors.
        commentator = #HERE I want a queryset of all blogs, where the requested user is one of the commentators.
        blogs = author|commentator
        return blogs.order_by(#HERE I want to order them by the lastTimeEdited-Field or the commentPosted-Field)

So I want to list all the blogs that the user has to do with in one way or another. I also want to order them by the lastTimeEdited value if the user is an author or by the commentPosted value if the user is a commenter. How can I do that?

I've already looked for the solution in other posts, but couldn't find the right solution for my problem.

Answer 1

Your AuthorList model acts as a junction table for a many-to-many relation between Blog and User. You can span a ManyToManyField ^[Django-doc] on the Blog model with:

class Blog(models.Model):
    # …
    authors = models.ManyToManyField(
        settings.AUTH_USER_MODEL,
        through='AuthorList',
        related_name='authored_blogs',
    )
    commenters = models.ManyToManyField(
        settings.AUTH_USER_MODEL,
        through='CommentsList',
        related_name='commented_blogs',
    )

This makes querying a lot easier, since we can work with:

from django.db.models import Q


class DashboardView(LoginRequiredMixin, ListView):
    model = Blog
    template_name = 'app/dashboard.html'
    paginate_by = 5

    def get_queryset(self) -> QuerySet[Blog]:
        return Blog.objects.filter(
            Q(authors=request.user) | Q(commenters=request.user)
        ).order_by(???)

The only problem now is how to order, we can determine the last author and/or comment time with .annotate(…) ^[Django-doc] and then use this:

from django.db.models import Greatest, Max, Q


class DashboardView(LoginRequiredMixin, ListView):
    model = Blog
    template_name = 'app/dashboard.html'
    paginate_by = 5

    def get_queryset(self) -> QuerySet[Blog]:
        return (
            Blog.objects.filter(
                Q(authors=request.user) | Q(commenters=request.user)
            )
            .annotate(
                latest_edit=Greatest(
                    Max('authorlist__lastTimeEdited'),
                    Max('commentslist__lastTimeEdited'),
                )
            )
            .order_by('-latest_edit')
        )

For databases like SQLite, Oracle and MySQL, this will not be sufficient, since if there the user is not an author, or a commenter, it will return NULL, only if you are both author and commenter, that will work. We can fix this in a very ugly way with Coalesce ^[Django-doc]:

from django.db.models import Coalesce, Greatest, Max, Q


class DashboardView(LoginRequiredMixin, ListView):
    model = Blog
    template_name = 'app/dashboard.html'
    paginate_by = 5

    def get_queryset(self) -> QuerySet[Blog]:
        return (
            Blog.objects.filter(
                Q(authors=request.user) | Q(commenters=request.user)
            )
            .annotate(
                latest_edit=Greatest(
                    Coalesce(
                        Max('authorlist__lastTimeEdited'),
                        Max('commentslist__lastTimeEdited'),
                    ),
                    Coalesce(
                        Max('commentslist__lastTimeEdited'),
                        Max('authorlist__lastTimeEdited'),
                    ),
                )
            )
            .order_by('-latest_edit')
        )

---

Note: It is normally better to make use of the settings.AUTH_USER_MODEL ^[Django-doc] to refer to the user model, than to use the User model ^[Django-doc] directly. For more information you can see the referencing the User model section of the documentation ^[Django-doc].