Django Rest Framework: QuerySet filtering not working as expected

Question

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']

It's probably unrelated, but is and ['position_date'] == 'all' what you intended to put in that if condition? I would expect perhaps and filtered_params['position_date'] == 'all' there. — Wander Nauta
– Wander Nauta, Commented Jan 22, 2022 at 22:55
@WanderNauta oops, sorry. Copy/paste/edit error. You are correct, it is supposed to be filtered_params['position_date'] == 'all' — jmcopeland
– jmcopeland, Commented Jan 22, 2022 at 23:15

Oleksii Tambovtsev · Accepted Answer · 2022-01-22 23:50:34Z

2

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.

edited Jan 22, 2022 at 23:50

answered Jan 22, 2022 at 23:19

Oleksii Tambovtsev

2,9041 gold badge7 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jmcopeland Over a year ago

filtered_kwargs is {}. filtered_params is {'position_date': '2022-01-07'} and qs_filter is {'position_date': '2022-01-07'}

jmcopeland Over a year ago

I passed in position_date '2022-01-08' which doesn't have records, but '2022-01-07' does. Upon inspection, the qs.all() results look correct, but when the final data comes out there is nothing. Somewhere the qs is being altered and the position_date is being overwritten by the original position_date param. I just don't know where/why.

Oleksii Tambovtsev Over a year ago

Updated my answer.

jmcopeland Over a year ago

That fixed it. Thank you so much. Is there a way to create custom filtering for a single param while still keeping the default DjangoFilterBackend working for everything else? Is that what your custom filter backend suggestion is about? I've never made one of those before.

Oleksii Tambovtsev Over a year ago

Yes, it is. You can see an example in the documentation, it's quite simple. In the filter_queryset function of your custom filter (you can name it PositionDateFilter), you will have the request parameter, you can get position_date value from it. And, of course, you can use DjangoFilterBackend for other fields (DjangoFilterBackend processes all fields from filterset_fields).

Collectives™ on Stack Overflow

Django Rest Framework: QuerySet filtering not working as expected

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related