complex annotate on django query set

Question

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)

I'd like to pass the following values based on a distinct record_id to my view template:

average quality
count
latest event_date
total duration

and...

...latest status (status of Activity with the latest event_date for a particular record_id)

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!

Alex Gaynor · Accepted Answer · 2013-03-29 07:09:01Z

1

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

answered Mar 29, 2013 at 7:09

Alex Gaynor

15.1k10 gold badges66 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nick Siemsen · Accepted Answer · 2013-03-29 06:56:21Z

0

I would just execute a second query so that I could keep all of that separated. Something like:

latest = Activity.objects.filter(record_id="whatever_the_record_id_you_want").order_by("-event_date)
most_recent = latest[0]
what_you_asked_for = most_recent.record_status

answered Mar 29, 2013 at 6:56

Nick Siemsen

1

Collectives™ on Stack Overflow

complex annotate on django query set

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related