1

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?

Troy
  • 21,172
  • 20
  • 74
  • 103
  • 1
    Wouldn't it be better to have a surrogate table with `EventDates`? – joanolo Jul 18 '17 at 22:24
  • Possibly, but currently the DB is the way it is and has a ton of data in it. I'm hoping to find out if it can be done with an ArrayField before making a major schema change and data migration. Also, this particular table is pummeled pretty hard and needs to be fast for most date-related queries (other than this particular max-date query - which doesn't _need_ to be uber fast as it won't be needed as often), so I'd like to avoid joins. – Troy Jul 18 '17 at 22:27

1 Answers1

1

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.

joanolo
  • 6,028
  • 1
  • 29
  • 37
  • Did you actually try the code? Did you have to modify it? – joanolo Jul 18 '17 at 22:55
  • 1
    Yes, I did. The above actually finds the latest date among the `Event`s, rather than the `Event` with the latest date. But that's actually what I was ultimately trying to get, anyway. And if I want the `Event`, that's easy enough to query once I have the date. – Troy Jul 18 '17 at 22:57