0

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!

1 Answer 1

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.

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

1 Comment

Thank you for your response. Is there a way where I can just modify my existing filter code to make this work? thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.