liquid_feedback_core

annotate update/core-update.v2.1.0-v2.1.1.sql @ 320:3a7f06a88ecb

find changesets by author, revision, files, or words in the commit message
Calculate "harmonic_weight" to be used as a secondary sorting key, while the "admitted" flag shall be used as the primary sorting key for initiatives
author jbe
date Fri Feb 08 18:26:25 2013 +0100 (2013-02-08)
parents 685d38986598
children 48a5036d5eb1
rev   line source
jbe@315 1 BEGIN;
jbe@315 2
jbe@315 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@315 4 SELECT * FROM (VALUES ('2.1.1', 2, 1, 1))
jbe@315 5 AS "subquery"("string", "major", "minor", "revision");
jbe@315 6
jbe@315 7 ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
jbe@320 8 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; shall be used as secondary sorting key after "admitted" as primary sorting key';
jbe@315 9
jbe@315 10 ALTER TABLE "suggestion" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
jbe@315 11 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';
jbe@315 12
jbe@315 13 CREATE VIEW "remaining_harmonic_supporter_weight" AS
jbe@315 14 SELECT
jbe@315 15 "direct_interest_snapshot"."issue_id",
jbe@315 16 "direct_interest_snapshot"."event",
jbe@315 17 "direct_interest_snapshot"."member_id",
jbe@315 18 "direct_interest_snapshot"."weight" AS "weight_num",
jbe@315 19 count("initiative"."id") AS "weight_den"
jbe@315 20 FROM "issue"
jbe@315 21 JOIN "direct_interest_snapshot"
jbe@315 22 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
jbe@315 23 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
jbe@315 24 JOIN "direct_supporter_snapshot"
jbe@315 25 ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id"
jbe@315 26 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
jbe@315 27 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@315 28 JOIN "initiative"
jbe@315 29 ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@315 30 AND "initiative"."harmonic_weight" ISNULL
jbe@315 31 GROUP BY
jbe@315 32 "direct_interest_snapshot"."issue_id",
jbe@315 33 "direct_interest_snapshot"."event",
jbe@315 34 "direct_interest_snapshot"."member_id",
jbe@315 35 "direct_interest_snapshot"."weight";
jbe@315 36
jbe@315 37 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@315 38
jbe@315 39 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
jbe@315 40 SELECT
jbe@315 41 "initiative"."issue_id",
jbe@315 42 "initiative"."id" AS "initiative_id",
jbe@320 43 "initiative"."admitted",
jbe@315 44 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
jbe@315 45 "remaining_harmonic_supporter_weight"."weight_den"
jbe@315 46 FROM "remaining_harmonic_supporter_weight"
jbe@315 47 JOIN "direct_supporter_snapshot"
jbe@315 48 ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id"
jbe@315 49 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
jbe@315 50 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@315 51 JOIN "initiative"
jbe@315 52 ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@315 53 AND "initiative"."harmonic_weight" ISNULL
jbe@315 54 GROUP BY
jbe@315 55 "initiative"."issue_id",
jbe@315 56 "initiative"."id",
jbe@320 57 "initiative"."admitted",
jbe@315 58 "remaining_harmonic_supporter_weight"."weight_den";
jbe@315 59
jbe@315 60 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@315 61
jbe@315 62 CREATE FUNCTION "set_harmonic_initiative_weights"
jbe@315 63 ( "issue_id_p" "issue"."id"%TYPE )
jbe@315 64 RETURNS VOID
jbe@315 65 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@315 66 DECLARE
jbe@315 67 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
jbe@315 68 "i" INT4;
jbe@315 69 "count_v" INT4;
jbe@315 70 "summand_v" FLOAT;
jbe@315 71 "id_ary" INT4[];
jbe@315 72 "weight_ary" FLOAT[];
jbe@315 73 "min_weight_v" FLOAT;
jbe@315 74 BEGIN
jbe@315 75 UPDATE "initiative" SET "harmonic_weight" = NULL
jbe@315 76 WHERE "issue_id" = "issue_id_p";
jbe@315 77 LOOP
jbe@315 78 "min_weight_v" := NULL;
jbe@315 79 "i" := 0;
jbe@315 80 "count_v" := 0;
jbe@315 81 FOR "weight_row" IN
jbe@315 82 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
jbe@315 83 WHERE "issue_id" = "issue_id_p"
jbe@320 84 AND (
jbe@320 85 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
jbe@320 86 SELECT NULL FROM "initiative"
jbe@320 87 WHERE "issue_id" = "issue_id_p"
jbe@320 88 AND "harmonic_weight" ISNULL
jbe@320 89 AND coalesce("admitted", FALSE) = FALSE
jbe@320 90 )
jbe@320 91 )
jbe@315 92 ORDER BY "initiative_id" DESC, "weight_den" DESC
jbe@320 93 -- NOTE: non-admitted initiatives placed first (at last positions),
jbe@320 94 -- latest initiatives treated worse in case of tie
jbe@315 95 LOOP
jbe@315 96 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
jbe@315 97 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
jbe@315 98 "i" := "i" + 1;
jbe@315 99 "count_v" := "i";
jbe@315 100 "id_ary"["i"] := "weight_row"."initiative_id";
jbe@315 101 "weight_ary"["i"] := "summand_v";
jbe@315 102 ELSE
jbe@315 103 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
jbe@315 104 END IF;
jbe@315 105 END LOOP;
jbe@315 106 EXIT WHEN "count_v" = 0;
jbe@315 107 "i" := 1;
jbe@315 108 LOOP
jbe@315 109 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
jbe@315 110 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
jbe@315 111 "min_weight_v" := "weight_ary"["i"];
jbe@315 112 END IF;
jbe@315 113 "i" := "i" + 1;
jbe@315 114 EXIT WHEN "i" > "count_v";
jbe@315 115 END LOOP;
jbe@315 116 "i" := 1;
jbe@315 117 LOOP
jbe@315 118 IF "weight_ary"["i"] = "min_weight_v" THEN
jbe@315 119 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
jbe@315 120 WHERE "id" = "id_ary"["i"];
jbe@315 121 EXIT;
jbe@315 122 END IF;
jbe@315 123 "i" := "i" + 1;
jbe@315 124 END LOOP;
jbe@315 125 END LOOP;
jbe@316 126 UPDATE "initiative" SET "harmonic_weight" = 0
jbe@316 127 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
jbe@315 128 END;
jbe@315 129 $$;
jbe@315 130
jbe@315 131 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
jbe@315 132 ( "issue"."id"%TYPE )
jbe@315 133 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
jbe@315 134
jbe@315 135 CREATE VIEW "remaining_harmonic_opinion_weight" AS
jbe@315 136 SELECT
jbe@315 137 "initiative"."issue_id",
jbe@315 138 "opinion"."initiative_id",
jbe@315 139 "direct_interest_snapshot"."member_id",
jbe@315 140 "direct_interest_snapshot"."weight" AS "weight_num",
jbe@315 141 count("opinion"."suggestion_id") AS "weight_den"
jbe@315 142 FROM "issue"
jbe@315 143 JOIN "direct_interest_snapshot"
jbe@315 144 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
jbe@315 145 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
jbe@315 146 JOIN "initiative"
jbe@315 147 ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"
jbe@315 148 JOIN "opinion"
jbe@315 149 ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"
jbe@315 150 AND "initiative"."id" = "opinion"."initiative_id"
jbe@317 151 AND (
jbe@317 152 ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR
jbe@317 153 ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE)
jbe@317 154 )
jbe@318 155 JOIN "suggestion"
jbe@318 156 ON "opinion"."suggestion_id" = "suggestion"."id"
jbe@318 157 AND "suggestion"."harmonic_weight" ISNULL
jbe@315 158 GROUP BY
jbe@315 159 "initiative"."issue_id",
jbe@315 160 "opinion"."initiative_id",
jbe@315 161 "direct_interest_snapshot"."member_id",
jbe@315 162 "direct_interest_snapshot"."weight";
jbe@315 163
jbe@315 164 COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"';
jbe@315 165
jbe@315 166 CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS
jbe@315 167 SELECT
jbe@315 168 "suggestion"."initiative_id",
jbe@315 169 "opinion"."suggestion_id",
jbe@315 170 sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num",
jbe@315 171 "remaining_harmonic_opinion_weight"."weight_den"
jbe@315 172 FROM "remaining_harmonic_opinion_weight"
jbe@315 173 JOIN "opinion"
jbe@315 174 ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"
jbe@315 175 AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"
jbe@319 176 AND (
jbe@319 177 ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR
jbe@319 178 ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE)
jbe@319 179 )
jbe@315 180 JOIN "suggestion"
jbe@315 181 ON "opinion"."suggestion_id" = "suggestion"."id"
jbe@315 182 AND "suggestion"."harmonic_weight" ISNULL
jbe@315 183 GROUP BY
jbe@315 184 "suggestion"."initiative_id",
jbe@315 185 "opinion"."suggestion_id",
jbe@315 186 "remaining_harmonic_opinion_weight"."weight_den";
jbe@315 187
jbe@315 188 COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"';
jbe@315 189
jbe@315 190 CREATE FUNCTION "set_harmonic_suggestion_weights"
jbe@315 191 ( "initiative_id_p" "initiative"."id"%TYPE )
jbe@315 192 RETURNS VOID
jbe@315 193 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@315 194 DECLARE
jbe@315 195 "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;
jbe@315 196 "i" INT4;
jbe@315 197 "count_v" INT4;
jbe@315 198 "summand_v" FLOAT;
jbe@315 199 "id_ary" INT4[];
jbe@315 200 "weight_ary" FLOAT[];
jbe@315 201 "min_weight_v" FLOAT;
jbe@315 202 BEGIN
jbe@315 203 UPDATE "suggestion" SET "harmonic_weight" = NULL
jbe@315 204 WHERE "initiative_id" = "initiative_id_p";
jbe@315 205 LOOP
jbe@315 206 "min_weight_v" := NULL;
jbe@315 207 "i" := 0;
jbe@315 208 "count_v" := 0;
jbe@315 209 FOR "weight_row" IN
jbe@315 210 SELECT * FROM "remaining_harmonic_suggestion_weight_summands"
jbe@315 211 WHERE "initiative_id" = "initiative_id_p"
jbe@315 212 ORDER BY "suggestion_id" DESC, "weight_den" DESC
jbe@320 213 -- NOTE: latest suggestions treated worse in case of tie
jbe@315 214 LOOP
jbe@315 215 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
jbe@315 216 IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN
jbe@315 217 "i" := "i" + 1;
jbe@315 218 "count_v" := "i";
jbe@315 219 "id_ary"["i"] := "weight_row"."suggestion_id";
jbe@315 220 "weight_ary"["i"] := "summand_v";
jbe@315 221 ELSE
jbe@315 222 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
jbe@315 223 END IF;
jbe@315 224 END LOOP;
jbe@315 225 EXIT WHEN "count_v" = 0;
jbe@315 226 "i" := 1;
jbe@315 227 LOOP
jbe@315 228 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
jbe@315 229 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
jbe@315 230 "min_weight_v" := "weight_ary"["i"];
jbe@315 231 END IF;
jbe@315 232 "i" := "i" + 1;
jbe@315 233 EXIT WHEN "i" > "count_v";
jbe@315 234 END LOOP;
jbe@315 235 "i" := 1;
jbe@315 236 LOOP
jbe@315 237 IF "weight_ary"["i"] = "min_weight_v" THEN
jbe@315 238 UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"
jbe@315 239 WHERE "id" = "id_ary"["i"];
jbe@315 240 EXIT;
jbe@315 241 END IF;
jbe@315 242 "i" := "i" + 1;
jbe@315 243 END LOOP;
jbe@315 244 END LOOP;
jbe@316 245 UPDATE "suggestion" SET "harmonic_weight" = 0
jbe@316 246 WHERE "initiative_id" = "initiative_id_p" AND "harmonic_weight" ISNULL;
jbe@315 247 END;
jbe@315 248 $$;
jbe@315 249
jbe@315 250 COMMENT ON FUNCTION "set_harmonic_suggestion_weights"
jbe@315 251 ( "issue"."id"%TYPE )
jbe@315 252 IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative';
jbe@315 253
jbe@315 254 CREATE OR REPLACE FUNCTION "create_snapshot"
jbe@315 255 ( "issue_id_p" "issue"."id"%TYPE )
jbe@315 256 RETURNS VOID
jbe@315 257 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@315 258 DECLARE
jbe@315 259 "initiative_id_v" "initiative"."id"%TYPE;
jbe@315 260 "suggestion_id_v" "suggestion"."id"%TYPE;
jbe@315 261 BEGIN
jbe@315 262 PERFORM "lock_issue"("issue_id_p");
jbe@315 263 PERFORM "create_population_snapshot"("issue_id_p");
jbe@315 264 PERFORM "create_interest_snapshot"("issue_id_p");
jbe@315 265 UPDATE "issue" SET
jbe@315 266 "snapshot" = now(),
jbe@315 267 "latest_snapshot_event" = 'periodic',
jbe@315 268 "population" = (
jbe@315 269 SELECT coalesce(sum("weight"), 0)
jbe@315 270 FROM "direct_population_snapshot"
jbe@315 271 WHERE "issue_id" = "issue_id_p"
jbe@315 272 AND "event" = 'periodic'
jbe@315 273 )
jbe@315 274 WHERE "id" = "issue_id_p";
jbe@315 275 FOR "initiative_id_v" IN
jbe@315 276 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@315 277 LOOP
jbe@315 278 UPDATE "initiative" SET
jbe@315 279 "supporter_count" = (
jbe@315 280 SELECT coalesce(sum("di"."weight"), 0)
jbe@315 281 FROM "direct_interest_snapshot" AS "di"
jbe@315 282 JOIN "direct_supporter_snapshot" AS "ds"
jbe@315 283 ON "di"."member_id" = "ds"."member_id"
jbe@315 284 WHERE "di"."issue_id" = "issue_id_p"
jbe@315 285 AND "di"."event" = 'periodic'
jbe@315 286 AND "ds"."initiative_id" = "initiative_id_v"
jbe@315 287 AND "ds"."event" = 'periodic'
jbe@315 288 ),
jbe@315 289 "informed_supporter_count" = (
jbe@315 290 SELECT coalesce(sum("di"."weight"), 0)
jbe@315 291 FROM "direct_interest_snapshot" AS "di"
jbe@315 292 JOIN "direct_supporter_snapshot" AS "ds"
jbe@315 293 ON "di"."member_id" = "ds"."member_id"
jbe@315 294 WHERE "di"."issue_id" = "issue_id_p"
jbe@315 295 AND "di"."event" = 'periodic'
jbe@315 296 AND "ds"."initiative_id" = "initiative_id_v"
jbe@315 297 AND "ds"."event" = 'periodic'
jbe@315 298 AND "ds"."informed"
jbe@315 299 ),
jbe@315 300 "satisfied_supporter_count" = (
jbe@315 301 SELECT coalesce(sum("di"."weight"), 0)
jbe@315 302 FROM "direct_interest_snapshot" AS "di"
jbe@315 303 JOIN "direct_supporter_snapshot" AS "ds"
jbe@315 304 ON "di"."member_id" = "ds"."member_id"
jbe@315 305 WHERE "di"."issue_id" = "issue_id_p"
jbe@315 306 AND "di"."event" = 'periodic'
jbe@315 307 AND "ds"."initiative_id" = "initiative_id_v"
jbe@315 308 AND "ds"."event" = 'periodic'
jbe@315 309 AND "ds"."satisfied"
jbe@315 310 ),
jbe@315 311 "satisfied_informed_supporter_count" = (
jbe@315 312 SELECT coalesce(sum("di"."weight"), 0)
jbe@315 313 FROM "direct_interest_snapshot" AS "di"
jbe@315 314 JOIN "direct_supporter_snapshot" AS "ds"
jbe@315 315 ON "di"."member_id" = "ds"."member_id"
jbe@315 316 WHERE "di"."issue_id" = "issue_id_p"
jbe@315 317 AND "di"."event" = 'periodic'
jbe@315 318 AND "ds"."initiative_id" = "initiative_id_v"
jbe@315 319 AND "ds"."event" = 'periodic'
jbe@315 320 AND "ds"."informed"
jbe@315 321 AND "ds"."satisfied"
jbe@315 322 )
jbe@315 323 WHERE "id" = "initiative_id_v";
jbe@315 324 FOR "suggestion_id_v" IN
jbe@315 325 SELECT "id" FROM "suggestion"
jbe@315 326 WHERE "initiative_id" = "initiative_id_v"
jbe@315 327 LOOP
jbe@315 328 UPDATE "suggestion" SET
jbe@315 329 "minus2_unfulfilled_count" = (
jbe@315 330 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315 331 FROM "issue" CROSS JOIN "opinion"
jbe@315 332 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315 333 ON "snapshot"."issue_id" = "issue"."id"
jbe@315 334 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315 335 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315 336 WHERE "issue"."id" = "issue_id_p"
jbe@315 337 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315 338 AND "opinion"."degree" = -2
jbe@315 339 AND "opinion"."fulfilled" = FALSE
jbe@315 340 ),
jbe@315 341 "minus2_fulfilled_count" = (
jbe@315 342 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315 343 FROM "issue" CROSS JOIN "opinion"
jbe@315 344 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315 345 ON "snapshot"."issue_id" = "issue"."id"
jbe@315 346 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315 347 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315 348 WHERE "issue"."id" = "issue_id_p"
jbe@315 349 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315 350 AND "opinion"."degree" = -2
jbe@315 351 AND "opinion"."fulfilled" = TRUE
jbe@315 352 ),
jbe@315 353 "minus1_unfulfilled_count" = (
jbe@315 354 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315 355 FROM "issue" CROSS JOIN "opinion"
jbe@315 356 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315 357 ON "snapshot"."issue_id" = "issue"."id"
jbe@315 358 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315 359 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315 360 WHERE "issue"."id" = "issue_id_p"
jbe@315 361 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315 362 AND "opinion"."degree" = -1
jbe@315 363 AND "opinion"."fulfilled" = FALSE
jbe@315 364 ),
jbe@315 365 "minus1_fulfilled_count" = (
jbe@315 366 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315 367 FROM "issue" CROSS JOIN "opinion"
jbe@315 368 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315 369 ON "snapshot"."issue_id" = "issue"."id"
jbe@315 370 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315 371 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315 372 WHERE "issue"."id" = "issue_id_p"
jbe@315 373 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315 374 AND "opinion"."degree" = -1
jbe@315 375 AND "opinion"."fulfilled" = TRUE
jbe@315 376 ),
jbe@315 377 "plus1_unfulfilled_count" = (
jbe@315 378 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315 379 FROM "issue" CROSS JOIN "opinion"
jbe@315 380 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315 381 ON "snapshot"."issue_id" = "issue"."id"
jbe@315 382 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315 383 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315 384 WHERE "issue"."id" = "issue_id_p"
jbe@315 385 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315 386 AND "opinion"."degree" = 1
jbe@315 387 AND "opinion"."fulfilled" = FALSE
jbe@315 388 ),
jbe@315 389 "plus1_fulfilled_count" = (
jbe@315 390 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315 391 FROM "issue" CROSS JOIN "opinion"
jbe@315 392 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315 393 ON "snapshot"."issue_id" = "issue"."id"
jbe@315 394 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315 395 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315 396 WHERE "issue"."id" = "issue_id_p"
jbe@315 397 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315 398 AND "opinion"."degree" = 1
jbe@315 399 AND "opinion"."fulfilled" = TRUE
jbe@315 400 ),
jbe@315 401 "plus2_unfulfilled_count" = (
jbe@315 402 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315 403 FROM "issue" CROSS JOIN "opinion"
jbe@315 404 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315 405 ON "snapshot"."issue_id" = "issue"."id"
jbe@315 406 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315 407 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315 408 WHERE "issue"."id" = "issue_id_p"
jbe@315 409 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315 410 AND "opinion"."degree" = 2
jbe@315 411 AND "opinion"."fulfilled" = FALSE
jbe@315 412 ),
jbe@315 413 "plus2_fulfilled_count" = (
jbe@315 414 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@315 415 FROM "issue" CROSS JOIN "opinion"
jbe@315 416 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@315 417 ON "snapshot"."issue_id" = "issue"."id"
jbe@315 418 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@315 419 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@315 420 WHERE "issue"."id" = "issue_id_p"
jbe@315 421 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@315 422 AND "opinion"."degree" = 2
jbe@315 423 AND "opinion"."fulfilled" = TRUE
jbe@315 424 )
jbe@315 425 WHERE "suggestion"."id" = "suggestion_id_v";
jbe@315 426 END LOOP;
jbe@315 427 PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");
jbe@315 428 END LOOP;
jbe@315 429 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@315 430 RETURN;
jbe@315 431 END;
jbe@315 432 $$;
jbe@315 433
jbe@315 434 SELECT "set_harmonic_initiative_weights"("id") FROM "issue";
jbe@315 435 SELECT "set_harmonic_suggestion_weights"("id") FROM "initiative";
jbe@315 436
jbe@315 437 COMMIT;

Impressum / About Us