Django ORM离开加入GROUP BY和SUM [英] Django ORM Left Join With GROUP BY and SUM

问题描述

我正在使用Django 1.8。

我有一个用户模型和一个 UserAction 模型。用户有一个类型。 UserAction有一段时间，表示操作需要多长时间。他们看起来像这样：

 类用户（models.Model）：
 user_type = models.IntegerField（）
 
 class UserAction：
 user = models.ForeignKey（User）
 time = models.IntegerField（）
  

现在我想做的是让所有用户拥有一定的类型，以及他们的动作时间之和。所以看起来像这样：

{user：1，total_time = 5000}，{user：2，total_time = 230}，{user：3，total_time = 0}

鉴于我在var中调用了类型所需的类型，我所做的是：

  UserAction.objects.filter（user__type = type）
 .values（'user'）。annotate（total_time = Sum ））
  

这几乎是我需要的，但它不包括给定类型的用户没有任何与他们相关联的UserAction，在这种情况下，我希望total_time为0.我一直在做一些搜索，但是我不太确定我该怎么做我知道我将如何在原始SQL中执行（只需做一个左连接），但Django ORM对我来说还是很新鲜的。任何人都可以指出我的方向正确吗？任何建议将不胜感激，谢谢！

解决方案

  User.objects.filter（user_type = type）.values（'id'）。annotate（total_time = Sum（useraction__time））
  

I am using Django 1.8.

I have a User model and a UserAction model. A user has a type. UserAction has a time, which indicates how long the action took. They look like this:

class User(models.Model):
   user_type = models.IntegerField()

class UserAction:
   user = models.ForeignKey(User)
   time = models.IntegerField()

Now what I want to do is get ALL users of a certain type along with the sum of the time of their actions.So it would look something like this:

{user:1,total_time=5000}, {user:2,total_time=230}, {user:3,total_time=0}

Given I have the required type in a var called type, what I am doing is:

UserAction.objects.filter(user__type=type)
.values('user').annotate(total_time=Sum(time))

This almost does what I need it to however it does not include users of the given type that don't have any UserAction associated with them, in that case I would want the total_time to just be 0. I've been doing some searching but am not quite sure how I would do this. I know how I would do it in raw SQL(just do a left join) but the Django ORM is still pretty new to me. Could anyone point me in the right direction here? Any advice would be appreciated, thanks much!

解决方案

User.objects.filter(user_type=type).values('id').annotate(total_time=Sum(useraction__time))

这篇关于Django ORM离开加入GROUP BY和SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！