Working in Django Rest Framework (DRF), django-filter, and PostgreSQL, and having an issue with one of our endpoints.
Assume the following:
# models.py
class Company(models.Model):
name = models.CharField(max_length=50)
class Venue(models.Model):
company = models.ForeignKey(to="Company", on_delete=models.CASCADE)
name = models.CharField(max_length=50)
# create some data
company1 = Company.objects.create(name="Proper Ltd")
company2 = Company.objects.create(name="MyCompany Ltd")
Venue.objects.create(name="Venue #1", company=company1)
Venue.objects.create(name="Venue #2", company=company1)
Venue.objects.create(name="Property #1", company=company2)
Venue.objects.create(name="Property #2", company=company2)
# viewset
class CompanyViewSet(viewsets.ReadOnlyModelViewSet):
serializer_class = CompanyVenueSearchSerializer
queryset = (
Venue.objects.all()
.select_related("company")
.order_by("company__name")
)
permission_classes = (ReadOnly,)
http_method_names = ["get"]
filter_backends = (filters.DjangoFilterBackend,)
filterset_class = CompanyVenueListFilter
pagination_class = None
# filterset
class CompanyVenueListFilter(filters.FilterSet):
text = filters.CharFilter(method="name_search")
def name_search(self, qs, name, value):
return qs.filter(
Q(name__icontains=value)
| Q(company__name__icontains=value)
)
class Meta:
model = Venue
fields = [
"name",
"company__name",
]
# serializer
class CompanyVenueSearchSerializer(serializers.ModelSerializer):
company_id = serializers.IntegerField(source="company.pk")
company_name = serializers.CharField(source="company.name")
venue_id = serializers.IntegerField(source="pk")
venue_name = serializers.CharField(source="name")
class Meta:
model = Venue
fields = (
"company_id",
"company_name",
"venue_id",
"venue_name",
)
We now want to allow the user to filter the results by sending a query in the request, e.g. curl -X GET https://example.com/api/company/?text=pr
.
The serializer result will look something like:
[
{
"company_id":1,
"company_name":"Proper Ltd",
"venue_id":1,
"venue_name":"Venue #1"
},
{ // update ORM to exclude this dict
"company_id":1,
"company_name":"Proper Ltd",
"venue_id":2,
"venue_name":"Venue #1"
},
{
"company_id":2,
"company_name":"MyCompany Ltd",
"venue_id":3,
"venue_name":"Property #1"
},
{
"company_id":2,
"company_name":"MyCompany Ltd",
"venue_id":4,
"venue_name":"Property #1"
}
]
Expected result:
Want to rewrite the ORM query so that if the filter ("pr") matches the venue__name
, return all venues. But if the filter matches the company__name
, only return it once, thus in the example above the second dict in the list would be excluded/removed.
Is this possible?
What you can do is to filter
Company
that matchesname
filtering and annotate them with the first relatedVenue
and then combine it's results with the second requirement to return venue withname=value
The query executed when accessing values of
venue_qs
looks likeThis is how the filter should look like
Update for Django 3.2.16
Seems like the query above will not work for such version because it generated a query without parentheses in
WHERE
clause aroundV0."id"
, chunk of query looks likeand it makes PostgreSQL complain with error
For
Django==3.2.16
the filtering method inCompanyVenueListFilter
could look like following:The answer is based on other stackoverflow answer and django docs