invalid parameter to prefetch_related()

Question

I'm trying to do an inner join on these 3 tables using PersonScore but it can't find persontype. What am i doing wrong?

models:

class PersonScore(models.Model):
    id = models.IntegerField(primary_key=True)  # AutoField?
    person = models.ForeignKey(‘Person’)

class Person(models.Model):
    id = models.IntegerField(primary_key=True)  # AutoField?
    name = models.CharField(max_length=255)

class PersonType(models.Model):
    person = models.ForeignKey(‘Person’)
    type = models.CharField(max_length=255)

code:

PersonScore.objects.filter(person__name="Bob").prefetch_related("person__persontype")

error:

Cannot find 'persontype' on Person object, 'person__persontype' is an invalid parameter to prefetch_related()

code:

PersonScore.objects.filter(person__name="Bob").prefetch_related("person__persontype_set")

error:

KeyError: (1L,)

is there still problem ? or solved ? – Mehrdad Pedramfar Sep 06 '17 at 07:26 — Mehrdad Pedramfar, Sep 06 '17 at 07:26

score 15 · Answer 1 · answered Nov 23 '17 at 13:47

In the PersonScore you should to use select_related instead of prefetch_related

    PersonScore.objects.filter(person__name="Bob").select_related("person__persontype")

if you want to use prefetch_related, you will use it with Person

    Person.objects.filter(personscore_set__name="Bob").prefetch_related("personscore_set"

you can check more details here: What's the difference between select_related and prefetch_related in Django ORM?

I hope that will be helpful for you.

score 0 · Answer 2 · answered Jun 06 '23 at 00:12

You can only use select_related from foreignkey side(i.e. in this case, PersonScore or PersonType) on 1:N relationship, or on OneToOneRelationship.

So,

PersonScore.objects.filter(person__name="Bob").select_related("person__persontype")

will throw FieldError: Invalid field name(s) given in select_related: 'persontype'. Choices are: (none), because you can't JOIN from Person to PersonType

On the other hand, you can use prefetch_related on N:M, 1:N, 1:1 on either side. The reason why you would use prefetch_related on 1:N or 1:1 is that even though it takes more query than select_related is that you can control the operation of prefetching using Prefetch. See the documentation.

For example, you can use ordered PersonType as your queryset to prefetch. Of course, you can do much more than this.

from django.db.models import Prefetch


PersonScore.objects.prefetch_related(
    Prefetch(
        'person__persontype_set',
        queryset=PersonType.objects.order_by('type')
    )
).filter(person__name='Bob')

Checking with django 3.2.18 and 4.2, PersonScore.objects.filter(person__name="Bob").prefetch_related("person__persontype_set") should do the job. If you are getting KeyError: (1L,) the problem may lie somewhere else.

With PersonScore.objects.filter(person__name="Bob").prefetch_related("person__persontype"), you should use "person__persontype_set" for parameter. If you want to use "person_persontype", set related_name to persontype:

class PersonScore(models.Model):
    ...
    person = models.ForeignKey(
'<app_label>.Person', related_name='persontype')

invalid parameter to prefetch_related()

2 Answers2