CODEWITHSUNDEEP
djangodjango-modelsdjango-admin
Adam Parkin
Adam Parkin

Reputation: 18680

Django annotate query with values from Many To Many Relationship

Following the example on Many To Many relationships for Django: https://docs.djangoproject.com/en/3.1/topics/db/examples/many_to_many/

I want to be able to display in the Django Admin a column on the Article listview which contains the title's of associated Publications. So if I have an article a1 which has publications:

Publication(title='Pub 1')
Publication(title='Pub 2')

I want to see a column in the admin listview showing "Pub 1, Pub 2". I could do this with a custom function on the Admin class:

class ArticleAdmin(admin.ModelAdmin):
    list_display = ['publication_titles']
    def publication_titles(self, obj):
        return ', '.join([pub.title for pub in obj.publications.all()])

But that's an N+1 problem: each Article row will execute a query for each associated publication. I see this in the Django debug toolbar, where I see the number of SQL queries executed go from 9 queries to 33 when rendering 24 articles in the listview.

I thought maybe I could do prefetch_related in the queryset:

class ArticleAdmin(admin.ModelAdmin):
    list_display = ['publication_titles']
    def get_queryset(self, request):
        queryset = super().get_queryset(request)
        queryset.prefetch_related('publications')
        return queryset

    def publication_titles(self, obj):
        return ', '.join([pub.title for pub in obj.publications.all()])

But that seems to have no effect on the number of SQL queries executed.

I thought I could use annotate() to annotate the queryset used in the admin, but I'm struggling to figure out how to do that. Like I want to do something like:

class ArticleAdmin(admin.ModelAdmin):
    list_display = ['publication_titles']
    def get_queryset(self, request):
        queryset = super().get_queryset(request)
        queryset.annotate(publication_titles=<HOW DO I GENERATE THE COLLECTION OF TITLES>)
        return queryset

But I'm not grokking what I'd put in place for <HOW DO I GENERATE THE COLLECTION OF TITLES>.

How can I get the list of Publications displayed without spiking the number of queries to the DB?

Upvotes: 1

Views: 398

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476659

QuerySets are immutable, this thus means that .prefetch_related creates a new QuerySet, you thus work with:

class ArticleAdmin(admin.ModelAdmin):
    list_display = ['publication_titles']
    
    def get_queryset(self, request):
        return super().get_queryset(request).prefetch_related('publications')

    def publication_titles(self, obj):
        return ', '.join([pub.title for pub in obj.publications.all()])

Upvotes: 1

Related Questions