Django- Display records in django-admin with group, max count and timestamp all together

Question

This is my code

models.py(App: Article)

from django.contrib.auth.models import User

class Article(models.Model):
    # code...
    url_title = models.CharField(max_length=80, unique=True, db_index=True)


HATE_SPEECH = 'HS'
SPAM = 'SP'
FAKE_INFO = 'FI'

REPORT_REASON = (
    (FAKE_INFO, 'Fake Information'),
    (HATE_SPEECH, 'Hate Speech'),
    (SPAM, 'Spam'))

class Report(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    article_id = models.ForeignKey(Article, on_delete=models.PROTECT)
    user_id = models.ForeignKey(User, on_delete=models.PROTECT)
    reason = models.CharField(max_length=2, choices=REPORT_REASON)
    solved_status = models.BooleanField(default=False)
    date_created = models.DateTimeField(auto_now_add=now)

admin.py(App: Article)

class ArticleAdmin(admin.ModelAdmin):
    pass
    # code...

class ReportAdmin(admin.ModelAdmin):
    list_display = ('id', 'article_id', 'user_id', 'reason', 'solved_status', 'date_created')

admin.site.register(Article, ArticleAdmin)
admin.site.register(Report, ReportAdmin)

My database records currently looks like this:

In django admin I want to display all these records in such a manner.

1. Unsolved queries should display first (which I am able to achieve using ordering = ['solved_status'])
2. The article which is (i) not solved and (ii) reported the highest number of times should come first(Here article a1: because it is reported 3 times. Do NOT consider a2 because, in the last record, it is considered as solved
3. Highest Number of same reason from the same article (Here Hate Speech is coming 2 times, so it should come first and then spam should come) NOTE: Do not consider Spam as 4 times because we have to fulfill condition 2 first.
4. The article which is reported first should display first according to DateTime. (id: 8 was made first, then id 1) NOTE: Do not consider id 7 as oldest because we have to fulfill condition 1, 2 and 3 first.
5. If the remaining record meets none of the condition from 1, 2 and 3, the report which was made first should display first according to DateTime.

So, the final Table should look something like this:

Now, we can see that

1. Our first condition is fulfilled as solved queries are moved to last.
2. Our second condition is fulfilled as a1 is moved to the top being the article with the highest reports raised.
3. Our third condition is fulfilled because Hate speech is at the top being the reason with the highest reports raised in article a1.
4. Our fourth condition is fulfilled as Id: 8 is moved to the top because this report was made prior to id: 1 considering the DateTimeField.
5. Our fifth condition is fulfilled because id:5 and id:3 have nothing in common, but as id: 5 was prior, so it should come first.

I tried using annotate using this link which I thought could solve my partial doubt, but it is constantly giving me errors and doubt yet not being solved. I am trying this for a long time, any help is appreciable. Thanks in advance :)

Answer 1

Some window function annotations should enable the described ordering:

from django.db.models import F, Count, Window, Q

class ReportAdmin(admin.ModelAdmin):

    list_display = ('id', 'article_id', 'user_id', 'reason', 'solved_status', 'date_created',
                    'count_by_article', 'count_by_article_and_reason')

    def get_queryset(self, request):
        return super().get_queryset(request).annotate(
            count_by_article=Window(
                expression=Count('id', filter=Q(solved_status=False)),
                partition_by=F('article_id')
            ),
            count_by_article_and_reason=Window(
                expression=Count('id', filter=Q(solved_status=False)),
                partition_by=[F('article_id'), F('reason')],
            ),
            earliest_report_by_article=Window(
                expression=Min('date_created', filter=Q(solved_status=False)),
                partition_by=[F('article_id')],
            ),
            earliest_report_by_article_and_reason=Window(
                expression=Min('date_created', filter=Q(solved_status=False)),
                partition_by=[F('article_id'), F('reason')],
            ),
        ).order_by('solved_status', '-count_by_article', 'earliest_report_by_article', 'article_id',
                   '-count_by_article_and_reason', 'earliest_report_by_article_and_reason',
                   'reason', 'date_created')

    def count_by_article(self, obj):
        return obj.count_by_article

    def count_by_article_and_reason(self, obj):
        return obj.count_by_article_and_reason


admin.site.register(Report, ReportAdmin)