#Trouble figuring out how to optimize this query

33 messages · Page 1 of 1 (latest)

unreal carbon
#

hi! I have a view which loops over a list of battles and gets the battle.player of each battle, which looks like this

@property
def player(self):
    return Player.objects.filter(team__battle=self, is_self=True) \
        .select_related('title_adjective__string') \
        .select_related('title_subject__string') \
        .select_related('nameplate_background__image') \
        .select_related('nameplate_badge_1__image') \
        .select_related('nameplate_badge_2__image') \
        .select_related('nameplate_badge_3__image') \
        .select_related('weapon__name') \
        .select_related('weapon__flat_image') \
        .select_related('weapon__sub__name') \
        .select_related('weapon__sub__overlay_image') \
        .select_related('weapon__sub__mask_image') \
        .select_related('weapon__special__name') \
        .select_related('weapon__special__overlay_image') \
        .select_related('weapon__special__mask_image') \
        .get()

This has lead to an n+1 issue but I'm not sure how to solve it. Adding a prefetch_related to the initial query to get the lists of battles with something like prefetch_related('teams__players__<each of the fields above>') seems to just make the problem even worse. Is there a way to optimize this?

warm dragon
#

Doesn't look like that's all FKs

#

also you know you can write select_related("title", "weapon", "somethig_else", ...) ?

unreal carbon
unreal carbon
warm dragon
#

__string is a FK and __image is a FK?
Then I question your DB structure

unreal carbon
#

they are. i store extra data with every image and strings contain the translation strings for every language (i pull translation strings from an external source)

warm dragon
#

That would be even worse

unreal carbon
#

how so? think it makes sense to me.

warm dragon
#

It's a major pain to query

#

When I made translatable models, I went other way around starting with the translation model and joining to it

#

But anyway, here I can't help I fear.

unreal carbon
hidden umbra
#

It's easier to help if you show us your models.

#

My first shot would be something like Battle.objects.select_related("team__player")?

unreal carbon
hidden umbra
#

Which player is the query supposed to return if there can be more than one?

unreal carbon
unreal carbon
#

I feel like maybe I'm not really understanding how to do complex query stuff with the ORM 😅

#

but is the solution to maybe like, instead of iterating over some battles and grabbing the is_self player, to instead iterate over players where is_self is True and then grab the battle from there?

hidden umbra
#

I think you should be able to solve this with a custom Prefetch object where you pass a queryset that filters the players according to is_self.

unreal carbon
#

ah, saw the Prefetch objects a while back in the docs but wasn't sure how exactly to use them or when they were even necessary.

hidden umbra
#

I don't remember the exact details, but very roughly, something like battle = Battle.objects.prefetch_related(Prefetch(queryset=Player.objects.filter("is_self=True")).first(); player=battle.player_set[0] .

unreal carbon
#

okii, I'll try that, thanks!

#

i think for the Prefetch constructor i'd wanna pass teams__players as the lookup?

hidden umbra
#

Probably, I don't remember.

#

Check the docs and try a few things 😉

unreal carbon
#

mhm, currently just going through the debug toolbar's sql menu and i think i might've gotten it working?

#

once i figure it out for sure i'll try to share my findings here for anyone who comes along this thread though

unreal carbon
#

this is what I have atm
Battle.objects.prefetch_related(Prefetch('teams__players', queryset=Player.objects.with_prefetch().filter(is_self=True), to_attr='prefetched_player'))