django rest framework filter on related model

Question

I have a model countries and a model of persons on holiday in a certain year to a country. I want to have an Api of Countries in which I can filter only the countries in which certain persons had a holiday in a certain year.

Models.py

from django.db import models

class Country(models.Model):
  id = models.CharField(max_length=50,primary_key=True)
  country = models.CharField(max_length=255,null=True)

class PersonYear(models.Model):
  id = models.IntegerField(primary_key=True)
  person = models.CharField(max_length=255,null=True)
  year = models.IntegerField(null=True)
  country = models.ForeignKey(Country, related_name='personyears')

Contents of model Country

id|country
1|France
2|Italy
3|Spain

Contents of model PersonYear

id|person|year|country_id
1|John|2014|1
2|John|2015|1
3|Mary|2014|1

serializers.py

from apiapp.models import PersonYear,Country
from rest_framework import serializers

class PersonyearSerializer(serializers.HyperlinkedModelSerializer):

   class Meta:
      model = PersonYear
      fields = ('id','person','year')

class CountrySerializer(serializers.HyperlinkedModelSerializer):
   personyears = PersonyearSerializer(many=True)

   class Meta:
      model = Country
      fields = ('id','country','personyears')

Views.py

import rest_framework_filters as filters

class PersonyearFilter(filters.FilterSet):
   id = filters.AllLookupsFilter(name='id')
   person = filters.AllLookupsFilter(name='person')
   year = filters.AllLookupsFilter(name='year')

   class Meta:
      model = PersonYear

class PersonyearViewSet(viewsets.ModelViewSet):
   queryset = PersonYear.objects.all()
   serializer_class = PersonyearSerializer
   filter_backends = (filters.backends.DjangoFilterBackend,)
   filter_fields =  ['id','person','year']
   filter_class = PersonyearFilter

class CountryFilter(filters.FilterSet):
   id = filters.AllLookupsFilter(name='id')
   nm = filters.AllLookupsFilter(name='country')
   personyears = filters.RelatedFilter(PersonyearFilter,name='personyears') 

   class Meta:
      model = Country 

class CountryViewSet(viewsets.ModelViewSet):
   queryset = Country.objects.all()
   serializer_class = CountrySerializer
   filter_backends = (filters.backends.DjangoFilterBackend,)
   filter_fields = ['id','country','personyears']
   filter_class = CountryFilter

I want a selection of all countries in which John had a holiday in 2014:

http://localhost:8000/country/?personyears__person=John&personyears__year=2014

I expected to get one record:

{"id": "1", "country": "France",
   "personyears": [
    {   "id": 1,
        "person": "John"
        "year": 2014
    }
]
}

But instead I got this record repeated 4 times. Can you explain what I am doing wrong and how to get what I want.

Update1:

I don't want only a special solution for John in 2014. I want a solution for all instances of Anyperson in AnyYear. For example I also want the following filter to give me one result: http://localhost:8001/api/country/?personyears__person=Mary&personyears__year=2014

Update2:

I tried replacing:

 queryset = Country.objects.all()

by:

 queryset = Country.objects.all().distinct()

It helped to get the expected (1 record) outcome of:

localhost:8000/country/?personyears__person=John&personyears__year=2014

But I now get unexpected/unwanted result for person='Mary' / year='2015'

localhost:8000/country/?personyears__person=Mary&personyears__year=2015

I expected no result (Mary did not go on holiday in 2015 to any country). But I got

  {"id": "1","country": "France",
   "personyears": [
            {
                "id": 1,
                "person": "John",
                "year": 2014
            },
            {
                "id": 2,
                "person": "John",
                "year": 2015
            },
            {
                "id": 3,
                "person": "Mary",
                "year": 2014
            }
   ]
   }

So, how often was John in France in 2014? This seems to be missing a .distinct() on the final queryset. — dhke
– dhke, Commented Aug 22, 2016 at 13:28
It looks indeed like replacing queryset = Country.objects.all() in CountryViewSet by queryset = Country.objects.all().distinct() simply does the trick. But will do some more testing. — waterbug
– waterbug, Commented Aug 22, 2016 at 14:38
Well that did not work out. If I now filter on person=Mary and year=2015, the results tell me Mary went to France in 2015, while she did not go anywhere in 2015. — waterbug
– waterbug, Commented Aug 22, 2016 at 20:47

Moinuddin Quadri · Accepted Answer · 2016-08-22 13:28:41Z

1

Your queryset should be:

Country.objects.filter(personyears__year='your_year', person='your_person').values_list('country', flat=True)

This will return list of all the countries.

Note: This will filter based on the name of person which may be same for different user. There should be one more model as Person whose foreign key should be mapped to PersonYear.

answered Aug 22, 2016 at 13:28

Moinuddin Quadri

48.4k13 gold badges101 silver badges137 bronze badges

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

9 Comments

waterbug Over a year ago

So I replaced the queryset in CountryViewSet by queryset = Country.objects.filter(personyears__year='2014',personyears__person='John').values_list('country',flat=True) I tested it first in a shell in which it worked. But in views.py it gives error

AttributeError: Got AttributeError when attempting to get a value for field 'id' on serializer 'CountrySerializer'. The serializer field might be named incorrectly and not match any attribute or key on the 'unicode' instance.

waterbug Over a year ago

Also tried to replace with this queryset = Country.objects.filter(personyears__year='2014', personyears__person='John') which gives no error, but same outcome (4 identical records). I also wonder if your suggestion works how to use it in a generic way (ex.: all countries in which Mary had holiday in 2015)

Moinuddin Quadri Over a year ago

Country.objects.filter(personyears__year='2014',personyears_‌_person='John').valu‌es_list('country',fl‌at=True).values('country').distinct()

Is this what you need?

Moinuddin Quadri Over a year ago

Alternatively, you can do:

countries_id = set(Country.objects.filter(personyears__year='your_year', person='your_person').values_list('id', flat=True))

. and then: country = Country.objects.filter(id__in=countries_id). Pass country into the serializer and it will work.

Moinuddin Quadri Over a year ago

By the way, I do not think you really need a serializer. You just need the name of countries, for which directly call the query I mentioned in the answer which return the list of countries. Return that list from the view, instead of clalling serlizer and then return the value

|

Collectives™ on Stack Overflow

django rest framework filter on related model

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related