Django Rest Framework - Filter nested one to many

Question

What I want to achieve:

I want a list of users with their respective missions, and filter on missions start date.

# Pseudo json
User 1
  - mission 1
  - mission 2
User 2
  - mission 1
  - mission 2
  - mission 3

My data structure:

Models:

class Mission(models.Model):
  start = models.DateTimeField()
  user = models.ForeignKey(settings.AUTH_USER_MODEL, related_name="missions")

Serializers:

# Mission
class MissionSerializer(serializers.ModelSerializer):
  class Meta:
    model  = Mission
    fields = (
      'start',
      'end',
    )

# User
class UserSerializer(serializers.ModelSerializer):
  missions = MissionSerializer(many=True)
  class Meta:
    model  = MyUser
    fields = (
      'username',
      'missions',
    )

Viewsets:

# Filter
class UserFilter(django_filters.FilterSet):
  class Meta:
    model  = MyUser
    fields = {
      'missions__start': ['gte','lt']
    }

# Viewset
class UserViewset(viewsets.ModelViewSet):
  filter_backends  = (filters.OrderingFilter, filters.DjangoFilterBackend,)
  filter_class     = UserFilter
  serializer_class = UserSerializer

  @list_route(methods=['get'])
  def listCalendar(self, request):
    prefetched_missions = Prefetch('missions', queryset=Mission.objects.all())
    objects_list = MyUser.objects.prefetch_related( prefetched_missions )
    objects_list = self.filter_queryset(objects_list)
    serializer   = UserSerializer(objects_list, many=True)
    return Response(serializer.data)

My Problem:

When calling this URL:

/api/users/listCalendar/?start__gte=2015-06-29&start__lt=2015-08-10

The filter is ignored and I can't figure out a way to make it work. I have the intuition that the problem is with Mission.objects.all() in the ViewSet that should probably be something like: Mission.objects.filter(*But what here?*)

Any help would be very much appreciated!

---

Edit 1:

There is some progress! But still not working... As you suggested Mark Galloway i tried calling the following url:

/api/users/listCalendar/?missions__start__gte=2015-06-29&missions__start__lt=2015-08-10

But this is the query that gets executed:

SELECT "app_myuser"."id", "app_myuser"."username"
FROM "app_myuser"
INNER JOIN "app_mission" ON ( "app_myuser"."id" = "app_mission"."user_id" )
INNER JOIN "app_mission" T4 ON ( "app_myuser"."id" = T4."user_id" )
WHERE ("app_mission"."start" >= '2015-07-06T00:00:00+00:00'::timestamptz
AND T4."start" < '2015-07-12T00:00:00+00:00'::timestamptz)
ORDER BY "app_myuser"."username" ASC;

As you can see, there are 2 INNER JOIN instead of 1. For some reasons it takes the 2 filtered fields as if they were in separate tables. The result is that my results are dupplicated.

Answer 1

There are three things here

First, you're missing the DjangoFilterBackend in your filter_backends list. This is what tells Django REST framework to look at the filter_class and apply the related filtering to the request, and without it your filter_class will be ignored (as you saw).

class UserViewset(viewsets.ModelViewSet):
    filter_backends = (filters.OrderingFilter, filters.DjangoFilterBackend, )
    filter_class = UserFilter
    serializer_class = UserSerializer

Second, you're expecting to be able to use the start and end query parameters but are telling django-filter to look at the missions__start field in the Meta.fields. You can fix this by manually defining the fields on the FilterSet with your alias

class UserFilter(django_filters.FilterSet):
    start_gte = django_filter.DateTimeFilter(name='missions__start', lookup_type='gte', distinct=True)
    start_lte = django_filter.DateTimeFilter(name='missions__start', lookup_type='lte', distinct=True)

    end_gte = django_filter.DateTimeFilter(name='missions__end', lookup_type='gte', distinct=True)
    end_lte = django_filter.DateTimeFilter(missions__name='end', lookup_type='lte', distinct=True)

    class Meta:
        model  = MyUser
        fields = ('start_gte', 'start_lte', 'end_gte', 'end_lte', )

