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
- Template tag - list punctuation for a list of items by shapiromatron 10 months, 4 weeks ago
- JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 11 months ago
- Serializer factory with Django Rest Framework by julio 1 year, 5 months ago
- Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 6 months ago
- Help text hyperlinks by sa2812 1 year, 7 months ago
Comments
Hmm, it seems that
filter
doesn't actually see the attribute added bycount_related
. It must be possible sinceorder_by
sees it just fine... probably a shortcoming ofQuerySet
.#
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.