Django Rest Framework - filter nested in one
What I want to achieve:
I want to list users with their respective missions and filter mission 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 being ignored and I cannot find a way to make it work. I have an intuition that the problem with Mission.objects.all()
the ViewSet should probably be something like this:Mission.objects.filter(*But what here?*)
Any help would be much appreciated!
Edit 1:
There is some progress! But still doesn't work ... As suggested by 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 request that is being 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 JOINs instead of 1. For some reason, it accepts 2 filtered fields as if they were in separate tables. As a result, my results are duplicated.
source to share
There are three things here.
First, you are missing DjangoFilterBackend
a filter_backends
. This is what tells the Django REST framework to look at filter_class
and apply the appropriate filtering to the request, and without it yours filter_class
will be ignored (as you've seen).
class UserViewset(viewsets.ModelViewSet):
filter_backends = (filters.OrderingFilter, filters.DjangoFilterBackend, )
filter_class = UserFilter
serializer_class = UserSerializer
Second, you expect to be able to use query parameters start
and end
, but tell the django filter to look at the field missions__start
in Meta.fields
. You can fix this by manually defining the fields in 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 just a reference to the request parameters will be full values ( missions__start_gte
instead of start_gte
).
Third, because of the way queries INNER JOIN
work across multiple tables, you will get duplicate values when you create a filter that affects multiple missions under the same user. You can fix this by using an argumentdistinct
in your filters (as shown above) or by adding .distinct()
to the end of your filter calls in filter_queryset
.
source to share
Given that you want to filter nested missions
I would suggest that you do it the other way around and then handle the rest of the client side. i.e.
First, submit a request for filtered missions that reference their user ID.
Then it sends a request for the users mentioned, that is, "# id__in = 1,2,3"
... or if you only have a small number of users: Send a request for all users
Having said that, I think you can also have your way if you like, applying filters to missions by extending filter_queryset
Here is one way to filter nested missions
Note, if you don't want to filter nested missions, you can simply remove 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's another idea
This way you can use the same query parameters that you are 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 anything, so it would be great to get some feedback.
source to share
Your filter_class is being ignored because you are not declaring DjangoFilterBackend inside filter_backends.
class UserViewset(viewsets.ModelViewSet):
filter_backends = (filters.OrderingFilter, filters.DjangoFilterBackend)
filter_class = UserFilter
Since you have an OrderingFilter but no ordering_fields, perhaps you have placed the wrong backend?
source to share