Filter from 2 different tables using Django rest Framework

Question

Good day, I am trying to filter data from 2 different tables which have a one to many relationships.

I have products table and specification table. 1 product is related to many specification list. I dont have any problems getting all the information of the product including the specifications. I am also successful in filtering the products table, like filter by category and manufacturer. but I want to filter also based on the specifications while filtering the category/manufacturer.

here is my models.

class Products(models.Model):
    product_partnumber = models.CharField(max_length=255)
    image_link = models.URLField(default=None, blank=True, null=True)
    pdf_link = models.URLField()
    manufacturer = models.ForeignKey(Manufacturer, on_delete=models.CASCADE)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    subcategory = models.ForeignKey(Subcategory, on_delete=models.CASCADE)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        verbose_name_plural = 'Products'

class ProductSpecification(models.Model):
    product = models.ForeignKey(Products, related_name='specifications', on_delete=models.CASCADE)
    specification = models.ForeignKey(Specification, related_name='specification_name', on_delete=models.CASCADE)
    value = models.CharField(max_length=255)
    created_at = models.DateTimeField(auto_now_add=True)

and this is my codes for api and filtering. As of now I am only successful on filtering on one table. I have no idea how I can also add the ProductSpecification model on my filter class.

class NumberInFilter(filters.BaseInFilter, filters.NumberFilter):
    pass

class ProductFilter(filters.FilterSet):
    manufacturer__in = NumberInFilter(field_name='manufacturer', lookup_expr='in')

    class Meta:
        model = Products
        fields = ['category', 'product_partnumber']

class ProductsView(viewsets.ModelViewSet):
    queryset = Products.objects.all()
    serializer_class = ProductsSerializers
    filter_backends = [filters.DjangoFilterBackend]
    filterset_class = ProductFilter

    def create(self, request, *args, **kwargs):
        many = True if isinstance(request.data, list) else False
        serializer = ProductsSerializers(data=request.data, many=many)
        if serializer.is_valid():
            serializer.save()
            product=serializer.data
        else:
            return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)
        return Response(product, status=status.HTTP_201_CREATED)

class SpecificationView(viewsets.ModelViewSet):
    queryset = Specification.objects.all()
    serializer_class = SpecificationSerializers

    def create(self, request, *args, **kwargs):
        many = True if isinstance(request.data, list) else False
        serializer = SpecificationSerializers(data=request.data, many=many)
        if serializer.is_valid():
            serializer.save()
        else:
            return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)
        return Response(serializer.data, status=status.HTTP_201_CREATED)
    
class ProductSpecificationView(viewsets.ModelViewSet):
    queryset = ProductSpecification.objects.all()
    serializer_class = ProductSpecificationSerializers

    def create(self, request, *args, **kwargs):
        many = True if isinstance(request.data, list) else False
        serializer = ProductSpecificationSerializers(data=request.data, many=many)
        if serializer.is_valid():
            serializer.save()
        else:
            return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)
        return Response(serializer.data, status=status.HTTP_201_CREATED)

I want to select all the data of a product where product.category is equal to $val1 and productspecification.specification = $val2 and productspecification.value = $val3. But even if I only use a filter on 1 specification, if a product have 3 specification I still want to get all those data.

this is the format of the product data that I am getting and this is still the same data format that I want to receive using the filter

{
  "id": 1,
  "product_partnumber": "SSPA 1.2-1.4-800-RM",
  "image_link": "https://www.aethercomm.com/wp-content/uploads/2019/05/SSPA-1.2-1.4-800-RM-300x112.png",
  "pdf_link": "https://www.aethercomm.com/wp-content/uploads/2019/05/Data-Sheet_SSPA-1.2-1.4-800-RM.pdf",
  "manufacturer": 1,
  "manufacturer_details": {
    "id": 1,
    "name": "Aethercomm",
    "url": "https://www.aethercomm.com/",
    "created_at": "2020-09-08T03:48:34.200963Z"
  },
  "category": 2,
  "category_details": {
    "id": 2,
    "name": "Amplifier",
    "created_at": "2020-09-09T16:17:56.777422Z"
  },
  "subcategory": 45,
  "specifications": [
    {
      "specification": 2,
      "specification_details": {
        "id": 2,
        "name": "Frequency (min/max)",
        "created_at": "2020-09-09T16:15:33.428022Z"
      },
      "value": "1200/1400"
    },
    {
      "specification": 1,
      "specification_details": {
        "id": 1,
        "name": "Description",
        "created_at": "2020-09-09T16:15:33.301119Z"
      },
      "value": "High Power L Band Radar Pulsed Amplifier 800 Watts typical Peak Output Power 50 dB typ gain Typical Pulse Width is 100uSec to 1000 uSec Typical Duty Cycle is 10%"
    }
  ]
}

Sorry for the long post and thanks in advance. Cheers!

Answer 1

Have you tried something like this:

Products.objects.filter(category=$val1, specifications.specification=$val2, specifications.value=$val3)

I am not sure what you mean with the last: But even if I only use a filter on 1 specification, if a product have 3 specification I still want to get all those data.