🐛 table count - fix bad number of items count on finds with many associated context record (refs #5898)

3 files changed, 81 insertions, 30 deletions

diff --git a/archaeological_finds/models_finds.py b/archaeological_finds/models_finds.py
index 375cfc366..aa92a93c4 100644
--- a/archaeological_finds/models_finds.py
+++ b/archaeological_finds/models_finds.py
@@ -1137,6 +1137,12 @@ class Find(
         "base_finds__context_record__parcel",
     ]
     NEW_QUERY_ENGINE = True
+    # if these fields are in the search query columns - add these keys for total count
+    QUERY_DISTINCT_COUNT = {
+        "base_finds__context_record__label": "base_finds__context_record__pk",
+        "base_finds__context_record__operation__code_patriarche": "base_finds__context_record__operation__pk",
+        "base_finds__context_record__operation__common_name": "base_finds__context_record__operation__pk",
+    }
     COL_LABELS = {
         "base_finds__context_record__label": _("Context record"),
         "base_finds__cache_short_id": _("Base find - Short ID"),
diff --git a/archaeological_finds/tests.py b/archaeological_finds/tests.py
index c98da7951..60dcb5a5d 100644
--- a/archaeological_finds/tests.py
+++ b/archaeological_finds/tests.py
@@ -1367,6 +1367,35 @@ class FindSearchTest(FindInit, TestCase, SearchText):
         User.objects.create_superuser(self.username, "myemail@test.com", self.password)
         self.client = Client()
 
+    def test_item_count(self):
+        find_1 = self.finds[0]
+        ope2 = self.create_operation(self.get_default_user())[-1]
+        context_record2 = self.create_context_record({"operation": ope2})[-1]
+        base_find_2 = self.create_finds(data_base={"context_record": context_record2})[1][-1]
+
+        c = Client()
+        # no result when no authentication
+        response = c.get(reverse("get-find"), {})
+        self.assertRedirects(response, "/")
+
+        c.login(username=self.username, password=self.password)
+
+        # classic search
+        response = c.get(reverse("get-find"), {})
+        self.assertEqual(response.status_code, 200)
+        content = response.content.decode()
+        res = json.loads(content)
+        self.assertEqual(res["recordsTotal"], len(self.finds))
+
+        # add the base find of find_2 to find_1 - grouping or other like treatment
+        find_1.base_finds.add(base_find_2)
+        # on find search there is column with context record that duplicate the line
+        response = c.get(reverse("get-find"), {})
+        self.assertEqual(response.status_code, 200)
+        content = response.content.decode()
+        res = json.loads(content)
+        self.assertEqual(res["recordsTotal"], len(self.finds) + 1)
+
     def test_material_type_hierarchic_search(self):
         find = self.finds[0]
         c = Client()
diff --git a/ishtar_common/views_item.py b/ishtar_common/views_item.py
index 6fd59624a..1a35e766e 100644
--- a/ishtar_common/views_item.py
+++ b/ishtar_common/views_item.py
@@ -1801,6 +1801,38 @@ def _get_json_stats(
     return HttpResponse(data, content_type="application/json")
 
 
+def _get_table_cols(data_type, own_table_cols, full, model):
+    # list of table cols depending on configuration and data send
+    if data_type == "json-map":
+        return []  # only pk for map
+    if own_table_cols:
+        table_cols = own_table_cols
+    else:
+        if full:
+            table_cols = [
+                field.name
+                for field in model._meta.fields
+                if field.name not in PRIVATE_FIELDS
+            ]
+            table_cols += [
+                field.name
+                for field in model._meta.many_to_many
+                if field.name not in PRIVATE_FIELDS
+            ]
+            if hasattr(model, "EXTRA_FULL_FIELDS"):
+                table_cols += model.EXTRA_FULL_FIELDS
+        else:
+            tb_key = (getattr(model, "SLUG", None), "TABLE_COLS")
+            if tb_key in settings.TABLE_COLS:
+                table_cols = settings.TABLE_COLS[tb_key]
+            else:
+                table_cols = model.TABLE_COLS
+    if callable(table_cols):
+        table_cols = table_cols()
+    table_cols = list(table_cols)
+    return table_cols
+
+
 DEFAULT_ROW_NUMBER = 10
 # length is used by ajax DataTables requests
 EXCLUDED_FIELDS = ["length"]
@@ -2288,9 +2320,21 @@ def get_item(
             return items
 
         items = items.distinct()
+        table_cols = _get_table_cols(data_type, own_table_cols, full, model)
 
+        count_values = ["pk"]
+        query_distinct_count = getattr(model, "QUERY_DISTINCT_COUNT", None)
+        if query_distinct_count:
+            for k, v in query_distinct_count.items():
+                if v in count_values:
+                    continue
+                for col in table_cols:
+                    if col.startswith(k):
+                        count_values.append(v)
+                        break
         try:
-            items_nb = items.values("pk").aggregate(Count("pk"))["pk__count"] or 0
+            q = items.values(*count_values)
+            items_nb = q.count() or 0
         except ProgrammingError:
             items_nb = 0
         if count:
@@ -2300,35 +2344,7 @@ def get_item(
         if search_vector:  # for serialization
             dct["search_vector"] = search_vector
 
-        # table cols
-        if own_table_cols:
-            table_cols = own_table_cols
-        else:
-            if full:
-                table_cols = [
-                    field.name
-                    for field in model._meta.fields
-                    if field.name not in PRIVATE_FIELDS
-                ]
-                table_cols += [
-                    field.name
-                    for field in model._meta.many_to_many
-                    if field.name not in PRIVATE_FIELDS
-                ]
-                if hasattr(model, "EXTRA_FULL_FIELDS"):
-                    table_cols += model.EXTRA_FULL_FIELDS
-            else:
-                tb_key = (getattr(model, "SLUG", None), "TABLE_COLS")
-                if tb_key in settings.TABLE_COLS:
-                    table_cols = settings.TABLE_COLS[tb_key]
-                else:
-                    table_cols = model.TABLE_COLS
-        if callable(table_cols):
-            table_cols = table_cols()
-        table_cols = list(table_cols)
-        if data_type == "json-map":
-            table_cols = []  # only pk for map
-        elif data_type == "json-stats":
+        if data_type == "json-stats":
             stats_modality_1 = request_items.get("stats_modality_1", None)
             stats_modality_2 = request_items.get("stats_modality_2", None)
             if (