How to join 3 tables in query with Django, combined select related and prefetch_related

user_10071876 0
Viewed 337

I have tree models 

class Category(models.Model):
    name = models.CharField(max_length=60)


class Product(models.Model):
    description = = models.CharField(max_length=255)
    category = models.ForeignKey(Category, related_name='products')


class Inventory(models.Model):
     userReservation = models.ForeignKey(User)
     Product=models.ForeignKey(Product related_name='products_reservation')
     Quantity = models.IntegerField()

I want to get all product by category, with quantity registered in inventory, for the user and the product

{
"id": 1,
"name": "Corbata",
"products": [{
        "id": 10,
        "description": "shoes",
        "quantitybyInventory": 3
    },
    {
        "id": 9,
        "description": "shirt",
        "quantitybyInventory": 1
    }]}

I have a view with this class views.py

 class inventoryList(generics.ListCreateAPIView):

queryset = Category.objects.prefetch_related('products')
serializer_class = CategorybyProductSerializer
def get_object(self):
    queryset = self.queryset()
    obj = get_object_or_404(queryset)
    return obj

And my serializer

class CategorybyProductSerializer(serializers.ModelSerializer):
products = ProductSerializer(many=True)

class Meta:
    model = Category
    fields = ('id', 'name', 'products')

class ProductSerializer(serializers.ModelSerializer):

class Meta:
    model = Product
    fields = ('id', 'description')

but I can not show the amount of the inventory table

this query just show me it 

{
"id": 1,
"name": "Corbata",
"products": [{
        "id": 10,
        "description": "shoes"
    },
    {
        "id": 9,
        "description": "shirt"
    }
]
}
django-rest-framework django-orm django-models
1 Answers

I use SerializerMethodField, and this post 

class ProductbyUserSerializer(serializers.ModelSerializer):
    quantitybyInventory = serializers.SerializerMethodField(read_only=True)

    def get_quantitybyInventory(self, product):
        return product.products_reservation.filter(userReservation = self.context['request'].user).count()

    class Meta:
        model = Product
        fields = ('id', 'description', 'quantitybyInventory')
user_100718760
Related