Optimize tables query for operations and sites

4 files changed, 245 insertions, 11 deletions

diff --git a/archaeological_operations/migrations/0055_auto_20190521_1244.py b/archaeological_operations/migrations/0055_auto_20190521_1244.py
new file mode 100644
index 000000000..72ac0d70c
--- /dev/null
+++ b/archaeological_operations/migrations/0055_auto_20190521_1244.py
@@ -0,0 +1,165 @@
+# -*- coding: utf-8 -*-
+# Generated by Django 1.11.18 on 2019-05-21 12:44
+from __future__ import unicode_literals
+
+from django.db import migrations, models
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('archaeological_operations', '0054_auto_20190225_1637'),
+    ]
+
+    operations = [
+        migrations.AddField(
+            model_name='archaeologicalsite',
+            name='cached_periods',
+            field=models.TextField(blank=True, help_text='Generated automatically - do not edit', null=True, verbose_name='Cached periods label'),
+        ),
+        migrations.AddField(
+            model_name='archaeologicalsite',
+            name='cached_remains',
+            field=models.TextField(blank=True, help_text='Generated automatically - do not edit', null=True, verbose_name='Cached remains label'),
+        ),
+        migrations.AddField(
+            model_name='archaeologicalsite',
+            name='cached_towns_label',
+            field=models.TextField(blank=True, help_text='Generated automatically - do not edit', null=True, verbose_name='Cached town label'),
+        ),
+        migrations.AddField(
+            model_name='historicalarchaeologicalsite',
+            name='cached_periods',
+            field=models.TextField(blank=True, help_text='Generated automatically - do not edit', null=True, verbose_name='Cached periods label'),
+        ),
+        migrations.AddField(
+            model_name='historicalarchaeologicalsite',
+            name='cached_remains',
+            field=models.TextField(blank=True, help_text='Generated automatically - do not edit', null=True, verbose_name='Cached remains label'),
+        ),
+        migrations.AddField(
+            model_name='historicalarchaeologicalsite',
+            name='cached_towns_label',
+            field=models.TextField(blank=True, help_text='Generated automatically - do not edit', null=True, verbose_name='Cached town label'),
+        ),
+        migrations.AddField(
+            model_name='historicaloperation',
+            name='cached_periods',
+            field=models.TextField(blank=True, help_text='Generated automatically - do not edit', null=True, verbose_name='Cached periods label'),
+        ),
+        migrations.AddField(
+            model_name='historicaloperation',
+            name='cached_remains',
+            field=models.TextField(blank=True, help_text='Generated automatically - do not edit', null=True, verbose_name='Cached remains label'),
+        ),
+        migrations.AddField(
+            model_name='historicaloperation',
+            name='cached_towns_label',
+            field=models.TextField(blank=True, help_text='Generated automatically - do not edit', null=True, verbose_name='Cached town label'),
+        ),
+        migrations.AddField(
+            model_name='operation',
+            name='cached_periods',
+            field=models.TextField(blank=True, help_text='Generated automatically - do not edit', null=True, verbose_name='Cached periods label'),
+        ),
+        migrations.AddField(
+            model_name='operation',
+            name='cached_remains',
+            field=models.TextField(blank=True, help_text='Generated automatically - do not edit', null=True, verbose_name='Cached remains label'),
+        ),
+        migrations.AddField(
+            model_name='operation',
+            name='cached_towns_label',
+            field=models.TextField(blank=True, help_text='Generated automatically - do not edit', null=True, verbose_name='Cached town label'),
+        ),
+        migrations.AlterField(
+            model_name='acttype',
+            name='intented_to',
+            field=models.CharField(choices=[('F', 'Dossier'), ('O', 'Opération'), ('TF', 'Demande de traitement'), ('T', 'Traitement')], max_length=2, verbose_name='Destiné à'),
+        ),
+        migrations.AlterField(
+            model_name='archaeologicalsite',
+            name='multi_polygon_source',
+            field=models.CharField(blank=True, choices=[('T', 'Commune'), ('P', 'Précis'), ('M', 'Polygone')], max_length=1, null=True, verbose_name='Source du multi-polygone'),
+        ),
+        migrations.AlterField(
+            model_name='archaeologicalsite',
+            name='multi_polygon_source_item',
+            field=models.CharField(blank=True, max_length=100, null=True, verbose_name='Élément source du multi-polygone'),
+        ),
+        migrations.AlterField(
+            model_name='archaeologicalsite',
+            name='point_source',
+            field=models.CharField(blank=True, choices=[('T', 'Commune'), ('P', 'Précis'), ('M', 'Polygone')], max_length=1, null=True, verbose_name='Source du point'),
+        ),
+        migrations.AlterField(
+            model_name='archaeologicalsite',
+            name='point_source_item',
+            field=models.CharField(blank=True, max_length=100, null=True, verbose_name='Élément source du point'),
+        ),
+        migrations.AlterField(
+            model_name='historicalarchaeologicalsite',
+            name='multi_polygon_source',
+            field=models.CharField(blank=True, choices=[('T', 'Commune'), ('P', 'Précis'), ('M', 'Polygone')], max_length=1, null=True, verbose_name='Source du multi-polygone'),
+        ),
+        migrations.AlterField(
+            model_name='historicalarchaeologicalsite',
+            name='multi_polygon_source_item',
+            field=models.CharField(blank=True, max_length=100, null=True, verbose_name='Élément source du multi-polygone'),
+        ),
+        migrations.AlterField(
+            model_name='historicalarchaeologicalsite',
+            name='point_source',
+            field=models.CharField(blank=True, choices=[('T', 'Commune'), ('P', 'Précis'), ('M', 'Polygone')], max_length=1, null=True, verbose_name='Source du point'),
+        ),
+        migrations.AlterField(
+            model_name='historicalarchaeologicalsite',
+            name='point_source_item',
+            field=models.CharField(blank=True, max_length=100, null=True, verbose_name='Élément source du point'),
+        ),
+        migrations.AlterField(
+            model_name='historicaloperation',
+            name='multi_polygon_source',
+            field=models.CharField(blank=True, choices=[('T', 'Commune'), ('P', 'Précis'), ('M', 'Polygone')], max_length=1, null=True, verbose_name='Source du multi-polygone'),
+        ),
+        migrations.AlterField(
+            model_name='historicaloperation',
+            name='multi_polygon_source_item',
+            field=models.CharField(blank=True, max_length=100, null=True, verbose_name='Élément source du multi-polygone'),
+        ),
+        migrations.AlterField(
+            model_name='historicaloperation',
+            name='point_source',
+            field=models.CharField(blank=True, choices=[('T', 'Commune'), ('P', 'Précis'), ('M', 'Polygone')], max_length=1, null=True, verbose_name='Source du point'),
+        ),
+        migrations.AlterField(
+            model_name='historicaloperation',
+            name='point_source_item',
+            field=models.CharField(blank=True, max_length=100, null=True, verbose_name='Élément source du point'),
+        ),
+        migrations.AlterField(
+            model_name='operation',
+            name='multi_polygon_source',
+            field=models.CharField(blank=True, choices=[('T', 'Commune'), ('P', 'Précis'), ('M', 'Polygone')], max_length=1, null=True, verbose_name='Source du multi-polygone'),
+        ),
+        migrations.AlterField(
+            model_name='operation',
+            name='multi_polygon_source_item',
+            field=models.CharField(blank=True, max_length=100, null=True, verbose_name='Élément source du multi-polygone'),
+        ),
+        migrations.AlterField(
+            model_name='operation',
+            name='point_source',
+            field=models.CharField(blank=True, choices=[('T', 'Commune'), ('P', 'Précis'), ('M', 'Polygone')], max_length=1, null=True, verbose_name='Source du point'),
+        ),
+        migrations.AlterField(
+            model_name='operation',
+            name='point_source_item',
+            field=models.CharField(blank=True, max_length=100, null=True, verbose_name='Élément source du point'),
+        ),
+        migrations.AlterField(
+            model_name='relationtype',
+            name='logical_relation',
+            field=models.CharField(blank=True, choices=[('above', 'Au-dessus'), ('bellow', 'En dessous'), ('equal', 'Égal')], max_length=10, null=True, verbose_name='Relation logique'),
+        ),
+    ]
diff --git a/archaeological_operations/models.py b/archaeological_operations/models.py
index 6b2cf4ec3..5bc730018 100644
--- a/archaeological_operations/models.py
+++ b/archaeological_operations/models.py
@@ -114,7 +114,19 @@ class ArchaeologicalSite(DocumentItem, BaseHistorizedItem, QRCodeItem,
     APP = "archaeological-operations"
     MODEL = "archaeological-site"
     SHOW_URL = 'show-site'
-    TABLE_COLS = ['reference', 'name', 'towns_label', 'periods', 'remains']
+    TABLE_COLS = ['reference', 'name', 'cached_towns_label',
+                  'cached_periods', 'cached_remains']
+    NEW_QUERY_ENGINE = True
+    COL_LABELS = {
+        'cached_towns_label': _("Towns"),
+        'cached_periods': _("Periods"),
+        'cached_remains': _("Remains"),
+    }
+    EXTRA_REQUEST_KEYS = {
+        'cached_towns_label': 'cached_towns_label',
+        'cached_periods': 'cached_periods',
+        'cached_remains': 'remains',
+    }
     LONG_SLUG = 'archaeologicalsite'
 
     STATISTIC_MODALITIES_OPTIONS = OrderedDict([
@@ -149,8 +161,6 @@ class ArchaeologicalSite(DocumentItem, BaseHistorizedItem, QRCodeItem,
 
     DATED_FIELDS = ['sinking_date']
 
-    COL_LABELS = {'towns_label': _("Towns")}
-
     EXTRA_REQUEST_KEYS = {
         'towns_label': 'towns',
         'collaborators__pk': 'collaborators__pk',  # dynamic_table_documents
@@ -242,6 +252,8 @@ class ArchaeologicalSite(DocumentItem, BaseHistorizedItem, QRCodeItem,
         ('operation', 'operations__pk'),
     ]
     HISTORICAL_M2M = ['periods', 'remains', 'towns']
+    CACHED_LABELS = ['cached_label', 'cached_towns_label', 'cached_periods',
+                     'cached_remains']
 
     # objects = SiteManager()
 
@@ -293,6 +305,18 @@ class ArchaeologicalSite(DocumentItem, BaseHistorizedItem, QRCodeItem,
         verbose_name=_("Main image"), blank=True, null=True)
     cached_label = models.TextField(_("Cached name"),
                                     null=True, blank=True, db_index=True)
+    cached_towns_label = models.TextField(
+        _("Cached town label"), blank=True, null=True,
+        help_text=_("Generated automatically - do not edit")
+    )
+    cached_periods =  models.TextField(
+        _("Cached periods label"), blank=True, null=True,
+        help_text=_("Generated automatically - do not edit")
+    )
+    cached_remains =  models.TextField(
+        _("Cached remains label"), blank=True, null=True,
+        help_text=_("Generated automatically - do not edit")
+    )
 
     history = HistoricalRecords(bases=[HistoryModel])
 
@@ -379,6 +403,15 @@ class ArchaeologicalSite(DocumentItem, BaseHistorizedItem, QRCodeItem,
                 ]))
         return name
 
+    def _generate_cached_towns_label(self):
+        return self.towns_label() or "-"
+
+    def _generate_cached_remains(self):
+        return " & ".join([str(remain) for remain in self.remains.all()]) or "-"
+
+    def _generate_cached_periods(self):
+        return " & ".join([str(period) for period in self.periods.all()]) or "-"
+
     # def natural_key(self):
     #     return (self.reference, )
 
@@ -390,7 +423,7 @@ class ArchaeologicalSite(DocumentItem, BaseHistorizedItem, QRCodeItem,
         return [town.label_with_areas for town in self.towns.all()]
 
     def towns_label(self):
-        return " - ".join(self.towns_codes())
+        return " & ".join(self.towns_codes())
 
     def get_town_centroid(self):
         q = self.towns.filter(center__isnull=False).annotate(
@@ -535,9 +568,10 @@ class Operation(ClosedItem, DocumentItem, BaseHistorizedItem, QRCodeItem,
     APP = "archaeological-operations"
     MODEL = "operation"
     SHOW_URL = 'show-operation'
-    TABLE_COLS = ['code_patriarche', 'year', 'towns_label', 'common_name',
-                  'operation_type', 'start_date', 'excavation_end_date',
-                  'remains']
+    TABLE_COLS = ['code_patriarche', 'year', 'cached_towns_label',
+                  'common_name', 'operation_type__label', 'start_date',
+                  'excavation_end_date', 'cached_remains']
+    NEW_QUERY_ENGINE = True
     # statistics
     STATISTIC_MODALITIES_OPTIONS = OrderedDict([
         ('year', _("Year")),
@@ -563,6 +597,7 @@ class Operation(ClosedItem, DocumentItem, BaseHistorizedItem, QRCodeItem,
         'documentation_deadline__gte', 'finds_deadline__lte',
         'finds_deadline__gte']
     EXTRA_REQUEST_KEYS = {
+        'operation_type__label': 'operation_type__label',
         'common_name': 'common_name__icontains',
         'cached_label': 'cached_label__icontains',
         'comment': 'comment__icontains',
@@ -615,6 +650,10 @@ class Operation(ClosedItem, DocumentItem, BaseHistorizedItem, QRCodeItem,
         'archaeological_sites__reference':
         _("Archaeological sites (reference)"),
         'towns_label': _("Towns"),
+        'operation_type__label': _("Operation type"),
+        'cached_towns_label': _("Towns"),
+        'cached_periods': _("Periods"),
+        'cached_remains': _("Remains"),
     }
     BASE_SEARCH_VECTORS = [
         SearchVectorConfig("abstract", "local"),
@@ -657,6 +696,8 @@ class Operation(ClosedItem, DocumentItem, BaseHistorizedItem, QRCodeItem,
             )
         },
     }
+    CACHED_LABELS = ['cached_label', 'cached_towns_label', 'cached_periods',
+                     'cached_remains']
     objects = OperationManager()
 
     # alternative names of fields for searches
@@ -967,6 +1008,18 @@ class Operation(ClosedItem, DocumentItem, BaseHistorizedItem, QRCodeItem,
                                               blank=True, null=True)
     name_of_the_protagonist = models.TextField(_("Name of the protagonist"),
                                                blank=True, null=True)
+    cached_towns_label = models.TextField(
+        _("Cached town label"), blank=True, null=True,
+        help_text=_("Generated automatically - do not edit")
+    )
+    cached_periods =  models.TextField(
+        _("Cached periods label"), blank=True, null=True,
+        help_text=_("Generated automatically - do not edit")
+    )
+    cached_remains =  models.TextField(
+        _("Cached remains label"), blank=True, null=True,
+        help_text=_("Generated automatically - do not edit")
+    )
 
     history = HistoricalRecords(bases=[HistoryModel])
 
@@ -1113,6 +1166,15 @@ class Operation(ClosedItem, DocumentItem, BaseHistorizedItem, QRCodeItem,
         cached_label = settings.JOINT.join(items)
         return cached_label
 
+    def _generate_cached_towns_label(self):
+        return self.towns_label() or "-"
+
+    def _generate_cached_remains(self):
+        return " & ".join([str(remain) for remain in self.remains.all()]) or "-"
+
+    def _generate_cached_periods(self):
+        return " & ".join([str(period) for period in self.periods.all()]) or "-"
+
     def _get_associated_cached_labels(self):
         return list(self.context_record.all())
 
diff --git a/ishtar_common/static/js/ishtar.js b/ishtar_common/static/js/ishtar.js
index 40592f0db..249d06907 100644
--- a/ishtar_common/static/js/ishtar.js
+++ b/ishtar_common/static/js/ishtar.js
@@ -1098,7 +1098,8 @@ var get_hover_div = function(table_cols, data){
             } else if (hover){
                 hover += " - ";
             }
-            hover += data[key].replace(/"/g, '"');
+            if (!data[key]) data[key] = "";
+            hover += String(data[key]).replace(/"/g, '"');
         }
     }
     return hover;
diff --git a/ishtar_common/views_item.py b/ishtar_common/views_item.py
index 000b17a33..80975eb0a 100644
--- a/ishtar_common/views_item.py
+++ b/ishtar_common/views_item.py
@@ -1101,7 +1101,7 @@ def _format_geojson(rows, link_template):
     return data
 
 
-def _get_data_from_query(items, query_table_cols, request, extra_request_keys,
+def _get_data_from_query(items, query_table_cols, extra_request_keys,
                          point_field=None):
     for query_keys in query_table_cols:
         if not isinstance(query_keys, (tuple, list)):
@@ -1615,7 +1615,6 @@ def get_item(model, func_name, default_name, extra_request_keys=None,
 
         for extra in extras:
             items = items.extra(**extra)
-
         items = items.distinct()
 
         items_nb = items.values('id').aggregate(Count('id'))['id__count']
@@ -1784,8 +1783,11 @@ def get_item(model, func_name, default_name, extra_request_keys=None,
         if data_type == 'json-map':
             point_field = query_table_cols.pop()
             datas = _get_data_from_query(
-                items, query_table_cols, request, my_extra_request_keys,
+                items, query_table_cols, my_extra_request_keys,
                 point_field=point_field)
+        elif getattr(model, "NEW_QUERY_ENGINE", False):
+            datas = _get_data_from_query(
+                items, query_table_cols, my_extra_request_keys)
         else:
             datas = _get_data_from_query_old(
                 items, query_table_cols, request, my_extra_request_keys,
@@ -1851,6 +1853,10 @@ def get_item(model, func_name, default_name, extra_request_keys=None,
                         k = "__".join(tab_cols)
                         if hasattr(model, 'COL_LINK') and k in model.COL_LINK:
                             value = link_ext_template.format(value, value)
+                        if isinstance(value, datetime.date):
+                            value = value.strftime('%Y-%m-%d')
+                        if isinstance(value, datetime.datetime):
+                            value = value.strftime('%Y-%m-%d %H:%M:%S')
                         res[k] = value
                 if full == 'shortcut':
                     if 'cached_label' in res: