使用Q对排行榜进行Django复杂查询

Question

我的模型是这样的：

class User(AbstractUser):
    ...

class UserPoints(models.Model):
    user = models.ForeignKey(User, relative_name='points')
    earned_points= models.IntegerField()
    reward_month = models.IntegerField(default=0)
    reward_year = models.IntegerField(default=0)

每个月都会创建UserRewards。我使用Q生成查询

我想做的是列出前10名用户，按照earned_points的总和排列。UserRewards将被筛选为只包含最近三个月的值。我知道如何单独执行过滤、聚合、注释和求和。但是在单个查询或组合两个查询中，我似乎无法准确地理解它，特别是当每个用户有多个UserRewards时

Answer 1

我已将您的模型固定为具有on_delete属性，并将relative_name更正为related_name如下所示：

class UserPoints(models.Model):
    user = models.ForeignKey(User, related_name='points', on_delete=models.CASCADE)
    earned_points = models.IntegerField()
    reward_month = models.IntegerField(default=0)
    reward_year = models.IntegerField(default=0)

然后我添加了一些测试数据：

>>> up = UserPoints(user=User.objects.get(pk=1), earned_points=10, reward_month=2, reward_year=2021)
>>> up.save()
>>> up = UserPoints(user=User.objects.get(pk=1), earned_points=15, reward_month=1, reward_year=2021)
>>> # up assignment and up.save() omitted from here on...
>>> UserPoints(user=User.objects.get(pk=2), earned_points=5, reward_month=1, reward_year=2021)
>>> UserPoints(user=User.objects.get(pk=3), earned_points=7, reward_month=2, reward_year=2021)
>>> UserPoints(user=User.objects.get(pk=3), earned_points=3, reward_month=1, reward_year=2021)

现在我们可以从User开始对UserPoints进行求和和和排序，如下所示：

from django.db.models import Sum
for user in User.objects.all() \
        .annotate(earned_points_sum=Sum('points__earned_points')) \
        .filter(earned_points_sum__gt=0) \
        .order_by('-earned_points_sum'):
    print(user.pk, user.earned_points_sum)

这将产生：

1 25
3 10
2 5