I am using Django 3.2 and Django Rest Framework for an API. I am having difficulty getting it to work the way I would expect. I have a position table with sample data similar to this:

[
  { id: 1, position_date: '2022-01-01', symbol: 'AAPL', shares: 100 },
  { id: 2, position_date: '2022-01-01', symbol: 'TSLA', shares: 100 },
  { id: 3, position_date: '2022-01-01', symbol: 'GOOG', shares: 100 },
  { id: 4, position_date: '2022-01-02', symbol: 'AAPL', shares: 200 },
  { id: 5, position_date: '2022-01-02', symbol: 'TSLA', shares: 200 },
  { id: 6, position_date: '2022-01-02', symbol: 'GOOG', shares: 200 },
  { id: 7, position_date: '2022-01-05', symbol: 'AAPL', shares: 300 },
  { id: 8, position_date: '2022-01-05', symbol: 'TSLA', shares: 300 },
  { id: 9, position_date: '2022-01-05', symbol: 'GOOG', shares: 300 },
]

This data contains stock positions on a certain date. This daily snapshot position info is created only on weekdays/days the market is open, so there are gaps in the dates for weekends/market holidays.

I want to query positions by position_date /api/v1/positions?position_date=<YYYY-MM-DD>, with the caveat that when you pass in a date that is not directly associated with a position, then you get the positions for the greatest date that is less than or equal to the passed-in date. For example, if I passed in a date for a Sunday that wouldn't have positions associated with it, I would expect to receive the positions from Friday. In the sample data above, if I passed in a position_date of '2022-01-04', I would expect to receive the three records with a position_date of '2022-01-02'.

Below is my ViewSet. Currently, if I don't pass in a position_date, it works as expected and returns the latest positions. However, when I pass in a position_date, it only returns positions on that date. The find max_date stuff seems to get overwritten somewhere/somehow when a date gets passed in. Any ideas on how to fix this?

class PositionViewSet(viewsets.ModelViewSet):
    """
    API endpoint that allows positions to be viewed
    """
    def get_queryset(self):
        # only include params from filterset_fields list
        params = dict(self.request.GET)
        filtered_params = dict((k, params[k][0]) for k in self.filterset_fields if k in params and params[k][0])
        
        # set default value to latest positions
        if 'position_date' not in filtered_params:
            filtered_params['position_date'] = date.today().isoformat()
        # return all positions
        if 'position_date' in filtered_params and filtered_params['position_date'] == 'all':
            del filtered_params['position_date']
        # find the greatest date that is lte to the passed in date
        if 'position_date' in filtered_params:
            max_date = Position.objects.filter(position_date__lte=filtered_params['position_date']).aggregate(position_date=Max('position_date'))['position_date'].isoformat()
            # use the max date in place of original date
            filtered_params['position_date'] = max_date

        # fix some janky nested route issues between django and drf
        filtered_kwargs = {k.replace('_pk', '_id'): v for k, v in self.kwargs.items()}
        
        # merge kwargs and params
        qs_filter = {**filtered_params, **filtered_kwargs}
        qs = Position.objects.filter(**qs_filter)

        return qs

    serializer_class = PositionSerializer
    filter_backends = [SearchFilter, OrderingFilter, DjangoFilterBackend]
    filterset_fields = [
        'symbol',
        'shares',
        'position_date',
    ]
    search_fields = [
        '^symbol',
    ]
    ordering_fields = [
        'symbol',
        'shares',
        'position_date',
    ]
    ordering = ['position_date', 'symbol']

CodePudding user response：

You should try to remove position_date from filterset_fields because I think that DjangoFilterBackend overrides your queryset.

And one more piece of advice: you have to do all your filtration stuff about the position_date field in the custom filter, not in the get_queryset function.