How to annotate the historical model to the QuerySet

[gabn88]

Problem Statement
We want to return on the API the a list of instances (a QuerySet), where the historical_model should be annotated and returned with only one extra query (instead of N extra queries).

Describe the solution you'd like
I tried this with a SubQuery inside an annotate:

Model.history.filter(
            id=models.OuterRef('related_id'), history_date=models.OuterRef('datetime_for_history')
        )

However, because the id of the historicalModel is not a 'real' ForeignKey, the next call to prefetch the instances fails:

self.annotate(
            model_history_id=models.Subquery(historical_model.values('history_id')[:1])
        ).prefetch_related(
            models.Prefetch('model_history_id', queryset=Model.history.all(), to_attr='versioned_related_field')
        )

Describe alternatives you've considered
If there are ways to do this I'd love to learn.

[a-gerhard]

There are already some discussions in #407 – However, I found none of them satisfying.

There should be an easy way to annotate the history in a View's queryset, especially when this needs to be done on a list. In our case, we have an API with a ListView that returns pages of 100 objects, and for each history entry we need to resolve a foreign key (which is stored as the object ID in the history). The only point where we have easy access to the historical queryset is the serializer field's to_representation() method, where we get a queryset instead of actual data. This is not really following django conventions, and I think this is the actual issue.

My suggestion would be to provide a Mixin for Views which can include the historical queryset into the View's normal queryset, where users can then annotate the queryset as usual, and where data is given to the history field as actual data. Maybe, in addition to this, also provide a specialised field type.

[a-gerhard]

@gabn88 to answer your question: our solution to your problem was to write a custom field type to use in our serializer, and annotate the queryset in that field's to_representation() method immediately before converting executing the queryset and returning the actual data representation. However, this is not ideal, as it adds a lot of queries to the overall process, especially with lists.

[gabn88]

I'm using this (https://schinckel.net/2019/07/30/subquery-and-subclasses/) and then annotate the required fields as json. That works fine for read-only fields.

Actual code used:

        historic_customer = Subquery(
            Contact.history.filter(history_id=OuterRef('customer_id'), history_date__lte=OuterRef('finalized_on'))
            .order_by('-history_date')
            .annotate(json=JSONObject(first_name='first_name', last_name='last_name'))[:1]
            .values('json')
        )