Annotate based on related field, with filters-CodePudding

class IncomeStream(models.Model):
    product = models.ForeignKey(Product, related_name="income_streams")
    from_date = models.DateTimeField(blank=True, null=True)
    to_date = models.DateTimeField(blank=True, null=True)
    value = MoneyField(max_digits=14, decimal_places=2, default_currency='USD')
    
    
class Product(models.Model):
    ...
    
    
class Sale(models.Model):
    product = models.ForeignKey(Product, related_name="sales")
    created_at = models.DateTimeField(auto_now_add=True)
    ...

With the above model, suppose I want to add a value to some Sales using .annotate.

This value is called cpa (cost per action): cpa is the value of the IncomeStream whose from_date and to_date include the Sale created_at in their range.

Furthermore, from_date and to_date are both nullable, in which case we assume they mean infinity.

For example:

<IncomeStream: from 2021-10-10 to NULL, value 10$, product TEST>
<IncomeStream: from NULL to 2021-10-09, value 5$, product TEST>

<Sale: product TEST, created_at 2019-01-01, [cpa should be 5$]>
<Sale: product TEST, created_at 2021-11-01, [cpa should be 10$]>

My question is: is it possible to write all these conditions using only the Django ORM and annotate? If yes, how?

I know F objects can traverse relationships like this:

Sale.objects.annotate(cpa=F('product__income_streams__value'))

But then where exactly can I write all the logic to determine which specific income_stream it should pick the value from?

Please suppose no income stream have overlapping dates for the same product, so the above mentioned specs never result in conflicts.

CodePudding user response：

Something like this should get you started

    subquery = (
        IncomeStream
        .objects
        .values('product') # group by product primary key i.e. product_id
        .filter(product=OuterRef('product'))
        .filter(from_date__gte=OuterRef('created_at'))
        .filter(to_date__lte=OuterRef('created_at'))
        .annotate(total_value=Sum('value'))
    )

Then with the subquery

    Sale
    .objects
    .annotate(
        cpa=Subquery(
            subquery.values('total_value')
        ) # subquery should return only one row so
        # so just need the total_value column
    )

Without the opportunity to play around with this in the shell myself I not 100%. It should be close though anyway.