Simple gettoptaxa.

SimonGoring · SimonGoring · commit 10c1c194b615 · 2020-12-17T12:05:10.000-08:00
diff --git a/function/ti/getdatasettoptaxa.sql b/function/ti/getdatasettoptaxa.sql
@@ -2,35 +2,37 @@ CREATE OR REPLACE FUNCTION ti.getdatasettoptaxa(
   _datasetid int,
   _topx int,
   _grouptaxa CHARACTER VARYING DEFAULT NULL,
-  _alwaysshowtaxa CHARACTER VARYING[] DEFAULT NULL)
+  _alwaysshowtaxa CHARACTER VARYING DEFAULT NULL)
 RETURNS TABLE (id integer,
                taxonname character varying,
                ecologicalgroupid CHAR(4))
 LANGUAGE sql
 AS
 $function$
-WITH wholesum AS (
-  SELECT tx.taxonid,
-	       tx.taxonname,
-         ec.ecolgroupid,
-	       row_number() OVER (ORDER BY SUM(dt.value) DESC) AS index
-  FROM          ndb.samples AS sm
-  INNER JOIN       ndb.data AS dt  ON sm.sampleid = dt.sampleid
-  INNER JOIN  ndb.variables AS var ON dt.variableid = var.variableid
-  INNER JOIN       ndb.taxa AS tx  ON var.taxonid = tx.taxonid
-  INNER JOIN ndb.ecolgroups AS ec  ON tx.taxonid = ec.taxonid
-  WHERE var.variableelementid = ANY('{141,166}')
-    AND          sm.datasetid = _datasetid
-  GROUP BY ec.ecolsetid,
+
+  WITH wholesum AS (
+    SELECT tx.taxonid,
+  	       tx.taxonname,
            ec.ecolgroupid,
-           tx.taxonname,
-  		     tx.taxonid,
-           var.variableunitsid
-  ORDER BY SUM(dt.value) DESC)
+  	       row_number() OVER (ORDER BY SUM(dt.value) DESC) AS index
+    FROM          ndb.samples AS sm
+    INNER JOIN       ndb.data AS dt  ON sm.sampleid = dt.sampleid
+    INNER JOIN  ndb.variables AS var ON dt.variableid = var.variableid
+    INNER JOIN       ndb.taxa AS tx  ON var.taxonid = tx.taxonid
+    INNER JOIN ndb.ecolgroups AS ec  ON tx.taxonid = ec.taxonid
+    WHERE var.variableelementid = ANY('{141,166}')
+      AND          sm.datasetid = _datasetid
+    GROUP BY ec.ecolsetid,
+             ec.ecolgroupid,
+             tx.taxonname,
+    		     tx.taxonid,
+             var.variableunitsid
+    ORDER BY SUM(dt.value) DESC)
+
   SELECT taxonid AS id,
          taxonname AS taxon,
          ecolgroupid AS ecologicalgroupid
   FROM wholesum
   WHERE index <= _topx
-    OR taxonname = ANY(_alwaysshowtaxa)
+    OR taxonname = ANY(SELECT unnest(string_to_array(_datasettypeidlist,',')))
 $function$
