prefetch to_attr traversing two models

Using Django Using the ORM
1

Hi,

I have 3 models like below and I want to get all Events and Parameters related to each event through the middle model ParameterSet. This is similar to the vegetarian_pizza example in the documentation, but I still have trouble making it work.

from django.db import models

class EventVersion(models.Model):
    ...  # other fields
    

class ParameterSet(models.Model):
    event = models.ForeignKey("event.EventVersion", related_name="parametersets", on_delete=models.CASCADE)
    preferred = models.BooleanField(default=False)
    ...  # other fields


class Parameter(models.Model):
    name = models.CharField(max_length=255) 
    parameterset = models.ForeignKey(ParameterSet, default=1, on_delete=models.CASCADE)
    ...  # other fields

Naively, this is what I want to do:

events = EventVersion.objects.all()
for event in events:
    default_parameters = Parameter.objects.filter(
        parameterset__preferred=True,
        parameterset__event=event
    )
    for param in default_parameters:
        # do some work, no more DB hits

This will make a DB query for each event, for which there are a LOT, and it’s making the page slow. So I thought of pre-fetching all the parameters. This is what I tried:

defparams = Parameter.objects.filter(parameterset__preferred=True)
events = EventVersion.objects.prefetch_related(
    Prefetch(
        "parametersets__parameter_set",
        queryset=defparams,
        to_attr="default_parameters"
    )
)
for event in events:
    for param in event.default_parameters:
        # do some work, no more DB hits
        print(param.name)

Traceback (most recent call last):
  File "<console>", line 2, in <module>
AttributeError: 'EventVersion' object has no attribute 'default_params'

Am I doing this right? The FK relations go in the opposite way than the example in the docs, but I think it should also work.

2

to_attr is used for the leaf of the relationship passed as the first argument of Prefetch.

See Prefetch 'to_attr' attribute results in an Attribute not found error which is the first result for searching “prefetch to_attr”

3

I did find that answer when I searched for prefetch, but I don’t think it answers my question. Or at least I don’t understand the relation to my question.

How would you change my ORM query to prefetch all of the parameters related to an event, like in my example? I want to store all of them in the default_parameters attribute.

4

I assume your data model has a unique constraint on ParameterSet.event for the condition preferred=True but the ORM has not way of knowing that.

What you are asking the ORM to do when you ask for

defparams = Parameter.objects.filter(parameterset__preferred=True)
events = EventVersion.objects.prefetch_related(
    Prefetch(
        "parametersets__parameter_set",
        queryset=defparams,
        to_attr="default_parameters"
    )
)

Is to retrieve all the event versions, then retrieve all the parameter sets (even the non-preferred ones) associated to these event versions, then for all of these parameters sets retrieve all the parameters associated to them only if they are for a preferred parameter set.

There is not way to tell Prefetch(to_attr) to assign an attribute on a non-local relationship; in your case default_parameters: list[Parameter] on your returned EventVersion instances. The closest you’ll get with PrefetchRelated is the following

preferred_parametersets = ParameterSet.objects.filter(preferred=True)
events = EventVersion.objects.prefetch_related(
    Prefetch(
        "parametersets",
        preferred_parametersets.prefetch_related("parameter_set"),
    )
)
for event in events:
    # XXX: Assume an app-level unique constraint
   prefered_parameterset = next(event.parametersets.all(), None)
   default_params = list(prefered_parameterset.parameter_set.all())

Which will incur an extra query to retrieve all the preferred parameter sets.

If there was a way to declare a 1-1 conditional relation for a preferred paramsets you could do

EventVersion.objects.select_related(
    "prefered_parameterset"
).prefetch_related("prefered_parameterset__parameters")

And call it a day by accessing event.prefered_parameterset.params.all() but the ORM doesn’t allow that.

An alternative to using prefetch_related is just to do the queries yourself. After all prefetch_related is really just

events = EventVersion.objects.all()
default_parameters = Parameter.objects.filter(
    parameterset__preferred=True,
    parameterset__event__in=events,  # Not necessary if you are interested in all events
).annotate(
    event_id=F("parameterset__event_id")  # Use for the `groupby` key.
).order_by("event_id")  # `groupby` expect values to be pre-emptively sorted.
events_default_parameters = {
    event_id: list(params)
    for event_id, params in itertools.groupby(
        default_parameters, operator.attrgetter("event_id")
    )
}
for event in events:
    default_parameters = events_default_parameters.get(
        events_default_parameters, []
    )
    for param in default_parameters:
        # do some work, no more DB hits
2 Likes
5

Thanks for your thorough response. I think I understand now.

I think the fundamental problem was that the foreign key relations go “the opposite way” for them to make sense. They should be “many to one” and not “one to many” IIUC.

There is no one-to-one from Event to ParameterSet, but there is a one-to-one from Event to a preferred ParameterSet. I will consider adding a field like the “best_pizza” field in the Django docs example, even if it doesn’t solve the problem completely.