liquid_feedback_core
changeset 437:eb12a069063c
Improved previous fix of "close_voting" function
author | jbe |
---|---|
date | Wed Jul 16 16:50:07 2014 +0200 (2014-07-16) |
parents | 34cc98defa8b |
children | 785ea3c0fd18 |
files | core.sql update/core-update.v3.0.2-v3.0.3.sql |
line diff
1.1 --- a/core.sql Wed Jul 16 16:28:35 2014 +0200 1.2 +++ b/core.sql Wed Jul 16 16:50:07 2014 +0200 1.3 @@ -3804,9 +3804,23 @@ 1.4 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 1.5 ) 1.6 WHERE "id" = "issue_id_p"; 1.7 + -- copy "positive_votes" and "negative_votes" from "battle" table: 1.8 + -- NOTE: "first_preference_votes" is set to a default of 0 at this step 1.9 + UPDATE "initiative" SET 1.10 + "first_preference_votes" = 0, 1.11 + "positive_votes" = "battle_win"."count", 1.12 + "negative_votes" = "battle_lose"."count" 1.13 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 1.14 + WHERE 1.15 + "battle_win"."issue_id" = "issue_id_p" AND 1.16 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 1.17 + "battle_win"."losing_initiative_id" ISNULL AND 1.18 + "battle_lose"."issue_id" = "issue_id_p" AND 1.19 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 1.20 + "battle_lose"."winning_initiative_id" ISNULL; 1.21 -- calculate "first_preference_votes": 1.22 - UPDATE "initiative" 1.23 - SET "first_preference_votes" = "subquery"."sum" 1.24 + -- NOTE: will only set values not equal to zero 1.25 + UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum" 1.26 FROM ( 1.27 SELECT "vote"."initiative_id", sum("direct_voter"."weight") 1.28 FROM "vote" JOIN "direct_voter" 1.29 @@ -3818,22 +3832,6 @@ 1.30 WHERE "initiative"."issue_id" = "issue_id_p" 1.31 AND "initiative"."admitted" 1.32 AND "initiative"."id" = "subquery"."initiative_id"; 1.33 - UPDATE "initiative" SET "first_preference_votes" = 0 1.34 - WHERE "issue_id" = "issue_id_p" 1.35 - AND "initiative"."admitted" 1.36 - AND "first_preference_votes" ISNULL; 1.37 - -- copy "positive_votes" and "negative_votes" from "battle" table: 1.38 - UPDATE "initiative" SET 1.39 - "positive_votes" = "battle_win"."count", 1.40 - "negative_votes" = "battle_lose"."count" 1.41 - FROM "battle" AS "battle_win", "battle" AS "battle_lose" 1.42 - WHERE 1.43 - "battle_win"."issue_id" = "issue_id_p" AND 1.44 - "battle_win"."winning_initiative_id" = "initiative"."id" AND 1.45 - "battle_win"."losing_initiative_id" ISNULL AND 1.46 - "battle_lose"."issue_id" = "issue_id_p" AND 1.47 - "battle_lose"."losing_initiative_id" = "initiative"."id" AND 1.48 - "battle_lose"."winning_initiative_id" ISNULL; 1.49 END; 1.50 $$; 1.51
2.1 --- a/update/core-update.v3.0.2-v3.0.3.sql Wed Jul 16 16:28:35 2014 +0200 2.2 +++ b/update/core-update.v3.0.2-v3.0.3.sql Wed Jul 16 16:50:07 2014 +0200 2.3 @@ -239,9 +239,23 @@ 2.4 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.5 ) 2.6 WHERE "id" = "issue_id_p"; 2.7 + -- copy "positive_votes" and "negative_votes" from "battle" table: 2.8 + -- NOTE: "first_preference_votes" is set to a default of 0 at this step 2.9 + UPDATE "initiative" SET 2.10 + "first_preference_votes" = 0, 2.11 + "positive_votes" = "battle_win"."count", 2.12 + "negative_votes" = "battle_lose"."count" 2.13 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 2.14 + WHERE 2.15 + "battle_win"."issue_id" = "issue_id_p" AND 2.16 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 2.17 + "battle_win"."losing_initiative_id" ISNULL AND 2.18 + "battle_lose"."issue_id" = "issue_id_p" AND 2.19 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 2.20 + "battle_lose"."winning_initiative_id" ISNULL; 2.21 -- calculate "first_preference_votes": 2.22 - UPDATE "initiative" 2.23 - SET "first_preference_votes" = "subquery"."sum" 2.24 + -- NOTE: will only set values not equal to zero 2.25 + UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum" 2.26 FROM ( 2.27 SELECT "vote"."initiative_id", sum("direct_voter"."weight") 2.28 FROM "vote" JOIN "direct_voter" 2.29 @@ -253,22 +267,6 @@ 2.30 WHERE "initiative"."issue_id" = "issue_id_p" 2.31 AND "initiative"."admitted" 2.32 AND "initiative"."id" = "subquery"."initiative_id"; 2.33 - UPDATE "initiative" SET "first_preference_votes" = 0 2.34 - WHERE "issue_id" = "issue_id_p" 2.35 - AND "initiative"."admitted" 2.36 - AND "first_preference_votes" ISNULL; 2.37 - -- copy "positive_votes" and "negative_votes" from "battle" table: 2.38 - UPDATE "initiative" SET 2.39 - "positive_votes" = "battle_win"."count", 2.40 - "negative_votes" = "battle_lose"."count" 2.41 - FROM "battle" AS "battle_win", "battle" AS "battle_lose" 2.42 - WHERE 2.43 - "battle_win"."issue_id" = "issue_id_p" AND 2.44 - "battle_win"."winning_initiative_id" = "initiative"."id" AND 2.45 - "battle_win"."losing_initiative_id" ISNULL AND 2.46 - "battle_lose"."issue_id" = "issue_id_p" AND 2.47 - "battle_lose"."losing_initiative_id" = "initiative"."id" AND 2.48 - "battle_lose"."winning_initiative_id" ISNULL; 2.49 END; 2.50 $$; 2.51