Django avoid extra queries using prefetch_related not working

Question

I've worked previously with select_related and prefetch_related and it worked great.

I'm working on a current project, and for some reason I can't figure out why my prefetch related query is not working right, hence I get a lot of redundant DB calls.

My models:

class User(models.Model):
    user_extra_info = models.ManyToManyField(
        AppGeneralData,
        through='UserExtraInfo',
        null=True,
        blank=True
    )

class AppGeneralData(models.Model):
    title = models.CharField(max_length=255)
    type = models.PositiveSmallIntegerField(
        choices=GENERAL_DATA_TYPE
    )

class UserExtraInfo(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL)
    info_item = models.ForeignKey(AppGeneralData)

The QuerySet:

User.objects.all().prefetch_related(
    Prefetch(
        'userextrainfo_set',
        queryset=UserExtraInfo.objects.select_related('info_item').all()
    )
)

The problem:

When iterating over the queryset and calling a sub-query, it doesn't fetch it from the cached queryset:

for user in qs:
    user.userextrainfo_set.filter(
        info_item__type=general_data_type
    ).values_list(
        'info_item__title', flat=True
    ))

The sub-query just goes to the DB on every call and I don't understand what I am missing.

Thanks.

Alasdair · Accepted Answer · 2017-08-08T17:16:37.087

8

When you call filter(), this creates a different queryset, so Django can't use the data from prefetch_related.

You can move the filter into the queryset for the Prefetch object.

qs = User.objects.all().prefetch_related(
    Prefetch('userextrainfo_set', queryset=UserExtraInfo.objects.filter(
        info_item__type=general_data_type
    ).select_related('info_item'), to_attr='general_userextrainfo')
)

then don't use filter() when accessing the related items in the queryset.

for user in qs:
    user.general_userextrainfo.values_list('info_item__title', flat=True))

edited Aug 08 '17 at 17:16

answered Nov 05 '15 at 17:44

Alasdair

298,606
55
578
516

You are right that 'filter' re-evaluates the query, but running user.extrainfo_set.values_list('info_item__title', flat=True) also re-evalutes it for me. only user.userextrainfo_set.all() or user.userextrainfo_set.all().exists doesn't re-evaluate. It seems like 'values_list' also re-evaluates the query - at least from what it seems in my tests. Also, using the filter in the prefetch_related doesn't really help me, because I need to filter based on a value which changes inside the loop constantly. Is there anyway I can 'filter' the user without this being re-evaluated? – OmriToptix Nov 05 '15 at 22:05
You could do the filtering and conversion to a list of values in Python, e.g. `[info.info_item.title for info in user.userextrainfo_set.all() if info.info_item.type == general_data_type]`. – Alasdair Nov 05 '15 at 22:29
Yes ,saw this solution in this SO Post: http://stackoverflow.com/questions/12973929/why-does-djangos-prefetch-related-only-work-with-all-and-not-filter and ended up using it. I will accept your answer, though the 'values_list' did re-evaluates the query in my tests. – OmriToptix Nov 06 '15 at 07:31
You could probably avoid processing in python (which can be quite expensive). Loop through your `general_data_type`s (I assume this is what's changing in each loop iteration) and do a prefetch for each one, assigning it to a different attribute on your user object using the `to_attr` kwarg. This will give you a database query for each `general_data_type`, but should still be quicker than python processing, depending on what your data looks like of course. I can write this up as an answer if it addressed your concerns, though the question could use some clarification too in that case. – DylanYoung Aug 08 '17 at 17:04

Django avoid extra queries using prefetch_related not working

1 Answers1