Django Sum & Count

Question

I have some MySQL code that looks like this:

SELECT 
visitor AS team,
COUNT(*) AS rg,
SUM(vscore>hscore) AS rw,
SUM(vscore<hscore) AS rl 
FROM `gamelog` WHERE status='Final' 
      AND date(start_et) BETWEEN %s AND %s GROUP BY visitor

I'm trying to translate this into a Django version of that query, without making multiple queries. Is this possible? I read up on how to do Sum(), and Count(), but it doesn't seem to work when I want to compare two fields like I'm doing.

Here's the best I could come up with so far, but it didn't work...

vrecord = GameLog.objects.filter(start_et__range=[start,end],visitor=i['id']
                                 ).aggregate(
                                             Sum('vscore'>'hscore'),
                                             Count('vscore'>'hscore'))

I also tried using 'vscore>hscore' in there, but that didn't work either. Any ideas? I need to use as few queries as possible.

Answer 1

Aggregation only works on single fields in the Django ORM. I looked at the code for the various aggregation functions, and noticed that the single-field restriction is hardwired. Basically, when you use, say, Sum(field), it just records that for later, then it passes it to the database-specific backend for conversion to SQL and execution. Apparently, aggregation and annotation are not standardized in SQL.

Anyway, you probably need to use a raw SQL query.