CODEWITHSUNDEEP
djangopostgresqldjango-queryset
Troy
Troy

Reputation: 21872

Django - Query latest date in an ArrayField

I'm making use of Django's postgres ArrayField.

Say I have a model like this:

from django.db import models
from django.contrib.postgres.fields import ArrayField

class Event(models.Model):
    name = models.CharField(max_length=200)
    dates = ArrayField(models.DateField())

And I want to find the Event with the latest date. Is there a way to build a queryset that can do that?

Upvotes: 1

Views: 710

Answers (1)

joanolo
joanolo

Reputation: 6328

I cannot get into the details of Django (I am not so much proficient in it). However, some pointers:

  1. Using directly the SQL language, you would use:

    SELECT
    name, (SELECT max(d) FROM unnest(dates) d) AS latest_date
FROM
    Event ;

  2. To backconvert this to Django, check Django Postgresql ArrayField aggregation, and adapt it to your specifc case.

    I think something along the lines of the following code should do the trick1:

    Event.objects.annotate(arr_els=Func(F('dates'), function='unnest')) \
        .values_list('arr_els', flat=True).aggregate(Max('arr_els'))

See the SQL example at dbfiddle here.

1) I do not have the right environment to test the Django part... so, this is untested.

Upvotes: 1

Related Questions