[Answered ]-Django ORM Query to get number of currently subbed user

by

1👍

✅

You can annotate the Users with the latest MailingListEvent for that MailingList by using a Subquery expression [Django-doc]:

from django.db.models import OuterRef, Subquery

User.objects.alias(
    latest_action=Subquery(
        MailingListEvent.objects.filter(
            mailing_list=mailing_list,
            user=OuterRef('pk')
        ).order_by('-event_timestamp').values('event_type')[:1]
    )
).filter(latest_action='sub').count()

If the latest action was BOUNCE, then this will not be counted as a subscribed user.

Note: It is normally better to make use of the settings.AUTH_USER_MODEL [Django-doc] to refer to the user model, than to use the User model [Django-doc] directly. For more information you can see the referencing the User model section of the documentation.

Note: Django’s DateTimeField [Django-doc]
has a auto_now_add=… parameter [Django-doc]
to work with timestamps. This will automatically assign the current datetime
when creating the object, and mark it as non-editable (editable=False), such
that it does not appear in ModelForms by default.

👤willeM_ Van Onsem

Leave a comment