Reverse relationships and distinct querysets.

Question

I have the following models and I'm trying to work out how to do backward relationships.

I want a distinct CostRate queryset showing which costrates are associated with a particular SalesEvent. The CostFixedList has all the sales that occurred on the different days.

So I'd filter the CostFixedList for a particular SalesEvent, create a list of the [distinct] cost rates it has and then match that up with the CostRate model.

I could do this easily in SQL, but can't get my head around how to do it [or even start] in ORM.

class SalesEvent(models.Model):
    event_type = models.ForeignKey(EventType, verbose_name="Event Type")
    retailer = models.ForeignKey(Retailer, verbose_name="Retailer")
    ....

class CostRate(models.Model):
    cost_item = models.ForeignKey(CostItem, verbose_name="Item")
    valid_from = models.DateField("From")
    valid_till = models.DateField("Till")
    unit_amount = models.DecimalField("Price Per Unit", max_digits=5, decimal_places=2)

class CostFixedList(models.Model):
    sales_event = models.ForeignKey(SalesEvent)
    cost_rate = models.ForeignKey(CostRate)
    units = models.IntegerField()
    appointment = models.ForeignKey(Appointment, null=True, blank=True)

Answer 1

It looks to me as if you have an implied ManyToMany relationship between SalesEvent and CostRate, with the through table as CostFixedList.

So, if you make that explicit, you can query directly via that relationship without having to do anything clever. Just add this to your CostRate model (no schema changes required):

sales_events = models.ManyToManyField(SalesEvent, through='CostFixedList')

Now you can do:

my_sales_event.costrates.all()