CODEWITHSUNDEEP
django
OmriToptix
OmriToptix

Reputation: 1309

Django avoid extra queries using prefetch_related not working

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.

Upvotes: 5

Views: 6865

Answers (1)

Alasdair
Alasdair

Reputation: 309039

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

Upvotes: 10

Related Questions