Sorting querysets by annotated field of reverse relation

Question

I have 2 models, Form and Row, Each Row has a FK to a Form. I want to sort my Forms by the time of last row addition.

class Form(models.Model):
    ...

class Row(models.Model):
    form = models.ForeignKey(
        Form,
        related_name="rows",
    )
    created_at = models.DateTimeField(
        auto_now_add=True,
    )

I tried this but it doesnt work:

queryset = Form.objects.filter(is_empty=False)
queryset = queryset.annotate(last_submit=Max("rows__created_at")).order_by('-last_submit')

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

After annotation, you need to call order_by() method.

sorted_qs = Form.objects.filter(is_empty=False
                                ).annotate(last_submit=Max("rows__created_at")
                                           ).order_by('-last_submit')

Update:

One possible cause not seeing the newly created Form instance at the beginning of the queryset is, some of the Row object may not have created_at value, so that becomes first. In such situations, you can change the order by behavior by setting nulls_last=True. For more details, ref this SO post, Django: Adding “NULLS LAST” to query

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 4, 2020 at 14:01

JPG

89.4k24 gold badges164 silver badges235 bronze badges

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

6 Comments

arianhf Over a year ago

I will add that now, i forget to mention it. I am using this, but it doesn't seem to be working. the order changes, but when I add a new row, it doesnt come to the front of the query.

JPG Over a year ago

" I add a new row, it doesnt come to the front of the query..." Which instance did you add? Form or Row?

arianhf Over a year ago

well for example if I add a row with created_at=now(), I want the corresponding form to be the first form after ordering, but this does not happen.

arianhf Over a year ago

so i found out my code was working fine, except there are some forms that have no submits, and django thinks that None is greater than any date :))

JPG Over a year ago

in such case, use this ordering technique, Django: Adding “NULLS LAST” to query

|

Collectives™ on Stack Overflow

Sorting querysets by annotated field of reverse relation

1 Answer 1

Update:

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update:

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related