CODEWITHSUNDEEP
djangodjango-orm
Saturnix
Saturnix

Reputation: 10564

Annotate based on related field, with filters

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.

Upvotes: 0

Views: 59

Answers (1)

user1849962
user1849962

Reputation: 1363

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.

Upvotes: 1

Related Questions