Selecting overlapping elements from ArrayField of DateTimeRangeField

[user0008]

Hello all!

I'm trying to flter query that returns rows from the table along with only elements from the ArrayField that overlap a specified DateTimeTZRange. Example:

import datetime
from psycopg2.extras import DateTimeTZRange

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


class MyModel(models.Model):
   myfield = ArrayField(DateTimeRangeField())


d1 = datetime.datetime.now()
d2 = datetime.datetime.now() + datetime.timedelta(days=1)

d3 = datetime.datetime.now() + datetime.timedelta(days=5)
d4 = datetime.datetime.now() + datetime.timedelta(days=7)


MyModel.objects.create(myfield=[DateTimeTZRange(d1, d2), DateTimeTZRange(d3, d4)])


Filtering by each element of array works as expected:

search_from = datetime.datetime.now()
search_to = datetime.datetime.now() + datetime.timedelta(hours=8)

MyModel.objects.filter(myfield__0__overlap=DateTimeTZRange(search_from, search_to))
<QuerySet [<MyModel: MyModel>]>


MyModel.objects.filter(myfield__1__overlap=DateTimeTZRange(search_from, search_to))
<QuerySet []>

But how to filter by all array fields?

MyModel.objects.filter(myfield__overlap__overlap=DateTimeTZRange(search_from, search_to))
...
FieldError: Unsupported lookup 'overlap' for ArrayField or join on the field not permitted.


MyModel.objects.filter(myfield__0_100000__overlap=DateTimeTZRange(search_from, search_to))
<QuerySet []>

Thanks for any help.

[user0008]

I answer to my own question. It can't be done :-) The solution needs raw SQL query + subquery (eg. using JOIN LATERAL: https://dba.stackexchange.com/questions/147129/selecting-only-overlapping-elements-from-array-of-ranges )