0

I'm not sure how best to go about this. I have the following @property method on my Modal:

@property
def check_ins(self):
    return SurfCheckIn.objects.filter(surf_waiver=self)

However, I would like to return this compited property in my values() of a queryset. I thought of using a SubQuery annotation:

queryset = self.get_queryset().filter(
    performance_datetime__year=date.year,
    performance_datetime__month=date.month,
    performance_datetime__day=date.day,
    void_type='0'
).exclude(
    surf_code='SPECTATOR'
).order_by(
    'performance_datetime'
).annotate(
    surf_check_ins=SubQuery()
).values()

But I'm not sure where to take the SubQuery from here? What would be the most sensible approach to retrieve that @property inside an annotation?

Micheal J. Roberts
  • 3,735
  • 4
  • 37
  • 76
  • If you're trying to get a related field, you can just put the related field in your serializer. If you defined `surf_waiver` in your `Model` as `surf_waiver = models.ForeignKey(OtherModel, related_name='surf_checkins')`, then, in your `OtherModelSerializer` you would refer to that related field in your model like: `surf_checkins = SurfCheckinSerializer(many=True)`. Thus, you would get the related models. Anyways, the devil is in the details. You should add your full `Model`, `Serializer`, and `ViewSet` definitions so we can understand your situation better. – Ross Rogers Feb 10 '20 at 17:05
  • Hi @RossRogers - unfortunately, I do not use a serializer as the data structure needs further manipulation after the queryset extraction, i.e., it is not just a flat list of queryset Objects so I can not use `Serializer(many=True)` unfortunately. I'd like to do this property extraction within the `queryset` and add the annotated value to `.values()` – Micheal J. Roberts Feb 10 '20 at 17:06
  • Instead of `annotate` you may just want `prefetch_related` of some kind. If you're able to _not_ use `annotate` and `values`, then whatever _is_ serializing your data can just call the `OtherModel's` `checkin_ins` property, but it won't be an [N+1 SQL query issue](https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping) – Ross Rogers Feb 10 '20 at 17:14

1 Answers1

1

I think @RossRogers is on the right track. Using prefetch_related seems like it would work better than an annotation here, since you want to return all of the related objects and not some aggregate or exists check.

To leverage this, you'd do a prefetch_related and update your property to return the value from the reverse relation instead of performing a new query:

@property
def check_ins(self):
    # Use reverse relation here. This is the default name based on your model,
    # but if the foreign key has a `related_name` set, use that value instead.
    return self.surfcheckin_set.all()

Instead of an annotation, you'd prefetch the set and that will prevent a database hit per returned waiver:

# Again, replace surcheckin_set with the `related_name` if set on the foreign key for `surf_waiver`.
queryset = self.get_queryset().filter(
    performance_datetime__year=date.year,
    performance_datetime__month=date.month,
    performance_datetime__day=date.day,
    void_type='0'
).exclude(
    surf_code='SPECTATOR'
).prefetch_related(
    "surfcheckin_set"
).order_by(
    'performance_datetime'
).values()

Calling prefetch_related here means you'll perform one query for the whole queryset and relate the values back to the manager for the reverse relation. When the property calls .all() on the reverse relation, it will use this cached query result.

aredzko
  • 1,690
  • 14
  • 14
  • Hmmm, it doesn't seem to have changed anything ... I haven't got a related_name so I have gone with the defaults ... what else would I need on my modals? Do I need a custom manager to handle this? – Micheal J. Roberts Feb 11 '20 at 10:34
  • @WindUpLordVexxos You shouldn't need a custom manager. You *could* put in a related name and use it, just to make sure I didn't provide the wrong reverse relational name for the manager. I also just realized that it may also be the call to `values`, which would [return a list of dictionaries containing the model's fields and their values](https://docs.djangoproject.com/en/3.0/ref/models/querysets/#django.db.models.query.QuerySet.values) instead of model instances. That would explain why that field would not show up in the provided dictionary. How are you using the data that comes back? – aredzko Feb 12 '20 at 02:00