Django QuerySet aggregate and join

Question

I'm quite new to Django, I'm trying to figure out how to solve this problem. This is my model:

class Product(models.Model):
    code = models.CharField(max_length=50)
    name = models.CharField(max_length=50)

class ProductDetail(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE, editable=False)
    price = models.DecimalField(max_digits=5, decimal_places=2, default=Decimal('999.99'))
    available = models.BooleanField(default=True)
    validity_tms = models.DateTimeField(default=timezone.now)

Basically, for each Product I want to track price and availability changes. For this reason, many ProductDetail exist for each Product.

When needed, I need to get code, name, available and price fields for every product, but only related to the ProductDetail with the maximum validity_tms for each product.

This QuerySet contains only a part of the informations I need:

ProductDetail.objects.values('product').annotate(max_date=Max('validity_tms'))

How can I retrieve price and available fields, too?

Is there a way to solve this? Did I make something wrong in the model?

Answer 1

@Todor I need the one with the greatest validity_tms

Then you are basically looking for a greatest-1-per-group query.

but this is not within the capabilities of the ORM to be done with a single query.

UPDATE

This question make me do some digging and I just figured out that we can actually build such a query with the new staff coming from Django 1.11. We do this by using Subquery and OuterRef, here is how.

latest_product_details = ProductDetail.objects.filter(
    validity_tms=Subquery(
        (ProductDetail.objects
            .filter(product=OuterRef('product'))
            .values('product')
            .annotate(max_date=Max('validity_tms'))
            .values('max_date')[:1]
        )
    )
)

#now you can choose to work with ProductDetail itself:
for product_detail in latest_product_details.select_related('product'):
    product = product_detail.product
    print (product, product_detail)


#or you can work with Product objects and prefetch those latest details
products = Product.objects.my_complex_filtering().prefetch_related(
    models.Prefetch('productdetail_set',
        queryset=latest_product_details,
        to_attr='latest_details'
    )
)
for product in products:
    #get the first element in the list or None if Empty
    last_detail = next(iter(product.latest_details), None)
    print (product, last_detail) 

END OF UPDATE.

Old answer remains for versions prior Django 1.11

I will offer you an alternative approach with 2 queries. This approach is not suitable in situations with many objects per group without any opportunities to filter down the group to only several elements. This is because, all objects all are gonna be loader into the memory, when you are gonna need only 1 per group.

products = (Product.objects
    .annotate(max_date=Max('productdetail__validity_tms'))
    .prefetch_related(models.Prefetch('productdetail_set',
        #this will fetch all related ProductDetails for a product
        #if one product has many of them, consider further limiting
        #the queryset with some additional filter
        #e.g. only details from the last year or something like that.
        #the idea is to lower the memory footprint, since you need only the first one
        queryset=ProductDetail.objects.order_by('-validity_tms'),
        to_attr='details'
    ))
)

#usage
for product in products:
    print({
        'product': product.id,
        'max_date': product.max_date,
        'detail_price': product.details[0].price if product.details else None,
        'detail_available': product.details[0].available if product.details else None,
    })

Answer 2

Give this a try:

ProductDetail.objects.values('product', 'price', 'available') \
                     .annotate(max_date=Max('validity_tms'))