Applying distinct to fields

[radusuciu]

Thank you for the great extension! Please feel free to close if this is way out of scope. The use-case I have in mind is autocomplete. I already use DRF, django-filter, and drf-flex-fields and in the course of developing an autocomplete input for a datatable I have been exploring the possibility for re-using the same viewset/serializer/filterset to provide data for my autocomplete input.

For sample, say I have a Widget endpoint that I apply some filters to and get one page of results at a time. Each Widget has a User, and I want my autocomplete input to list users for the filtered set of widgets. Options:

1. Query the User endpoint. Problem: if not every User has a Widget then I'm providing options that will yield no results
2. Apply a filter to the User endpoint to restrict the results to users that have widgets. Problem: what if we have many more filters we're applying to widgets, we're still listing users that do not have widgets among the filtered set. We could of course keep adding more filters, but this isn't DRY.

So my desired implementation would re-use the same viewset/serializer/filterset, but would let me pick out an arbitrary field to return (easy with `drf-flex-fields!), and would only give me distinct values for that field.

My first attempt at this was with an autocomplete action on my endpoint:

@action(detail=False, pagination_class=None)
def autocomplete(self, request, *args, **kwargs):
    data = self.filter_queryset(self.get_queryset()).order_by('user').distinct('user')
    serializer = self.get_serializer(data, many=True)
    return Response(serializer.data)

called like so: api/widget/autocomplete?fields=user&foo=1&bar=2. However, despite not defining any annotations on my queryset or in the serializer/filterset, I hit this:

NotImplementedError at /api/proteomics/experiment/autocomplete/
annotate() + distinct(fields) is not implemented.

My second attempt looks like this:

@action(detail=False, pagination_class=None)
def autocomplete(self, request, *args, **kwargs):
    autocomplete_field = request.query_params.get('fields')
    filtered = self.filter_queryset(self.get_queryset())
    aggregated_queryset = filtered.values(autocomplete_field).annotate(max_id=Max('id'))
    distinct_queryset = filtered.filter(id__in=aggregated_queryset.values('max_id'))
    serializer = self.get_serializer(distinct_queryset, many=True)
    return Response(serializer.data)

or with a subquery:

@action(detail=False, pagination_class=None)
def autocomplete(self, request, *args, **kwargs):
    autocomplete_field = request.query_params.get('fields')
    filtered = self.filter_queryset(self.get_queryset())
    subquery = (
        filtered.filter(**{autocomplete_field: OuterRef(autocomplete_field)}).order_by('id').values('id')[:1]
    )
    distinct_queryset = filtered.filter(id__in=Subquery(subquery))
    serializer = self.get_serializer(distinct_queryset, many=True)
    return Response(serializer.data)

Now this actually works, and if you use FlexFieldsFilterBackend, the query ends up being pretty good as well.

So.. I hope I made my use-case clear, and I don't think it's too niche really. Do you think a feature like this could be implemented in drf-flex-fields itself? Perhaps we could just call api/widget/?fields=user&distinct&foo=1&bar=2.