Login

Allow filtering and ordering by counts of related query results

Author:
exogen
Posted:
April 10, 2007
Language:
Python
Version:
.96
Score:
6 (after 6 ratings)

I know you're thinking, what the heck could that title mean?

I often find myself wanting to filter and order by the result of a COUNT(*) of a query using a method similar to the entry_count example. Writing this many times is tedious and hardcoding the table and column names made me cringe, I also wanted the counts to result from more complex queries.

This is a method you can add to your custom Manager to do this easily. It's not an ideal syntax, but it's good for the amount of code required.

Example: suppose we have some articles we want to filter and order by comments and visit logs to show the most popular...

class ArticleManager(models.Manager):
    count_related = _count_related

class Article(models.Model):
    pub_date = models.DateTimeField(auto_now_add=True)
    objects = ArticleManager()

class Comment(models.Model):
    article = models.ForeignKey(Article)
    is_spam = models.BooleanField(default=False)

class Visit(models.Model):
    article = models.ForeignKey(Article)
    referrer = models.URLField(verify_exists=False)
    search_query = models.CharField(maxlength=200)

Notice how the ArticleManager is given the count_related method. Now you can find the most popular like so...

Order by non-spam comments:

Article.objects.count_related(Comment.objects.filter(
    is_spam=False)).order_by('-comment__count')

Order by incoming non-search-engine links:

Article.objects.count_related(Visit.objects.filter(
    referrer__isnull=False, search_query__isnull=True),
    'links').order_by('-links')

Order by total visits:

Article.objects.count_related(Visit).order_by('-visit__count')

Note: Doesn't work if query contains joins or for many-to-many relationships, but those could be made to work identically if there's demand.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
from django.db import models

# Some convenience functions.

def _get_table(model):
    return model._meta.db_table

def _get_column(model, attr):
    return "%s.%s" % (_get_table(model), model._meta.get_field(attr).column)

def _get_related_column(model, attr):
    related = model._meta.get_field(attr).rel
    return "%s.%s" % (_get_table(related.to), related.field_name)

def _related_count_sql(related, attr):
    return """SELECT COUNT(*) FROM %s WHERE %s = %s
           """ % (_get_table(related), _get_column(related, attr),
                  _get_related_column(related, attr))

# This is what it's all about.

def _count_related(self, query, count_attr=None, related_attr=None):
    """
    Count the rows matching `query` related to this model by their
    foreign key attribute `related_attr`, and store the result in
    `count_attr`.
    
    If `count_attr` is None, use the name of the module given in `query`
    suffixed with '__count'.
    
    If `related_attr` is None, find the first foreign key field in the
    model queried by `query` relating to this model.
    
    If `query` is a model class, use the all() method on its default
    manager as the query.
    """
    if isinstance(query, models.base.ModelBase):
        query = query._default_manager.all()
    if count_attr is None:
        count_attr = query.model._meta.module_name + '__count'
    if related_attr is None:
        for field in query.model._meta.fields:
            if isinstance(field, models.related.RelatedField):
                if field.rel.to is self.model:
                    related_attr = field.name
    select_count = _related_count_sql(query.model, related_attr)
    joins, wheres, params = query._filters.get_sql(query.model._meta)
    if wheres:
        select_count += ' AND %s' % wheres[0]
    return self.extra(select={count_attr: select_count}, params=params)

# Here's where your code comes in.

class YourManager(models.Manager):
    count_related = _count_related # Add this to your managers.

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 11 months, 2 weeks ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 11 months, 3 weeks ago
  3. Serializer factory with Django Rest Framework by julio 1 year, 6 months ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 7 months ago
  5. Help text hyperlinks by sa2812 1 year, 7 months ago

Comments

exogen (on April 10, 2007):

Hmm, it seems that filter doesn't actually see the attribute added by count_related. It must be possible since order_by sees it just fine... probably a shortcoming of QuerySet.

#

josho (on March 21, 2008):

I'm definitely interested in getting this to work with many-to-many relationships. What would be involved in doing so?

#

Please login first before commenting.