CODEWITHSUNDEEP
django-models
Crag
Crag

Reputation: 1841

How can I use range filtering in a Django Subquery?

I'm trying to run (simplified):

reverses = Location.objects.filter(collect_time__range=(OuterRef('start_time'), OuterRef('stop_time'))).values('id')
mungeable_trips = Trip.objects.annotate(reverse__count=Count(Subquery(reverses))).filter(Q(reverse__count__gt=0)).all()

... and I'm getting:

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

I have other filter conditions with OuterRef that work - for comparison, here's the full subquery:

reverses = Location.objects.annotate(mod_reversed=F('reversed') % 2).filter(mod_reversed=1)\
            .filter(device=OuterRef('device'), person=OuterRef('mungee'), collect_time__range=(OuterRef('start_time'), OuterRef('stop_time')))

Removing just the range filter lets this run without error, but that filter is needed. How can I get this running?

Upvotes: 0

Views: 187

Answers (1)

Crag
Crag

Reputation: 1841

Looks like it works without __range, so instead of:

collect_time__range=(OuterRef('start_time'), OuterRef('stop_time'))

You can do:

collect_time__gte=OuterRef('start_time'), collect_time__lte=OuterRef('stop_time')

... bug in Django? I'm on 2.2.6.

I also worked around another issue with counting subqueries; I added this step:

count_reverses = reverses.annotate(c=Count('*')).values('c')

... and I'm annotating this:

reverse_count=Subquery(count_reverses, output_field=IntegerField())

Upvotes: 1

Related Questions