Allow filtering and ordering by counts of related query results

 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. Sphinx Search ORM by zeeg 6 years, 11 months ago
  2. render_markup filter, specify the markup filter as a string by exogen 7 years ago
  3. Ordered items in the database - alternative by Leonidas 6 years, 10 months ago
  4. TaggedManager and TaggedQuerySet with chainable tagged() methods implemented with django-tagging by fish2000 4 years, 1 month ago
  5. Multiple querysets by t_rybik 4 years, 2 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?

#

(Forgotten your password?)