Create a Django custom Transform from an annotation

I've got an annotation for a foreign key field that I use frequently and would like to write a custom Django Transform which behaves similar to the annotation. The annotation that does what I would like is in this snippet:

queryset = Event.objects.annotate(num_visits=Count("pageinfo__newpageview") + Sum(F("pageinfo__newpageview__timestamp_seconds__len")))

number_of_events_with_several_visits = queryset.filter(num_visits__gt=10).count()
number_of_events_with_many_visits = queryset.filter(num_visits__gt=100).count()
event_with_most_visits = queryset.order_by('-num_visits').first()

I'd like to turn this annotation into a custom Transform so I can use it in multiple situations without having to repeat the annotation. The framework of the Transform would be:

class NumVisitsTransform(models.lookups.Transform):
    lookup_name = "num_visits"
    # Some implementation goes here

page_info_field = Events._meta.get_field("pageinfo").field
page_info_field.register_lookup(NumVisitsTransform)

My goal with this Transform is to enable then re-writing the examples above, without respecifying the annotation to be:

number_of_events_with_several_visits = queryset.filter(pageinfo__num_visits__gt=10).count()
number_of_events_with_many_visits = queryset.filter(pageinfo__num_visits__gt=100).count()
event_with_most_visits = queryset.order_by('-pageinfo__num_visits').first()

Is there a straight-forward way to write the implementation of the Transform using the annotation and above building it up from the expressions in the annotation? Even a simpler example building a Transform using sub-expressions would be useful.

I tried to implement a template for the SQL in the Transform but this was quickly getting beyond my capability in SQL. I was hoping there might be a way to build this up without creating SQL that might handle all the variations where the annotation could be used.

Вернуться на верх