开发者

How to create annotation on filtered data in Django ORM?

I have the following models:

class ApiUser(models.Model):
    apikey = models.CharField(max_length=32, unique=True)


class ExtMethodCall(models.Model):
    apiuser = models.ForeignKey(ApiUser)
    method  = models.CharField(max_length=100) #method name
    units   = models.PositiveIntegerField()    #how many units method call cost
    created_dt = models.DateField(auto_now_add=True)

For report, i need to get all users who made any call today and total cost of all calls for each user.

In SQL, that would be something li开发者_StackOverflowke:

SELECT apiuser.*, q1.total_cost
FROM apiuser INNER JOIN (
    SELECT apiuser_id, sum(units) as total_cost
    FROM extmethodcall
    WHERE create_dt = curdate()
    GROUP by apiuser_id
) USING apiuser_id

So far, i have found the following solution:

models.ExtMethodCall.objects.filter(created_dt=datetime.date.today()).values('apiuser').annotate(Sum('units'))

which returns me apiuser_id and units__sum.

Is there any more intelligent solution?


Is there any more intelligent solution?

No this is the most natural solution, Django ORM will "tanslate"

.annotate(Sum('units'))

into SQL

SELECT ... sum(units) as units__sum

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