Or by just referencing the query parameters will the full values (missions__start_gte instead of start_gte).

Third, because of how INNER JOIN queries work across multiple tables, you will receive duplicate values when doing a filter that affects multiple missions under a single user. You can fix this by using the distinct argument in your filters (as shown above) or adding .distinct() to the end of your filter calls in filter_queryset.

Answer 2

Given that you want to filter nested missions

I would suggest that you do this the other way around, and then handle the rest client side. i.e.

First send a request for filtered missions, that reference the id of their user.
Then send a request for the referenced users i.e. "#id__in=1,2,3"
...or if you'll only ever have a small number of users: Send a request for all the user

That being said, I think you can also have your way if you want, by applying the filters to the missions as well, by extending filter_queryset

Here is one approach to filtering nested missions

Note that if you don't want to filter the nested missions, you can simply delete the filter_queryset method from the class.

class MissionFilter(django_filters.FilterSet):
    class Meta:
        model = Mission
        fields = {
            'start': ['gte', 'lt'],
            'end': ['gte', 'lt'],
        }

class UserFilter(django_filters.FilterSet):
    class Meta:
        model = MyUser
        fields = {
            'start': ['gte', 'lt'],
            'end': ['gte', 'lt'],
        }

class UserViewset(viewsets.ModelViewSet):
    filter_backends  = (filters.OrderingFilter, filters.DjangoFilterBackend,)
    filter_class     = UserFilter
    serializer_class = UserSerializer

    def get_queryset(self):
        # Get the original queryset:
        qs = super(UserViewset, self).get_queryset()

        # * Annotate:
        #     * start = the start date of the first mission
        #     * end = the end date of the last mission
        # * Make sure, we don't get duplicate values by adding .distinct()
        return qs.annotate(start=models.Min('missions__start'),
                           end=models.Max('missions__end')).distinct()

    def filter_queryset(self, queryset):
        # Get the original queryset:
        qs = super(UserViewset, self).filter_queryset(queryset)

        # Apply same filters to missions:
        mqs = MissionFilter(self.request.query_params,
                            queryset=Missions.objects.all()).qs
        # Notice: Since we "start", and "end" in the User queryset,
        #         we can apply the same filters to both querysets

        return qs.prefetch_related(Prefetch('missions', queryset=mqs))

Here is another idea

This way you can use the same query parameters that you're already using.

class MissionFilter(django_filters.FilterSet):
    class Meta:
        model = Mission
        fields = {
            'start': ['gte', 'lt'],
            'end': ['gte', 'lt'],
        }

class UserFilter(django_filters.FilterSet):
    class Meta:
        model = MyUser
        fields = {
            'missions__start': ['gte', 'lt'],
            'missions__end': ['gte', 'lt'],
        }

class UserViewset(viewsets.ModelViewSet):
    filter_backends  = (filters.OrderingFilter, filters.DjangoFilterBackend,)
    filter_class     = UserFilter
    serializer_class = UserSerializer
    queryset         = MyUser.objects.all().distinct()

    def filter_queryset(self, queryset):
        # Get the original queryset:
        qs = super(UserViewset, self).filter_queryset(queryset)

        # Create a copy of the query_params:
        query_params = self.request.GET.copy()

        # Check if filtering of nested missions is requested:
        if query_params.pop('filter_missions', None) == None:
            return qs

        # Find and collect missions filters with 'missions__' removed:
        params = {k.split('__', 1)[1]: v
                  for k, v in query_params.items() if k.startswith('missions__')}

        # Create a Mission queryset with filters applied:
        mqs = MissionFilter(params, queryset=Missions.objects).qs.distinct()

        return qs.prefetch_related(Prefetch('missions', queryset=mqs))

I haven't tested any of this, so it would be cool to get some feedback.