liquid_feedback_core

changeset 315:3e450a518197

Update script for "harmonic_weight" feature
author jbe
date Fri Feb 01 20:14:43 2013 +0100 (2013-02-01)
parents 5289d62c028c
children 727926e290e7
files update/core-update.v2.1.0-v2.1.1.sql
line diff
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v2.1.0-v2.1.1.sql	Fri Feb 01 20:14:43 2013 +0100
     1.3 @@ -0,0 +1,412 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 +  SELECT * FROM (VALUES ('2.1.1', 2, 1, 1))
     1.8 +  AS "subquery"("string", "major", "minor", "revision");
     1.9 +
    1.10 +ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
    1.11 +COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much';
    1.12 +
    1.13 +ALTER TABLE "suggestion" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
    1.14 +COMMENT ON COLUMN "suggestion"."harmonic_weight" IS 'Indicates the relevancy of the suggestion, calculated from the supporters (positive "degree") of the suggestion weighted with the harmonic series to avoid a large number of clones affecting other suggestion''s sortings position too much';
    1.15 +
    1.16 +CREATE VIEW "remaining_harmonic_supporter_weight" AS
    1.17 +  SELECT
    1.18 +    "direct_interest_snapshot"."issue_id",
    1.19 +    "direct_interest_snapshot"."event",
    1.20 +    "direct_interest_snapshot"."member_id",
    1.21 +    "direct_interest_snapshot"."weight" AS "weight_num",
    1.22 +    count("initiative"."id") AS "weight_den"
    1.23 +  FROM "issue"
    1.24 +  JOIN "direct_interest_snapshot"
    1.25 +    ON "issue"."id" = "direct_interest_snapshot"."issue_id"
    1.26 +    AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
    1.27 +  JOIN "direct_supporter_snapshot"
    1.28 +    ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id"
    1.29 +    AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
    1.30 +    AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
    1.31 +  JOIN "initiative"
    1.32 +    ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
    1.33 +    AND "initiative"."harmonic_weight" ISNULL
    1.34 +  GROUP BY
    1.35 +    "direct_interest_snapshot"."issue_id",
    1.36 +    "direct_interest_snapshot"."event",
    1.37 +    "direct_interest_snapshot"."member_id",
    1.38 +    "direct_interest_snapshot"."weight";
    1.39 +
    1.40 +COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
    1.41 +
    1.42 +CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
    1.43 +  SELECT
    1.44 +    "initiative"."issue_id",
    1.45 +    "initiative"."id" AS "initiative_id",
    1.46 +    sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
    1.47 +    "remaining_harmonic_supporter_weight"."weight_den"
    1.48 +  FROM "remaining_harmonic_supporter_weight"
    1.49 +  JOIN "direct_supporter_snapshot"
    1.50 +    ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id"
    1.51 +    AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
    1.52 +    AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
    1.53 +  JOIN "initiative"
    1.54 +    ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
    1.55 +    AND "initiative"."harmonic_weight" ISNULL
    1.56 +  GROUP BY
    1.57 +    "initiative"."issue_id",
    1.58 +    "initiative"."id",
    1.59 +    "remaining_harmonic_supporter_weight"."weight_den";
    1.60 +
    1.61 +COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
    1.62 +
    1.63 +CREATE FUNCTION "set_harmonic_initiative_weights"
    1.64 +  ( "issue_id_p" "issue"."id"%TYPE )
    1.65 +  RETURNS VOID
    1.66 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.67 +    DECLARE
    1.68 +      "weight_row"   "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
    1.69 +      "i"            INT4;
    1.70 +      "count_v"      INT4;
    1.71 +      "summand_v"    FLOAT;
    1.72 +      "id_ary"       INT4[];
    1.73 +      "weight_ary"   FLOAT[];
    1.74 +      "min_weight_v" FLOAT;
    1.75 +    BEGIN
    1.76 +      UPDATE "initiative" SET "harmonic_weight" = NULL
    1.77 +        WHERE "issue_id" = "issue_id_p";
    1.78 +      LOOP
    1.79 +        "min_weight_v" := NULL;
    1.80 +        "i" := 0;
    1.81 +        "count_v" := 0;
    1.82 +        FOR "weight_row" IN
    1.83 +          SELECT * FROM "remaining_harmonic_initiative_weight_summands"
    1.84 +          WHERE "issue_id" = "issue_id_p"
    1.85 +          ORDER BY "initiative_id" DESC, "weight_den" DESC
    1.86 +          -- NOTE: latest initiatives treated worse
    1.87 +        LOOP
    1.88 +          "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
    1.89 +          IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
    1.90 +            "i" := "i" + 1;
    1.91 +            "count_v" := "i";
    1.92 +            "id_ary"["i"] := "weight_row"."initiative_id";
    1.93 +            "weight_ary"["i"] := "summand_v";
    1.94 +          ELSE
    1.95 +            "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
    1.96 +          END IF;
    1.97 +        END LOOP;
    1.98 +        EXIT WHEN "count_v" = 0;
    1.99 +        "i" := 1;
   1.100 +        LOOP
   1.101 +          "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
   1.102 +          IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
   1.103 +            "min_weight_v" := "weight_ary"["i"];
   1.104 +          END IF;
   1.105 +          "i" := "i" + 1;
   1.106 +          EXIT WHEN "i" > "count_v";
   1.107 +        END LOOP;
   1.108 +        "i" := 1;
   1.109 +        LOOP
   1.110 +          IF "weight_ary"["i"] = "min_weight_v" THEN
   1.111 +            UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
   1.112 +              WHERE "id" = "id_ary"["i"];
   1.113 +            EXIT;
   1.114 +          END IF;
   1.115 +          "i" := "i" + 1;
   1.116 +        END LOOP;
   1.117 +      END LOOP;
   1.118 +    END;
   1.119 +  $$;
   1.120 +
   1.121 +COMMENT ON FUNCTION "set_harmonic_initiative_weights"
   1.122 +  ( "issue"."id"%TYPE )
   1.123 +  IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
   1.124 +
   1.125 +CREATE VIEW "remaining_harmonic_opinion_weight" AS
   1.126 +  SELECT
   1.127 +    "initiative"."issue_id",
   1.128 +    "opinion"."initiative_id",
   1.129 +    "direct_interest_snapshot"."member_id",
   1.130 +    "direct_interest_snapshot"."weight" AS "weight_num",
   1.131 +    count("opinion"."suggestion_id") AS "weight_den"
   1.132 +  FROM "issue"
   1.133 +  JOIN "direct_interest_snapshot"
   1.134 +    ON "issue"."id" = "direct_interest_snapshot"."issue_id"
   1.135 +    AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
   1.136 +  JOIN "initiative"
   1.137 +    ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"
   1.138 +  JOIN "opinion"
   1.139 +    ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"
   1.140 +    AND "initiative"."id" = "opinion"."initiative_id"
   1.141 +    AND "opinion"."degree" > 0
   1.142 +  GROUP BY
   1.143 +    "initiative"."issue_id",
   1.144 +    "opinion"."initiative_id",
   1.145 +    "direct_interest_snapshot"."member_id",
   1.146 +    "direct_interest_snapshot"."weight";
   1.147 +
   1.148 +COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"';
   1.149 +
   1.150 +CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS
   1.151 +  SELECT
   1.152 +    "suggestion"."initiative_id",
   1.153 +    "opinion"."suggestion_id",
   1.154 +    sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num",
   1.155 +    "remaining_harmonic_opinion_weight"."weight_den"
   1.156 +  FROM "remaining_harmonic_opinion_weight"
   1.157 +  JOIN "opinion"
   1.158 +    ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"
   1.159 +    AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"
   1.160 +  JOIN "suggestion"
   1.161 +    ON "opinion"."suggestion_id" = "suggestion"."id"
   1.162 +    AND "suggestion"."harmonic_weight" ISNULL
   1.163 +  GROUP BY
   1.164 +    "suggestion"."initiative_id",
   1.165 +    "opinion"."suggestion_id",
   1.166 +    "remaining_harmonic_opinion_weight"."weight_den";
   1.167 +
   1.168 +COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"';
   1.169 +
   1.170 +CREATE FUNCTION "set_harmonic_suggestion_weights"
   1.171 +  ( "initiative_id_p" "initiative"."id"%TYPE )
   1.172 +  RETURNS VOID
   1.173 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.174 +    DECLARE
   1.175 +      "weight_row"   "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;
   1.176 +      "i"            INT4;
   1.177 +      "count_v"      INT4;
   1.178 +      "summand_v"    FLOAT;
   1.179 +      "id_ary"       INT4[];
   1.180 +      "weight_ary"   FLOAT[];
   1.181 +      "min_weight_v" FLOAT;
   1.182 +    BEGIN
   1.183 +      UPDATE "suggestion" SET "harmonic_weight" = NULL
   1.184 +        WHERE "initiative_id" = "initiative_id_p";
   1.185 +      LOOP
   1.186 +        "min_weight_v" := NULL;
   1.187 +        "i" := 0;
   1.188 +        "count_v" := 0;
   1.189 +        FOR "weight_row" IN
   1.190 +          SELECT * FROM "remaining_harmonic_suggestion_weight_summands"
   1.191 +          WHERE "initiative_id" = "initiative_id_p"
   1.192 +          ORDER BY "suggestion_id" DESC, "weight_den" DESC
   1.193 +          -- NOTE: latest suggestions treated worse
   1.194 +        LOOP
   1.195 +          "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
   1.196 +          IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN
   1.197 +            "i" := "i" + 1;
   1.198 +            "count_v" := "i";
   1.199 +            "id_ary"["i"] := "weight_row"."suggestion_id";
   1.200 +            "weight_ary"["i"] := "summand_v";
   1.201 +          ELSE
   1.202 +            "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
   1.203 +          END IF;
   1.204 +        END LOOP;
   1.205 +        EXIT WHEN "count_v" = 0;
   1.206 +        "i" := 1;
   1.207 +        LOOP
   1.208 +          "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
   1.209 +          IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
   1.210 +            "min_weight_v" := "weight_ary"["i"];
   1.211 +          END IF;
   1.212 +          "i" := "i" + 1;
   1.213 +          EXIT WHEN "i" > "count_v";
   1.214 +        END LOOP;
   1.215 +        "i" := 1;
   1.216 +        LOOP
   1.217 +          IF "weight_ary"["i"] = "min_weight_v" THEN
   1.218 +            UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"
   1.219 +              WHERE "id" = "id_ary"["i"];
   1.220 +            EXIT;
   1.221 +          END IF;
   1.222 +          "i" := "i" + 1;
   1.223 +        END LOOP;
   1.224 +      END LOOP;
   1.225 +    END;
   1.226 +  $$;
   1.227 +
   1.228 +COMMENT ON FUNCTION "set_harmonic_suggestion_weights"
   1.229 +  ( "issue"."id"%TYPE )
   1.230 +  IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative';
   1.231 +
   1.232 +CREATE OR REPLACE FUNCTION "create_snapshot"
   1.233 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.234 +  RETURNS VOID
   1.235 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.236 +    DECLARE
   1.237 +      "initiative_id_v"    "initiative"."id"%TYPE;
   1.238 +      "suggestion_id_v"    "suggestion"."id"%TYPE;
   1.239 +    BEGIN
   1.240 +      PERFORM "lock_issue"("issue_id_p");
   1.241 +      PERFORM "create_population_snapshot"("issue_id_p");
   1.242 +      PERFORM "create_interest_snapshot"("issue_id_p");
   1.243 +      UPDATE "issue" SET
   1.244 +        "snapshot" = now(),
   1.245 +        "latest_snapshot_event" = 'periodic',
   1.246 +        "population" = (
   1.247 +          SELECT coalesce(sum("weight"), 0)
   1.248 +          FROM "direct_population_snapshot"
   1.249 +          WHERE "issue_id" = "issue_id_p"
   1.250 +          AND "event" = 'periodic'
   1.251 +        )
   1.252 +        WHERE "id" = "issue_id_p";
   1.253 +      FOR "initiative_id_v" IN
   1.254 +        SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
   1.255 +      LOOP
   1.256 +        UPDATE "initiative" SET
   1.257 +          "supporter_count" = (
   1.258 +            SELECT coalesce(sum("di"."weight"), 0)
   1.259 +            FROM "direct_interest_snapshot" AS "di"
   1.260 +            JOIN "direct_supporter_snapshot" AS "ds"
   1.261 +            ON "di"."member_id" = "ds"."member_id"
   1.262 +            WHERE "di"."issue_id" = "issue_id_p"
   1.263 +            AND "di"."event" = 'periodic'
   1.264 +            AND "ds"."initiative_id" = "initiative_id_v"
   1.265 +            AND "ds"."event" = 'periodic'
   1.266 +          ),
   1.267 +          "informed_supporter_count" = (
   1.268 +            SELECT coalesce(sum("di"."weight"), 0)
   1.269 +            FROM "direct_interest_snapshot" AS "di"
   1.270 +            JOIN "direct_supporter_snapshot" AS "ds"
   1.271 +            ON "di"."member_id" = "ds"."member_id"
   1.272 +            WHERE "di"."issue_id" = "issue_id_p"
   1.273 +            AND "di"."event" = 'periodic'
   1.274 +            AND "ds"."initiative_id" = "initiative_id_v"
   1.275 +            AND "ds"."event" = 'periodic'
   1.276 +            AND "ds"."informed"
   1.277 +          ),
   1.278 +          "satisfied_supporter_count" = (
   1.279 +            SELECT coalesce(sum("di"."weight"), 0)
   1.280 +            FROM "direct_interest_snapshot" AS "di"
   1.281 +            JOIN "direct_supporter_snapshot" AS "ds"
   1.282 +            ON "di"."member_id" = "ds"."member_id"
   1.283 +            WHERE "di"."issue_id" = "issue_id_p"
   1.284 +            AND "di"."event" = 'periodic'
   1.285 +            AND "ds"."initiative_id" = "initiative_id_v"
   1.286 +            AND "ds"."event" = 'periodic'
   1.287 +            AND "ds"."satisfied"
   1.288 +          ),
   1.289 +          "satisfied_informed_supporter_count" = (
   1.290 +            SELECT coalesce(sum("di"."weight"), 0)
   1.291 +            FROM "direct_interest_snapshot" AS "di"
   1.292 +            JOIN "direct_supporter_snapshot" AS "ds"
   1.293 +            ON "di"."member_id" = "ds"."member_id"
   1.294 +            WHERE "di"."issue_id" = "issue_id_p"
   1.295 +            AND "di"."event" = 'periodic'
   1.296 +            AND "ds"."initiative_id" = "initiative_id_v"
   1.297 +            AND "ds"."event" = 'periodic'
   1.298 +            AND "ds"."informed"
   1.299 +            AND "ds"."satisfied"
   1.300 +          )
   1.301 +          WHERE "id" = "initiative_id_v";
   1.302 +        FOR "suggestion_id_v" IN
   1.303 +          SELECT "id" FROM "suggestion"
   1.304 +          WHERE "initiative_id" = "initiative_id_v"
   1.305 +        LOOP
   1.306 +          UPDATE "suggestion" SET
   1.307 +            "minus2_unfulfilled_count" = (
   1.308 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.309 +              FROM "issue" CROSS JOIN "opinion"
   1.310 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.311 +              ON "snapshot"."issue_id" = "issue"."id"
   1.312 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.313 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.314 +              WHERE "issue"."id" = "issue_id_p"
   1.315 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.316 +              AND "opinion"."degree" = -2
   1.317 +              AND "opinion"."fulfilled" = FALSE
   1.318 +            ),
   1.319 +            "minus2_fulfilled_count" = (
   1.320 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.321 +              FROM "issue" CROSS JOIN "opinion"
   1.322 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.323 +              ON "snapshot"."issue_id" = "issue"."id"
   1.324 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.325 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.326 +              WHERE "issue"."id" = "issue_id_p"
   1.327 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.328 +              AND "opinion"."degree" = -2
   1.329 +              AND "opinion"."fulfilled" = TRUE
   1.330 +            ),
   1.331 +            "minus1_unfulfilled_count" = (
   1.332 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.333 +              FROM "issue" CROSS JOIN "opinion"
   1.334 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.335 +              ON "snapshot"."issue_id" = "issue"."id"
   1.336 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.337 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.338 +              WHERE "issue"."id" = "issue_id_p"
   1.339 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.340 +              AND "opinion"."degree" = -1
   1.341 +              AND "opinion"."fulfilled" = FALSE
   1.342 +            ),
   1.343 +            "minus1_fulfilled_count" = (
   1.344 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.345 +              FROM "issue" CROSS JOIN "opinion"
   1.346 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.347 +              ON "snapshot"."issue_id" = "issue"."id"
   1.348 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.349 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.350 +              WHERE "issue"."id" = "issue_id_p"
   1.351 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.352 +              AND "opinion"."degree" = -1
   1.353 +              AND "opinion"."fulfilled" = TRUE
   1.354 +            ),
   1.355 +            "plus1_unfulfilled_count" = (
   1.356 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.357 +              FROM "issue" CROSS JOIN "opinion"
   1.358 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.359 +              ON "snapshot"."issue_id" = "issue"."id"
   1.360 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.361 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.362 +              WHERE "issue"."id" = "issue_id_p"
   1.363 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.364 +              AND "opinion"."degree" = 1
   1.365 +              AND "opinion"."fulfilled" = FALSE
   1.366 +            ),
   1.367 +            "plus1_fulfilled_count" = (
   1.368 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.369 +              FROM "issue" CROSS JOIN "opinion"
   1.370 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.371 +              ON "snapshot"."issue_id" = "issue"."id"
   1.372 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.373 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.374 +              WHERE "issue"."id" = "issue_id_p"
   1.375 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.376 +              AND "opinion"."degree" = 1
   1.377 +              AND "opinion"."fulfilled" = TRUE
   1.378 +            ),
   1.379 +            "plus2_unfulfilled_count" = (
   1.380 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.381 +              FROM "issue" CROSS JOIN "opinion"
   1.382 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.383 +              ON "snapshot"."issue_id" = "issue"."id"
   1.384 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.385 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.386 +              WHERE "issue"."id" = "issue_id_p"
   1.387 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.388 +              AND "opinion"."degree" = 2
   1.389 +              AND "opinion"."fulfilled" = FALSE
   1.390 +            ),
   1.391 +            "plus2_fulfilled_count" = (
   1.392 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.393 +              FROM "issue" CROSS JOIN "opinion"
   1.394 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.395 +              ON "snapshot"."issue_id" = "issue"."id"
   1.396 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.397 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.398 +              WHERE "issue"."id" = "issue_id_p"
   1.399 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.400 +              AND "opinion"."degree" = 2
   1.401 +              AND "opinion"."fulfilled" = TRUE
   1.402 +            )
   1.403 +            WHERE "suggestion"."id" = "suggestion_id_v";
   1.404 +        END LOOP;
   1.405 +        PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");
   1.406 +      END LOOP;
   1.407 +      PERFORM "set_harmonic_initiative_weights"("issue_id_p");
   1.408 +      RETURN;
   1.409 +    END;
   1.410 +  $$;
   1.411 +
   1.412 +SELECT "set_harmonic_initiative_weights"("id") FROM "issue";
   1.413 +SELECT "set_harmonic_suggestion_weights"("id") FROM "initiative";
   1.414 +
   1.415 +COMMIT;

Impressum / About Us