复杂的注释在django查询集 [英] complex annotate on django query set
问题描述
我有一个问题,我无法从我的django视图中的一个复杂的 .annotate()
调用中获取所需的所有信息。这是我的模型:
I have an issue where I can't quite get all the information I need from a complex .annotate()
call in my django view. Here's my model:
RECORD_STATUS = (
(0, "Hidden"),
(1, "Unhidden"),
(2, "Deleted"),
)
class Activity(models.Model):
event_date = models.DateTimeField()
duration = models.IntegerField(default=0)
username = models.CharField(max_length=200)
record_id = models.CharField(max_length=100)
record_status = models.IntegerField(choices=RECORD_STATUS)
record_quality = models.FloatField(default=0)
我想通过基于我的视图模板的不同record_id的以下值:
I'd like to pass the following values based on a distinct record_id to my view template:
- 平均质量
- count
- 最新的event_date
- 总持续时间
- average quality
- count
- latest event_date
- total duration
..
...最新状态(特定record_id的最新event_date的活动状态)
...latest status (status of Activity with the latest event_date for a particular record_id)
我已经能够按照预期的方式获得大部分内容,并提供以下查询et codechunk但是我无法获取特定 record_id
的最新状态。我倾向于相信一个 .extra()
调用可能是要走的路,但我似乎无法正确构建它。
I've been able to get most of these as intended, with the following queryset codechunk but I am unable to grab the latest status for a particular record_id
. I'm inclined to believe an .extra()
call might be the way to go, but I can't seem to construct it properly.
record_id_details = Activity.objects.values(
'record_id',
).annotate(
ave_quality=Avg('record_quality'),
count=Count('id'),
latest_event_date=Max('event_date'),
total_duration=Sum('duration'),
# Here's where I'm going wrong
# I need the record_statuses from the most recent Activities of each record_id
latest_status=,# dont know... record_status WHERE Max(event_date) ???
).order_by(sort)
关于注释参数的任何建议,可以给我所需要的,或者也许一些帮助构建额外的命令将不胜感激。谢谢!
Any advice as to an annotate argument that would give me what I need, or perhaps some help constructing the extra command would be appreciated. Thanks!
推荐答案
没有内置的方式用Django查询表达这个查询。在两个查询中执行它可能是纯粹使用ORM最简单的方法。如果您不介意一些原始的SQL,并且您正在使用支持它的数据库(如PostgreSQL),那么您需要的工具称为Window函数,您可以在这里找到很好的细节: http://www.postgresguide.com/tips/window.html
There's no builtin way to express this query with a Django queryset. Performing it in two queries is probably the easiest way to do it purely with the ORM. If you don't mind some raw SQL, and you're using a database that supports it, like PostgreSQL, the tool you'll want is called a Window Function, you can find good detail here: http://www.postgresguide.com/tips/window.html
这篇关于复杂的注释在django查询集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!