api: add new filters to get overlapping subscriptions (#71579)

chrono/api/views.py

@@ -2250,8 +2250,8 @@ agendas_events_check_status = MultipleAgendasEventsCheckStatus.as_view()
 
 
 class SubscriptionFilter(filters.FilterSet):
-    date_start = filters.DateFilter(lookup_expr='gte')
-    date_end = filters.DateFilter(lookup_expr='lt')
+    date_start = filters.DateFilter(method='do_nothing')
+    date_end = filters.DateFilter(method='do_nothing')
 
     class Meta:
         model = Subscription
@@ -2261,6 +2261,28 @@ class SubscriptionFilter(filters.FilterSet):
             'date_end',
         ]
 
+    def do_nothing(self, queryset, name, value):
+        return queryset
+
+    def filter_queryset(self, queryset):
+        queryset = super().filter_queryset(queryset)
+        overlaps = {k: self.form.cleaned_data[k] for k in ['date_start', 'date_end']}
+        if any(overlaps.values()):
+            if not all(overlaps.values()):
+                missing = [k for k, v in overlaps.items() if not v][0]
+                not_missing = [k for k, v in overlaps.items() if v][0]
+                raise ValidationError(
+                    {missing: _('This filter is required when using "%s" filter.') % not_missing}
+                )
+            queryset = queryset.extra(
+                where=["(date_start, date_end) OVERLAPS (%s, %s)"],
+                params=[
+                    self.form.cleaned_data['date_start'],
+                    self.form.cleaned_data['date_end'],
+                ],
+            )
+        return queryset
+
 
 class SubscriptionsAPI(ListAPIView):
     filter_backends = (filters.DjangoFilterBackend,)

tests/api/test_subscription.py

@@ -75,7 +75,7 @@ def test_api_list_subscription_filter_user_external_id(app, user):
     assert [d['id'] for d in resp.json['data']] == []
 
 
-def test_api_list_subscription_filter_date_start(app, user):
+def test_api_list_subscription_filter_date_overlaps(app, user):
     agenda = Agenda.objects.create(label='Foo bar', kind='events')
     subscription1 = Subscription.objects.create(
         agenda=agenda,
@@ -92,47 +92,59 @@ def test_api_list_subscription_filter_date_start(app, user):
 
     app.authorization = ('Basic', ('john.doe', 'password'))
 
-    resp = app.get('/api/agenda/%s/subscription/' % agenda.slug, params={'date_start': '2021-09-01'})
-    assert [d['id'] for d in resp.json['data']] == [subscription1.pk, subscription2.pk]
-    resp = app.get('/api/agenda/%s/subscription/' % agenda.slug, params={'date_start': '2021-09-02'})
-    assert [d['id'] for d in resp.json['data']] == [subscription2.pk]
-    resp = app.get('/api/agenda/%s/subscription/' % agenda.slug, params={'date_start': '2022-09-02'})
-    assert [d['id'] for d in resp.json['data']] == []
-
     resp = app.get(
-        '/api/agenda/%s/subscription/' % agenda.slug, params={'date_start': 'wrong-format'}, status=400
+        '/api/agenda/%s/subscription/' % agenda.slug,
+        params={'date_start': '2021-08-31', 'date_end': '2021-09-01'},
     )
-    assert resp.json['err_class'] == 'invalid filters'
-
-
-def test_api_list_subscription_filter_date_end(app, user):
-    agenda = Agenda.objects.create(label='Foo bar', kind='events')
-    subscription1 = Subscription.objects.create(
-        agenda=agenda,
-        user_external_id='xxx',
-        date_start=datetime.date(year=2021, month=9, day=1),
-        date_end=datetime.date(year=2021, month=10, day=1),
+    assert [d['id'] for d in resp.json['data']] == []
+    resp = app.get(
+        '/api/agenda/%s/subscription/' % agenda.slug,
+        params={'date_start': '2021-08-31', 'date_end': '2021-09-02'},
     )
-    subscription2 = Subscription.objects.create(
-        agenda=agenda,
-        user_external_id='xxx',
-        date_start=datetime.date(year=2022, month=9, day=1),
-        date_end=datetime.date(year=2022, month=10, day=1),
-    )
-
-    app.authorization = ('Basic', ('john.doe', 'password'))
-
-    resp = app.get('/api/agenda/%s/subscription/' % agenda.slug, params={'date_end': '2022-10-02'})
-    assert [d['id'] for d in resp.json['data']] == [subscription1.pk, subscription2.pk]
-    resp = app.get('/api/agenda/%s/subscription/' % agenda.slug, params={'date_end': '2021-10-02'})
     assert [d['id'] for d in resp.json['data']] == [subscription1.pk]
-    resp = app.get('/api/agenda/%s/subscription/' % agenda.slug, params={'date_end': '2021-10-01'})
+    resp = app.get(
+        '/api/agenda/%s/subscription/' % agenda.slug,
+        params={'date_start': '2021-09-02', 'date_end': '2021-09-30'},
+    )
+    assert [d['id'] for d in resp.json['data']] == [subscription1.pk]
+    resp = app.get(
+        '/api/agenda/%s/subscription/' % agenda.slug,
+        params={'date_start': '2021-09-30', 'date_end': '2021-10-01'},
+    )
+    assert [d['id'] for d in resp.json['data']] == [subscription1.pk]
+    resp = app.get(
+        '/api/agenda/%s/subscription/' % agenda.slug,
+        params={'date_start': '2021-10-01', 'date_end': '2021-10-02'},
+    )
     assert [d['id'] for d in resp.json['data']] == []
+    resp = app.get(
+        '/api/agenda/%s/subscription/' % agenda.slug,
+        params={'date_start': '2021-09-15', 'date_end': '2022-09-15'},
+    )
+    assert [d['id'] for d in resp.json['data']] == [subscription1.pk, subscription2.pk]
 
     resp = app.get(
-        '/api/agenda/%s/subscription/' % agenda.slug, params={'date_end': 'wrong-format'}, status=400
+        '/api/agenda/%s/subscription/' % agenda.slug,
+        params={'date_start': 'wrong-format', 'date_end': '2021-09-01'},
+        status=400,
     )
     assert resp.json['err_class'] == 'invalid filters'
+    resp = app.get(
+        '/api/agenda/%s/subscription/' % agenda.slug,
+        params={'date_start': '2021-08-31', 'date_end': 'wrong-format'},
+        status=400,
+    )
+    assert resp.json['err_class'] == 'invalid filters'
+    resp = app.get(
+        '/api/agenda/%s/subscription/' % agenda.slug, params={'date_start': '2021-08-31'}, status=400
+    )
+    assert resp.json['err_class'] == 'invalid filters'
+    assert resp.json['errors']['date_end'] == 'This filter is required when using "date_start" filter.'
+    resp = app.get(
+        '/api/agenda/%s/subscription/' % agenda.slug, params={'date_end': '2021-09-01'}, status=400
+    )
+    assert resp.json['err_class'] == 'invalid filters'
+    assert resp.json['errors']['date_start'] == 'This filter is required when using "date_end" filter.'
 
 
 def test_api_create_subscription(app, user):