GROUP By in Django ORM for page in Django Admin-CodePudding

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()).

CodePudding user response：

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

CodePudding user response：

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.