Django - ORM question

Question

Just wondering if it is possible to get a result that I can get using this SQL query with only Django ORM:

SELECT * FROM (SELECT DATE_FORMAT(created, "%Y") as dte, sum(1) FROM some_table GROUP BY dte) as analytics;

The result is:

+------+--------+
| dte  | sum(1) |
+------+--------+
| 2006 |     20 | 
| 2007 |   2230 | 
| 2008 |   4929 | 
| 2009 |   1177 | 
+------+--------+

The simplified model looks like this:

# some/models.py

class Table(models.Model):
   created = models.DateTimeField(default=datetime.datetime.now)

I've tried various ways using mix of .extra(select={}) and .values() and also using the .query.group_by trick described here but would appreciate a fresh eyes on the problem.

Answer 1

Django 1.1 (trunk at the time of posting this) has aggregates, these allow you to perform counts, mins, sums, averages, etc. in your queries.

What you're looking to do would probably be accomplished using multiple querysets. Remember, each row in a table (even a generated results table) is supposed to be a new object. You don't really explain what you're summing so I'll consider it dollars:

book_years = Books.object.all().order_by('year').distinct()

# I use a list comprehension to filter out just the years
for year in [book_year.created.year for book_year in book_years]:
    sum_for_year = Book.objects.filter(created__year=year).aggregate(Sum(sales))

Answer 2

When you need a query that Django doesn't let you express through the ORM, you can always use raw SQL.

For your immediate purpose, I'm thinking that grouping by an expression (and doing an aggregate calculation on the group) is beyond Django's current capabilities.