Filtering a many2many relationship while aggregating?

[Richard Brockie]

Hi,

I have the following many2many relationship between 2 models:

class RaceDay(models.Model):
    date = models.DateField()
    published = models.BooleanField(default=False)

class Series(models.Model):
    published = models.BooleanField(default=False)
    year = models.PositiveSmallIntegerField(default=2015)
    racedays = models.ManyToManyField('RaceDay')

I can use the following annotation to get the Series ordered by the latest RaceDay in each series.

series_ordered_by_latest_raceday = Series.objects \
    .annotate(latest_raceday=Max('racedays__date')) \
    .filter(year=this_year, published=True) \
    .order_by('latest_raceday')

The filter here applies to the fields in the Series model.

How would I limit the aggregation to members of the many2many relation that have RaceDay.published=True?

Thanks,
Richard Brockie

[Richard Brockie]

Aha - I think this does it:

revised_ordered_series = racereg_models.Series.objects \
    .filter(racedays__published=True, year=this_year, published=True) \
    .annotate(latest_raceday=Max('racedays__date')) \
    .order_by('latest_raceday')