Annotate over Multi-table Inheritance in Django

Question

I have a base LoggedEvent model and a number of subclass models like follows:

class LoggedEvent(models.Model):
    user = models.ForeignKey(User, blank=True, null=True)
    timestamp = models.DateTimeField(auto_now_add=True)

class AuthEvent(LoggedEvent):
    good = models.BooleanField()
    username = models.CharField(max_length=12)

class LDAPSearchEvent(LoggedEvent):
    type = models.CharField(max_length=12)
    query = models.CharField(max_length=24)

class PRISearchEvent(LoggedEvent):
    type = models.CharField(max_length=12)
    query = models.CharField(max_length=24)

Users generate these events as they do the related actions. I am attempting to generate a usage-report of how many of each event-type each user has caused in the last month. I am struggling with Django's ORM and while I am close I am running into a problem. Here is the query code:

def usage(request):
    # Calculate date range
    today = datetime.date.today()
    month_start = datetime.date(year=today.year, month=today.month - 1, day=1)
    month_end = datetime.date(year=today.year, month=today.month, day=1) - datetime.timedelta(days=1)

    # Search for how many LDAP events were generated per user, last month
    baseusage = User.objects.filter(loggedevent__timestamp__gte=month_start, loggedevent__timestamp__lte=month_end)
    ldapusage = baseusage.exclude(loggedevent__ldapsearchevent__id__lt=1).annotate(count=Count('loggedevent__pk'))
    authusage = baseusage.exclude(loggedevent__authevent__id__lt=1).annotate(count=Count('loggedevent__pk'))

    return render_to_response('usage.html', {
        'ldapusage' : ldapusage,
        'authusage' : authusage,
    }, context_instance=RequestContext(request))

Both ldapusage and authusage are both a list of users, each user annotated with a .count attribute which is supposed to represent how many particular events that user generated. However in both lists, the .count attributes are the same value. Infact the annotated 'count' is equal to how many events that user generated, regardless of type. So it would seem that my specific

authusage = baseusage.exclude(loggedevent__authevent__id__lt=1)

isn't excluding by subclass. I have tried id_lt=1, id_isnull=True, and others. Halp.

Answer 1

The key to Django model inheritance is remembering that with a non-abstract base class everything is really an instance of the base class which might happen to have some extra data strapped on the side from a separate table. This means that when you do searches on the base table you get back instances of the base class and there's no way to tell which subclass it is without doing repeated database queries on the subclass tables to see if they contain a record with a matching key ("I have an event. Does it have a record in AuthEvent? No. What about LDAP Event?…"). Among other things this means that you can't easily filter on them in normal queries on the base class without doing a join on every subclass table.

You have a couple of choices: one would simply be to do your queries on the subclass and tally the results (ldap_event_count = LDAPEvent.objects.filter(user=foo).count(), …), which might be sufficient for a single report. I usually recommend adding a content type field to the base class so you can efficiently tell which particular subclass an instance is without having to do another query:

content_type = models.ForeignKey("contenttypes.ContentType")

That allows two major improvements: the most common one is that you can deal with many Events generically without having to do something like hit the subclass-specific accessors (e.g. event.authevent or event.ldapevent) and handling DoesNotExist. In this case it would also make it trivial to rewrite your query since you could just do something like Event.objects.aggregate(Count("content_type")) to get the report values, which becomes particularly handy if your logic gets more complicated ("Event is Auth or LDAP and …").