Re: Selecting overlapping elements from ArrayField of DateTimeRangeField

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 )


W dniu czwartek, 18 maja 2017 12:58:35 UTC+2 użytkownik user0008 napisał:

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.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/dbce520d-8b10-4497-84e3-9da0cb1b056c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.