Django Query (aggregates and counts)

Question

Hey guys, I've got a model that looks like this:

class Interaction(DateAwareModel, UserAwareModel):
  page = models.ForeignKey(Page)
  container = models.ForeignKey(Container, blank=True, null=True)
  content = models.ForeignKey(Content)
  interaction_node = models.ForeignKey(InteractionNode)
  kind = models.CharField(max_length=3, choices=INTERACTION_TYPES)

I want to be able to do one query to get the count of the interactions grouped by container then by kind. The idea being that the output JSON data structure (serialization taken care of by piston) would look like this:

"data": {
   "container 1": {
       "tag_count": 3, 
       "com_count": 1
   },
   "container 2": {
       "tag_count": 7, 
       "com_count": 12
   },
   ...
}

The SQL would look like this:

SELECT container_id, kind, count(*) FROM rb_interaction GROUP BY container_id, kind;

Any ideas on how to group by multiple fields using the ORM? (I don't want to write raw queries for this project if I can avoid id) This seems like a simple and common query.

Before you ask: I have seen the django aggregates documentation and the raw queries documentation.

Update As per advice below I've created a custom manager to handle this:

class ContainerManager(models.Manager):
    def get_query_set(self, *args, **kwargs):
        qs = super(ContainerManager, self).get_query_set(*args, **kwargs)
        qs.filter(Q(interaction__kind='tag') | Q(interaction__kind='com')).distinct()
        annotations = {
            'tag_count':models.Count('interaction__kind'),
            'com_count':models.Count('interaction__kind')
        }
        return qs.annotate(**annotations)

This only counts the interactions that are of kind tag or com instead of retrieving the counts of tags and of the coms via group by. It is obvious that it works that way from the code but wondering how to fix it...

Answer 1

Create a custom manager:

class ContainerManager(models.Manager):
    def get_query_set(self, *args, **kwargs):
        qs = super(ContainerManager, self).get_query_set(*args, **kwargs)
        annotations = {'tag_count':models.Count('tag'), 'com_count':models.Count('com')}
        return qs.annotate(**annotations)

class Container(models.Model):
    ...
    objects = ContainerManager()

Then, Container queries will always include tag_count and com_count attributes. You'll probably need to modify the annotations, since I don't have a copy of your model to refer to; I just guessed on the field names.

UPDATE:

So after gaining a better understanding of your models, annotations won't work for what you're looking for. Really the only to get counts for how many Containers have kinds of 'tag' or 'com' is:

tag_count = Container.objects.filter(kind='tag').count()
com_count = Container.objects.filter(kind='com').count()

Annotations won't give you that information. I think it's possible to write your own aggregates and annotations, so that might be a possible solution. However, I've never done that myself, so I can't really give you any guidance there. You're probably stuck with using straight SQL.