Create a list view with group by field name and link to details

So I have the following the following table:

class IncomeStatementQuarterly(models.Model):
    date = models.DateField()
    statement = models.CharField(max_length=1000, blank=True, null=True)
    ticker = models.CharField(max_length=1000, blank=True, null=True)
    security = models.ForeignKey(SecuritiesTable, models.DO_NOTHING)
    line_item = models.CharField(max_length=1000, blank=True, null=True)
    amount = models.DecimalField(max_digits=65535, decimal_places=4, blank=True, null=True)

    class Meta:
        ordering=('ticker',)
        verbose_name = "Income statement / Quarterly"
        verbose_name_plural = "Income statements / Quarterly"
        managed = False
        db_table = 'income_statement_quarterly'
        unique_together = (('date', 'ticker', 'line_item'),)

and the following in my admin.py class:

@admin.register(IncomeStatementQuarterly)
class IncomeStatementQuarterlyAdmin(admin.ModelAdmin):
    
    date_hierarchy = 'date'
    list_filter = ('line_item',)
    list_display = ("ticker", "date", "statement", "line_item", 'amount')
    search_fields = ['ticker']
    list_display_links = ('ticker',)


    def has_add_permission(self, request, obj=None):
        return False

    def has_delete_permission(self, request, obj=None):
        return False

    def has_change_permission(self, request, obj=None) -> bool:
        return False

My goal is to create a view grouped by the 'ticker' and 'date' field. As of right now, my admin view is displaying each rows of my model like so:

I want to regroup everything by ticker and date so that I'll have a link that if clicked on, I'll have all the rows based on the given combination of date and ticker.

Is this possible?

I've been looking everywhere for the past 5 days and I was getting ready to start a new model called statements_list consisting of all the unique combinations of the fields (ticker, date) of my current model that would have a primary key linking it to the unique combination of the current's model (date,ticker) fields

I hope this is not too confusing... Basically the finally result would look something like this (based on what's available on the picture):

A | Jan 31, 2005 ---> link to all statements with these parameters
A | Apr 30, 2005 ---> link to all statements with these parameters

CodePudding user response：

You can implement this by overriding get_queryset() method of ModelAdmin class and use group_by query.

queryset = IncomeStatementQuarterly.objects.values('date', 'ticker').annotate(max_id=Max('id'))

In mysql raw query like,

SELECT date, ticker, MAX(id) AS "max_id" 
FROM incomestatementquarterly 
GROUP BY date, ticker

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.

Another Solution:

One another way you can get same data,

Override get_queryset() method of ModelAdmin class

Your Admin class ,

@admin.register(IncomeStatementQuarterly)
class IncomeStatementQuarterlyAdmin(admin.ModelAdmin):
    
    list_display = ("ticker", "date", "view_details")
    ....
    ....
    def get_queryset(self, request):
        max_ids_subquery = IncomeStatementQuarterly.objects.values('date', 'ticker').annotate(max_id=Max('id')).values('max_id')

        queryset = IncomeStatementQuarterly.objects.filter(id__in=max_ids_subquery)

        return queryset

    def view_details(self, obj):
        date = obj.date
        next_date = date   datetime.timedelta(days=1)
        url = ("%s?ticker=%s&date__gte=%s&date__lt=%s") % (reverse('admin:your_app_incomestatementquarterlyproxy_changelist'),obj.ticker, date, next_date)
        return format_html('<a class="button" href="{}">View</a>',url)

In mysql query simiral to,

SELECT * FROM incomestatementquarterly 
WHERE id IN ( SELECT MAX(id) AS "max_id" FROM incomestatementquarterly t1 GROUP BY t1.date, t1.ticker)

you create a proxy model and register this model, on click of view details you redirect to this proxy model admin class which is exact your model admin class.

class IncomeStatementQuarterlyProxy(IncomeStatementQuarterly):

    class Meta:
        proxy = True

proxy model admin class

@admin.register(IncomeStatementQuarterlyProxy)
class IncomeStatementQuarterlyProxyAdmin(admin.ModelAdmin):
    
    date_hierarchy = 'date'
    list_filter = ('line_item',)
    list_display = ("ticker", "date", "statement", "line_item", 'amount')
    search_fields = ['ticker']
    list_display_links = ('ticker',)


    def has_add_permission(self, request, obj=None):
        return False

    def has_delete_permission(self, request, obj=None):
        return False

    def has_change_permission(self, request, obj=None) -> bool:
        return False