GROUP By in Django ORM for page in Django Admin

Question

I'm not very long in Django, sorry for the probably stupid question. But after many hours of trying to solve and a huge number of searches on the Internet, I did not find a solution.

My Models:

class Offer(models.Model):
    seller = models.ForeignKey()<..>
    # other fields

class OfferViewCount(models.Model):
    offer = models.ForeignKey(Offer, verbose_name=_('Offer'), on_delete=models.CASCADE)
    user_agent = models.CharField(verbose_name=_('User Agent'), max_length=200)
    ip_address = models.CharField(verbose_name=_('IP Address'), max_length=32)
    created_date = models.DateTimeField(auto_now_add=True)

The database of the OfferViewCount model has the following data:

id;user_agent;ip_address;created_date;offer_id
24;insomnia/2022.6.0f;127.0.0.1;2022-11-18 14:14:52.501008+00;192
25;insomnia/2022.6.0z;127.0.0.1;2022-11-18 15:03:31.471366+00;192
23;insomnia/2022.6.0;127.0.0.1;2022-11-18 14:14:49.840141+00;193
28;insomnia/2022.6.0;127.0.0.1;2022-11-18 15:04:18.867051+00;195
29;insomnia/2022.6.0;127.0.0.1;2022-11-21 11:33:15.719524+00;195
30;test;127.0.1.1;2022-11-22 19:34:39+00;195

If I use the default output in Django Admin like this:

class OfferViewCountAdmin(admin.ModelAdmin):
    list_display = ('offer',)

I get this:

Offer
offer #192
offer #192
offer #193
offer #195
offer #195
offer #195

I want to get a result like this:

Offer;Views
offer #192;2
offer #193;1
offer #195;3

Simply put, I want to display one instance of each duplicate post in the admin, and display the total number of them in a custom field. In SQL it would look something like this:

SELECT offer_id, COUNT(*) AS count FROM offer_offerviewcount GROUP BY offer_id ORDER BY COUNT DESC;

I've tried many options, including overwriting get_queryset. In general, I managed to achieve the desired result like this:

class OfferViewCountAdmin(admin.ModelAdmin):
    list_display = ('offer', 'get_views')
    list_filter = ['created_date', 'offer']
    list_per_page = 20

    def get_views(self, obj):
        return OfferViewCount.objects.filter(offer=obj.offer).count()

    def get_queryset(self, request):
        qs = OfferViewCount.objects.filter(
            ~Exists(OfferViewCount.objects.filter(
                Q(offer__lt=OuterRef('offer')) | Q(offer=OuterRef('offer'), pk__lt=OuterRef('pk')),
                offer=OuterRef('offer')
            ))
        )
        
        return qs

    get_views.short_description = _('Views')

But in this case, sorting by Views does not work. If I add it explicitly via admin_order_field for get_views, I get an error because there is no such field in the database. To avoid such an error, it is necessary to fasten the overwritten annotate queriset, something like this:

qs = OfferViewCount.objects.filter(
    ~Exists(OfferViewCount.objects.filter(
        Q(offer__lt=OuterRef('offer')) | Q(offer=OuterRef('offer'), pk__lt=OuterRef('pk')),
        offer=OuterRef('offer')
    ))
).annotate(_views_count=Count('offer'))

And change get_views to:

def get_views(self, obj):
    return obj._views_count

But in this case, Count('offer') always returns 1, probably because not the entire base is analyzed there.

Actually, tell me, please, how to add a working sorting? If there is some much simpler way (without ~Exists and constructions with Q()|Q()).

Answer 1

you should use the Django group by like below

def get_queryset(self, request):
    qs = OfferViewCount.objects.values("offer")
    .annotate(count=Count("offer")).distinct().order_by("count")
    return qs

Answer 2

You can use below queryset for group by query

from django.db.models import Count

def get_queryset(self, request):
    qs = OfferViewCount.objects.values(
            'offer'
        ).annotate(
            offer_count=Count('id')
        ).order_by("-offer_count")
    return qs

In mysql raw query like,

SELECT 
`offer_offerviewcount`.`offer_id`, 
COUNT(`offer_offerviewcount`.`id`) AS `offer_count` 
FROM `offer_offerviewcount` 
GROUP BY `offer_offerviewcount`.`offer_id` 
ORDER BY `offer_count` DESC

But if you use admin default change_list template then this query gives you an error. Because django admin when render values in template expect list of objects in queryset and group_by query return in queryset as list of dict.

If you want to use above query, then you override change_list template and rendered data itself.