Finds: new SQL view to manage easily list of tretments (refs #3108)

1 files changed, 42 insertions, 0 deletions

diff --git a/archaeological_finds/models.py b/archaeological_finds/models.py
index b570d7b85..a5168eb0e 100644
--- a/archaeological_finds/models.py
+++ b/archaeological_finds/models.py
@@ -758,6 +758,48 @@ class Treatment(BaseHistorizedItem, OwnPerms):
                 basket.items.add(new)
 
 
+class FindTreaments(models.Model):
+    """
+    CREATE VIEW find_treatments_tree AS
+        WITH RECURSIVE rel_tree AS (
+          SELECT id AS find_id, upstream_treatment_id, downstream_treatment_id,
+              1 AS level,
+              array[downstream_treatment_id] AS path_info
+            FROM archaeological_finds_find
+            WHERE downstream_treatment_id is null
+          UNION ALL
+          SELECT c.id AS find_id, c.upstream_treatment_id,
+            c.downstream_treatment_id,
+            p.level + 1, p.path_info||c.downstream_treatment_id
+            FROM archaeological_finds_find c
+          JOIN rel_tree p
+            ON c.downstream_treatment_id = p.upstream_treatment_id
+        )
+        SELECT DISTINCT find_id, path_info, level
+          FROM rel_tree ORDER BY find_id;
+
+    CREATE VIEW find_treatments AS
+        SELECT DISTINCT find_id,
+            path_info[nb] AS treatment_id, level - nb + 1 AS treatment_nb
+          FROM (SELECT *, generate_subscripts(path_info, 1) AS nb
+                FROM find_treatments_tree) y
+         WHERE path_info[nb] is not NULL
+        ORDER BY find_id, treatment_id;
+    """
+    find = models.ForeignKey(Find, verbose_name=_(u"Find"))
+    treatment = models.ForeignKey(Treatment, verbose_name=_(u"Treatment"))
+    treatment_nb = models.IntegerField(
+        _(u"Digit of the treatment for this find"))
+
+    class Meta:
+        managed = False
+        db_table = 'find_treatments'
+
+    def __unicode__(self):
+        return u"{} - {} [{}]".format(
+            self.find, self.treatment, self.treatment_nb)
+
+
 class TreatmentSource(Source):
     class Meta:
         verbose_name = _(u"Treatment documentation")