Optimize a Django query where it uses prefetch_related on a select_related

Question

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

can you provide a models.py

bazzuk123
– bazzuk123

2021-05-12 23:17:36 +00:00
Commented May 12, 2021 at 23:17 — bazzuk123
– bazzuk123, Commented May 12, 2021 at 23:17
@bazzuk123 Added them.

Rohit
– Rohit

2021-05-12 23:25:40 +00:00
Commented May 12, 2021 at 23:25 — Rohit
– Rohit, Commented May 12, 2021 at 23:25

jake_kil · Accepted Answer · 2021-05-13 07:31:58Z

1

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

answered May 13, 2021 at 7:31

jake_kil

556 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rohit Over a year ago

So unfortunately, it didn't work, but it may also be because of how it's being used. We're not calling the queryset directly, rather it's being used in an admin page, so being called for us. I removed the to_attr so it'd map to the base attr, but still as many queries as before :/

jake_kil Over a year ago

Which data you want to get, ultimately?

Rohit Over a year ago

I want to optimize the query that ends up being able to get all the data in a single query. Right now, as it's an admin page, makes dozens of calls, which obviously takes time.

bazzuk123 · Accepted Answer · 2021-05-12 23:32:25Z

0

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

answered May 12, 2021 at 23:32

bazzuk123

1911 silver badge10 bronze badges

1 Comment

Rohit Over a year ago

This isn't about filtering; I know how to do that. I'm trying to figure out how to optimize the query. Usually it'd be with select_related or prefetch_related, but the camel case doesn't work here for prefetch_related. I'll update the question again.

Collectives™ on Stack Overflow

Optimize a Django query where it uses prefetch_related on a select_related

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related