How to model complex left join Django

I have two Django models that have a relationship that cannot be modelled with a foreign key

class PositionUnadjusted(models.Model):
    identifier = models.CharField(max_length=256)
    timestamp = models.DateTimeField()
    quantity = models.IntegerField()

class Adjustment(models.Model):
    identifier = models.CharField(max_length=256)
    start = models.DateTimeField()
    end = models.DateTimeField()
    quantity_delta = models.IntegerField()

I want to create the notion of an adjusted position, where the quantity is modified by the sum of qty_deltas of all adjustments where adj.start <= pos.date < adj.end. In SQL this would be

  SELECT pos_unadjusted.id, 
         pos_unadjusted.timestamp,
         pos_unadjusted.identifier,
         CASE
           WHEN Sum(qty_delta) IS NOT NULL THEN pos_unadjusted.qty + Sum(qty_delta)
           ELSE qty
         END AS qty,
  FROM   myapp_positionunadjusted AS pos_unadjusted
         LEFT JOIN myapp_adjustment AS adjustments
                ON pos_unadjusted.identifier = adjustments.identifier
                   AND pos_unadjusted.timestamp >= date_start
                   AND pos_unadjusted.timestamp < date_end
  GROUP  BY pos_unadjusted.id, 
            pos_unadjusted.timestamp,
            pos_unadjusted.identifier,

Is there some way to get this result without using raw sql? I use this query as a base for many other queries so I don't want to use raw sql.

I've looked into QuerySet and extra() but can't seem to coerce them into having this precise relationship. I'd love for position and PositionUnadjusted to have the same model and same API with no copy-pasting since right now updating them is a lot of copy pasting.

Back to Top