Optimize a Django query where it uses prefetch_related on a select_related

user_2072516 0
Viewed 237

I'm trying to figure out how to optimize a Django query. I have an queryset that contains a foreign key to exercises. That table makes ManyToMany references to a number of other tables. If selecting exercises, I'd use prefetch_related to include them in the query, but I can't figure out how to do that in this case, since I'm doing it from a different table. Currently, it's being selected as

super().get_queryset(request).select_related('exercise').prefetch_related('exercise__equipments', 'exercise__injuries')

But I can't figure out how to attach prefetch_related to that. Looking at SO, I found a solution that led me to this:

return super().get_queryset(request).select_related(
    Prefetch(
        'exercise',
        queryset=Exercise.objects.prefetch_related('equipments', 'inuries')
    )
)

But it fails with 'Prefetch' object has no attribute 'split', which I traced through the source, and it looks like select_related can't work with Prefetch.

EDIT: More info:

  • I'm trying to optimize an admin page with a SortableStackedInline
  • The starting model is Template
class Template(SortableMixin):
    exercise = models.ForeignKey('Exercise', models.CASCADE)

class Exercise(models.Model):
    equipments = models.ManyToManyField('Equipment', blank=True, through='ExerciseEquipment')
    injuries = models.ManyToManyField('Injury', blank=True, through='ExerciseInjury')

The ultimate goal is to optimize the query from Template through Equipment/Injury

postgresql django
2 Answers

If I understand well, you want to optimize query using combination of select_related and prefetch_related.

Prefetch should be used with prefetch_related not select_related.

I'm not sure cuz I didn't test it, try this one.

- Here's code:

prefetch1 = Prefetch(
    "exercise__equipments",
    queryset=Equipment.objects.all(), ----------------------------------- [2]
    to_attr="prefetch_equipments_set"
) ----------------------------------------------------------------------- [1]

prefetch2 = Prefetch(
    "exercise__injuries",
    queryset=Injury.objects.all(),    ----------------------------------- [2]
    to_attr="prefetch_injuries_set"
) ----------------------------------------------------------------------- [1]

queryset = Template.objects.all()  -------------------------------------- [2]
queryset = queryset.select_related("exercise")  ------------------------- [3]
queryset = queryset.prefetch_related(prefetch1, prefetch2)  ------------- [4]
  • [1] - Prefetch object
  • [2] - you can add filter or order_by whatever.
  • [3] - optimize query using select_related
  • [4] - optimize query using prefetch_related, [1]

- How to use:

  • get equipments from Template(id=1)
queryset.get(id=1).exercise.prefetch_equipments_set
  • get injuries from Template(id=1)
queryset.get(id=1).exercise.prefetch_injuries_set
user_124278960

Maybe this example will help. Look how i navigate through ForeignKeys My goal is to get Resume that has SkillName="DJANGO"

class Resume(models.Model):
    User = models.ForeignKey(User, on_delete=models.CASCADE)
    Description = models.TextField(max_length=255)
    CreationDate = models.DateField(auto_now_add=True)
    

class Skill(models.Model):
    SkillName = models.CharField(max_length=50)


class ResumeSkill(models.Model):
    Resume = models.ForeignKey(Resume, on_delete=models.CASCADE)
    Skill = models.ForeignKey(Skill, on_delete=models.CASCADE)

Resume --> ResumeSkill.Skill --> Skill.SkillName

notice how django handles cammel cases

Resume.objects.filter(resumeskill__Skill__SkillName="DJANGO")
user_133053650
Related