django-rest-framework annotate relationship

Question

I have spend many hours on google to find a solution, but i'm still stuck with my problem. I have two tables, I'm interested in the latest date related stock_value from the Stock Model. How to set this relationship after annotate?

The Product Tables looks like:

    {
    product_id: 1, 
    'description': 
    'macbook pro', 'brand': 
    'Apple', 
    'supplier': 'Apple'
    }

The Stock Table looks like:

    {product_id: 1, 'date': '04-07-2020', 'stock-value': 38}
    {product_id: 1, 'date': '05-07-2020', 'stock-value': 34}
    {product_id: 1, 'date': '06-07-2020', 'stock-value': 32}
    {product_id: 1, 'date': '07-07-2020', 'stock-value': 24}

This is the result I'm looking for:

    {product_id: 1, ... other_fields, stock_value: 24}

My code looks like:

class Product(models.Model):
    supplier_id = models.ForeignKey(Supplier, on_delete=models.CASCADE)
    description = models.CharField(max_length=200)
    brand = models.CharField(max_length=200)


class Stock(models.Model):
    product_id = models.ForeignKey(Product, on_delete=models.CASCADE)
    date = models.DateField(default=timezone.now)
    stock_value = models.BigIntegerField()


class ProductSerializer(serializers.ModelSerializer):
    supplier = serializers.ReadOnlyField(source='supplier_id.supplier')
    date = serializers.DateField()
    stock = serializers.IntegerField()

    class Meta:    
       model = Product
       fields = ('description', 'brand', 'supplier' 'date', 'stock')


class ProductList(generics.ListCreateAPIView):

    def get_queryset(self):
        query = Product.objects.annotate(date=Max('stock__date'))

        return query

Answer 1

I don't have a complete solution for you, but maybe some help.

I think you should add a related name to Stock model first like this.

class Stock(models.Model):
    product_id = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='stock_prices')

Then to query this you can do (I'm not sure how to integrate into Django Rest Framework but I think you'll know that).

product = Product.objects.get(pk=some_pk)
product.stock_prices.order_by('-date')[0]

Maybe have a look a DRF SerializerMethodField aswell, I think you should be able to integrate the query above into a MethodField.