Filtering django queryset by a value of the through table of a m2m relationship

Question

I'm trying to do this: queryset.filter(m2m_related_lookup__through_table_field=value)

These are the models, simplified:

class User(models.Model):
    name = models.CharField("nom", max_length=100)
    surname = models.CharField("cognoms", max_length=100)

class Activity(models.Model):
    name = models.CharField("Títol", max_length=200)
    date_start = models.DateField("Dia inici")
    date_end = models.DateField("Dia finalització")
    enrolled = models.ManyToManyField(User, related_name='enrolled_activities', through='ActivityEnrolled')

class ActivityEnrolled(models.Model):
    class Meta:
        db_table = 'main_activity_personetes_enrolled'

    activity = models.ForeignKey(Activity, on_delete=models.CASCADE)
    user = models.ForeignKey(Personeta, on_delete=models.CASCADE)
    date_enrolled = models.DateTimeField("data d'inscripció")
    confirmed = models.BooleanField("confirmada", default=False)

I guess is quite simple, just a many 2 many with a custom through table, so I can store the enrollment date and some other things there. This relationship is set at Activity, with a related_name of 'enrolled_activities'.

So, how can I query "all the users where the ActivityEnrolled.enrollment_date is in 2019" using Django's ORM?

This is for a custom filter (with admin.SimpleListFilter) for a change_list view in the Django Admin, which is listing the User items. In other words, is like I'm doing User.objects.filter(blabla).

Trying: queryset.filter(enrolled_activities__date_enrolled__year=2019) obviously throws the error Related Field got invalid lookup: date_enrolled, because enrolled_activities does not refer to the through table but to the related table (this is: Activity), and this field does not exist there.

Is the only solution to query the through table instead of Users? Like: ActivityEnrolled.objects.filter(date_enrolled__year=2019) + grouping the results so it only returns one row per each User. I know I can do that but it's quite nasty, I've been trying to find a cleaner way to avoid it but with no success.

Thank you very much!!

Answer 1

So, how can I query "all the users where the ActivityEnrolled.enrollment_date is in 2019" using Django's ORM?

A many-to-many relation is in fact just a combination of two one-to-many tables. We thus can filter on the one-to-many relation with:

User.objects.filter(activityenrolled__enrollment_date__year=2019).distinct()

The .distinct() will prevent yielding the same user, if th user has multiple activities for which he/she was enrolled in 2019.