liquid_feedback_core
changeset 320:3a7f06a88ecb
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 |
files | core.sql update/core-update.v2.1.0-v2.1.1.sql |
line diff
1.1 --- a/core.sql Sat Feb 02 22:31:55 2013 +0100 1.2 +++ b/core.sql Fri Feb 08 18:26:25 2013 +0100 1.3 @@ -668,7 +668,7 @@ 1.4 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.5 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.6 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.7 -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.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'; 1.9 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 1.10 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 1.11 COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"'; 1.12 @@ -3085,6 +3085,7 @@ 1.13 SELECT 1.14 "initiative"."issue_id", 1.15 "initiative"."id" AS "initiative_id", 1.16 + "initiative"."admitted", 1.17 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num", 1.18 "remaining_harmonic_supporter_weight"."weight_den" 1.19 FROM "remaining_harmonic_supporter_weight" 1.20 @@ -3098,6 +3099,7 @@ 1.21 GROUP BY 1.22 "initiative"."issue_id", 1.23 "initiative"."id", 1.24 + "initiative"."admitted", 1.25 "remaining_harmonic_supporter_weight"."weight_den"; 1.26 1.27 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"'; 1.28 @@ -3125,8 +3127,17 @@ 1.29 FOR "weight_row" IN 1.30 SELECT * FROM "remaining_harmonic_initiative_weight_summands" 1.31 WHERE "issue_id" = "issue_id_p" 1.32 + AND ( 1.33 + coalesce("admitted", FALSE) = FALSE OR NOT EXISTS ( 1.34 + SELECT NULL FROM "initiative" 1.35 + WHERE "issue_id" = "issue_id_p" 1.36 + AND "harmonic_weight" ISNULL 1.37 + AND coalesce("admitted", FALSE) = FALSE 1.38 + ) 1.39 + ) 1.40 ORDER BY "initiative_id" DESC, "weight_den" DESC 1.41 - -- NOTE: latest initiatives treated worse 1.42 + -- NOTE: non-admitted initiatives placed first (at last positions), 1.43 + -- latest initiatives treated worse in case of tie 1.44 LOOP 1.45 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; 1.46 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN 1.47 @@ -3248,7 +3259,7 @@ 1.48 SELECT * FROM "remaining_harmonic_suggestion_weight_summands" 1.49 WHERE "initiative_id" = "initiative_id_p" 1.50 ORDER BY "suggestion_id" DESC, "weight_den" DESC 1.51 - -- NOTE: latest suggestions treated worse 1.52 + -- NOTE: latest suggestions treated worse in case of tie 1.53 LOOP 1.54 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; 1.55 IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN
2.1 --- a/update/core-update.v2.1.0-v2.1.1.sql Sat Feb 02 22:31:55 2013 +0100 2.2 +++ b/update/core-update.v2.1.0-v2.1.1.sql Fri Feb 08 18:26:25 2013 +0100 2.3 @@ -5,7 +5,7 @@ 2.4 AS "subquery"("string", "major", "minor", "revision"); 2.5 2.6 ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3); 2.7 -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'; 2.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'; 2.9 2.10 ALTER TABLE "suggestion" ADD COLUMN "harmonic_weight" NUMERIC(12, 3); 2.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'; 2.12 @@ -40,6 +40,7 @@ 2.13 SELECT 2.14 "initiative"."issue_id", 2.15 "initiative"."id" AS "initiative_id", 2.16 + "initiative"."admitted", 2.17 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num", 2.18 "remaining_harmonic_supporter_weight"."weight_den" 2.19 FROM "remaining_harmonic_supporter_weight" 2.20 @@ -53,6 +54,7 @@ 2.21 GROUP BY 2.22 "initiative"."issue_id", 2.23 "initiative"."id", 2.24 + "initiative"."admitted", 2.25 "remaining_harmonic_supporter_weight"."weight_den"; 2.26 2.27 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"'; 2.28 @@ -79,8 +81,17 @@ 2.29 FOR "weight_row" IN 2.30 SELECT * FROM "remaining_harmonic_initiative_weight_summands" 2.31 WHERE "issue_id" = "issue_id_p" 2.32 + AND ( 2.33 + coalesce("admitted", FALSE) = FALSE OR NOT EXISTS ( 2.34 + SELECT NULL FROM "initiative" 2.35 + WHERE "issue_id" = "issue_id_p" 2.36 + AND "harmonic_weight" ISNULL 2.37 + AND coalesce("admitted", FALSE) = FALSE 2.38 + ) 2.39 + ) 2.40 ORDER BY "initiative_id" DESC, "weight_den" DESC 2.41 - -- NOTE: latest initiatives treated worse 2.42 + -- NOTE: non-admitted initiatives placed first (at last positions), 2.43 + -- latest initiatives treated worse in case of tie 2.44 LOOP 2.45 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; 2.46 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN 2.47 @@ -199,7 +210,7 @@ 2.48 SELECT * FROM "remaining_harmonic_suggestion_weight_summands" 2.49 WHERE "initiative_id" = "initiative_id_p" 2.50 ORDER BY "suggestion_id" DESC, "weight_den" DESC 2.51 - -- NOTE: latest suggestions treated worse 2.52 + -- NOTE: latest suggestions treated worse in case of tie 2.53 LOOP 2.54 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; 2.55 IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN