I use an Event log that tracks subscriptions and unsubscriptions to given mailing lists.

My objective is to hit the database one time (sqlite) to get the number of subscribed users, I don't need the objects, just a number.

models.py

class MailingListEvent(models.Model):
    """Events on mailing lists.

    This represents subscribes, unsubscribes, and bounces.  We'd like
    to understand what happens and when, not just the current state of
    the system.

    """
    class EventType(models.TextChoices):
        SUBSCRIBE = 'sub', 'inscription'
        UNSUBSCRIBE = 'unsub', 'désinscription'
        BOUNCE = 'bounce', 'bounce'

    user = models.ForeignKey(User, on_delete=models.CASCADE)
    mailing_list = models.ForeignKey(MailingList,
                                     on_delete=models.CASCADE)
    event_timestamp = models.DateTimeField(default=django.utils.timezone.now)
    event_type = models.CharField(max_length=6, choices=EventType.choices)

So far I found only this solution to work with :

def user_subscribe_count(mailing_list):
    """Return the number of users currently subscribed to the mailing list.

    We want to know how many users are currently subscribed.  Note
    that individual users might subscribe and unsubscribe multiple
    times.  Other (future) events could happen as well.
    """
    user_list = MailingListEvent.objects.filter(mailing_list=mailing_list).values_list('user',flat=True).distinct()
    users_subscribed = list()
    for user in user_list:
        user_state = user_current_state(User.objects.get(pk=user),mailing_list)
        if user_state.event_type == MailingListEvent.EventType.SUBSCRIBE:
           users_subscribed.append(user_state)
    return len(users_subscribed)

def user_current_state(user, mailing_list):
    """Return user's most current state on the provided mailing list

    Return the most recent event associated with this user in this
    mailing list.

    """
    try:
        the_user = MailingListEvent.objects.filter(
            Q(event_type=MailingListEvent.EventType.SUBSCRIBE) |
            Q(event_type=MailingListEvent.EventType.UNSUBSCRIBE),
            user=user, mailing_list=mailing_list).latest(
                'event_timestamp')
        return the_user
    except MailingListEvent.DoesNotExist:
        return MailingListEvent(
            user=user, mailing_list=mailing_list,
            event_type=MailingListEvent.EventType.UNSUBSCRIBE)

I know there is a .count() method on Django, but converting to list already hits the database anyway.

Can someone suggest a query that would return the number of users currently subscribed given this model please ?

CodePudding user response：

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.