summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorFrédéric Péters <fpeters@entrouvert.com>2019-12-24 09:14:55 (GMT)
committerFrédéric Péters <fpeters@entrouvert.com>2019-12-25 08:01:32 (GMT)
commit01c784f4ee07a96dc07e00e1c9a175c30a9f4f57 (patch)
treec28ec736657c217f57130a092d577cab6da1d1f0
parentdaffa22e304fa0407ac049070688a92d04e4dd7e (diff)
downloadchrono-wip/38699-reduce-query-count.zip
chrono-wip/38699-reduce-query-count.tar.gz
chrono-wip/38699-reduce-query-count.tar.bz2
manager: reduce number of queries when listing events (#38699)wip/38699-reduce-query-count
-rw-r--r--chrono/agendas/models.py38
-rw-r--r--chrono/manager/templates/chrono/manager_events_agenda_month_view.html10
-rw-r--r--chrono/manager/templates/chrono/manager_events_agenda_settings.html12
-rw-r--r--chrono/manager/views.py9
-rw-r--r--tests/test_agendas.py27
5 files changed, 84 insertions, 12 deletions
diff --git a/chrono/agendas/models.py b/chrono/agendas/models.py
index e4e8500..50ec863 100644
--- a/chrono/agendas/models.py
+++ b/chrono/agendas/models.py
@@ -20,11 +20,12 @@ import math
import requests
import vobject
+import django
from django.conf import settings
from django.contrib.auth.models import Group
from django.core.exceptions import ValidationError
from django.db import models, transaction
-from django.db.models import Q
+from django.db.models import Count, Q, Case, When
from django.urls import reverse
from django.utils.dates import WEEKDAYS
from django.utils.encoding import force_text
@@ -358,6 +359,41 @@ class Event(models.Model):
return False
return True
+ @staticmethod
+ def annotate_queryset(qs):
+ if django.VERSION < (2, 0):
+ return qs.annotate(
+ booked_places_count=Count(
+ Case(
+ When(
+ booking__cancellation_datetime__isnull=True,
+ booking__in_waiting_list=False,
+ then='booking',
+ )
+ )
+ ),
+ waiting_list_count=Count(
+ Case(
+ When(
+ booking__cancellation_datetime__isnull=True,
+ booking__in_waiting_list=True,
+ then='booking',
+ )
+ )
+ ),
+ )
+ else:
+ return qs.annotate(
+ booked_places_count=Count(
+ 'booking',
+ filter=Q(booking__cancellation_datetime__isnull=True, booking__in_waiting_list=False),
+ ),
+ waiting_list_count=Count(
+ 'booking',
+ filter=Q(booking__cancellation_datetime__isnull=True, booking__in_waiting_list=True),
+ ),
+ )
+
@property
def booked_places(self):
return self.booking_set.filter(cancellation_datetime__isnull=True, in_waiting_list=False).count()
diff --git a/chrono/manager/templates/chrono/manager_events_agenda_month_view.html b/chrono/manager/templates/chrono/manager_events_agenda_month_view.html
index a64e7ad..f419a93 100644
--- a/chrono/manager/templates/chrono/manager_events_agenda_month_view.html
+++ b/chrono/manager/templates/chrono/manager_events_agenda_month_view.html
@@ -8,13 +8,13 @@
{% if object_list %}
<ul class="objects-list single-links">
{% for event in object_list %}
- <li class="{% if event.booked_places > event.places %}overbooking{% endif %}
+ <li class="{% if event.booked_places_count > event.places %}overbooking{% endif %}
{% if event.full %}full{% endif %}
{% if not event.in_bookable_period %}not-{% endif %}bookable"
{% if event.places %}
- data-total="{{event.places}}" data-booked="{{event.booked_places}}"
+ data-total="{{event.places}}" data-booked="{{event.booked_places_count}}"
{% elif event.waiting_list_places %}
- data-total="{{event.waiting_list_places}}" data-booked="{{event.waiting_list}}"
+ data-total="{{event.waiting_list_places}}" data-booked="{{event.waiting_list_count}}"
{% endif %}
><a href="{% url 'chrono-manager-event-view' pk=agenda.id event_pk=event.id %}">
{% if event.label %}{{event.label}} / {% endif %}
@@ -22,11 +22,11 @@
{% if event.full %}/ <span class="full">{% trans "full" %}</span>{% endif %}
(
{% if event.places %}
- {% blocktrans with places=event.places booked_places=event.booked_places %}{{ places }} places, {{ booked_places }} booked places{% endblocktrans %}
+ {% blocktrans with places=event.places booked_places=event.booked_places_count %}{{ places }} places, {{ booked_places }} booked places{% endblocktrans %}
{% endif %}
{% if event.places and event.waiting_list_places %} / {% endif %}
{% if event.waiting_list_places %}
- {% blocktrans with places=event.waiting_list_places waiting_places=event.waiting_list %}
+ {% blocktrans with places=event.waiting_list_places waiting_places=event.waiting_list_count %}
{{waiting_places}} on {{ places }} in waiting list
{% endblocktrans %}
{% endif %}
diff --git a/chrono/manager/templates/chrono/manager_events_agenda_settings.html b/chrono/manager/templates/chrono/manager_events_agenda_settings.html
index 51461e6..a541a3d 100644
--- a/chrono/manager/templates/chrono/manager_events_agenda_settings.html
+++ b/chrono/manager/templates/chrono/manager_events_agenda_settings.html
@@ -13,14 +13,14 @@
<div>
{% if object.event_set.count %}
<ul class="objects-list single-links">
- {% for event in object.event_set.all %}
- <li class="{% if event.booked_places > event.places %}overbooking{% endif %}
+ {% for event in view.get_events %}
+ <li class="{% if event.booked_places_count > event.places %}overbooking{% endif %}
{% if event.full %}full{% endif %}
{% if not event.in_bookable_period %}not-{% endif %}bookable"
{% if event.places %}
- data-total="{{event.places}}" data-booked="{{event.booked_places}}"
+ data-total="{{event.places}}" data-booked="{{event.booked_places_count}}"
{% elif event.waiting_list_places %}
- data-total="{{event.waiting_list_places}}" data-booked="{{event.waiting_list}}"
+ data-total="{{event.waiting_list_places}}" data-booked="{{event.waiting_list_count}}"
{% endif %}
><a rel="popup" href="{% url 'chrono-manager-event-edit' pk=agenda.id event_pk=event.id %}?next=settings">
{% if event.label %}{{event.label}} / {% endif %}
@@ -28,11 +28,11 @@
{% if event.full %}/ <span class="full">{% trans "full" %}</span>{% endif %}
(
{% if event.places %}
- {% blocktrans with places=event.places booked_places=event.booked_places %}{{ places }} places, {{ booked_places }} booked places{% endblocktrans %}
+ {% blocktrans with places=event.places booked_places=event.booked_places_count %}{{ places }} places, {{ booked_places }} booked places{% endblocktrans %}
{% endif %}
{% if event.places and event.waiting_list_places %} / {% endif %}
{% if event.waiting_list_places %}
- {% blocktrans with places=event.waiting_list_places waiting_places=event.waiting_list %}
+ {% blocktrans with places=event.waiting_list_places waiting_places=event.waiting_list_count %}
{{waiting_places}} on {{ places }} in waiting list
{% endblocktrans %}
{% endif %}
diff --git a/chrono/manager/views.py b/chrono/manager/views.py
index 2096909..f09c1f1 100644
--- a/chrono/manager/views.py
+++ b/chrono/manager/views.py
@@ -421,6 +421,12 @@ agenda_day_view = AgendaDayView.as_view()
class AgendaMonthView(AgendaDateView, MonthArchiveView):
+ def get_queryset(self):
+ qs = super(AgendaMonthView, self).get_queryset()
+ if self.agenda.kind == 'meetings':
+ return qs
+ return Event.annotate_queryset(qs)
+
def get_template_names(self):
return ['chrono/manager_%s_agenda_month_view.html' % self.agenda.kind]
@@ -618,6 +624,9 @@ class ManagedDeskSubobjectMixin(object):
class AgendaSettings(ManagedAgendaMixin, DetailView):
model = Agenda
+ def get_events(self):
+ return Event.annotate_queryset(Event.objects.filter(agenda=self.agenda).select_related('agenda'))
+
def get_template_names(self):
return ['chrono/manager_%s_agenda_settings.html' % self.agenda.kind]
diff --git a/tests/test_agendas.py b/tests/test_agendas.py
index 1a74bd7..8b57585 100644
--- a/tests/test_agendas.py
+++ b/tests/test_agendas.py
@@ -455,3 +455,30 @@ def test_management_role_deletion():
Agenda.objects.get(id=agenda.id).view_role is None
Agenda.objects.get(id=agenda.id).edit_role is None
+
+
+def test_event_bookings_annotation():
+ agenda = Agenda(label='test', kind='events')
+ agenda.save()
+ event = Event(start_datetime=now(), label='foo', places=10, waiting_list_places=10, agenda=agenda)
+ event.save()
+ event2 = Event(start_datetime=now(), label='bar', places=10, waiting_list_places=10, agenda=agenda)
+ event2.save()
+
+ Booking(event=event).save()
+ Booking(event=event).save()
+ Booking(event=event, cancellation_datetime=now()).save()
+ Booking(event=event, in_waiting_list=True).save()
+ Booking(event=event, in_waiting_list=True, cancellation_datetime=now()).save()
+
+ Booking(event=event2).save()
+ Booking(event=event2).save()
+ Booking(event=event2).save()
+
+ for event in Event.annotate_queryset(Event.objects.filter(agenda=agenda)):
+ if event.label == 'foo':
+ assert event.booked_places_count == 2
+ assert event.waiting_list_count == 1
+ elif event.label == 'bar':
+ assert event.booked_places_count == 3
+ assert event.waiting_list_count == 0