liquid_feedback_core
annotate update/core-update.v2.2.0-v2.2.1.sql @ 378:e88d0606891f
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
author | jbe |
---|---|
date | Mon Mar 18 09:36:21 2013 +0100 (2013-03-18) |
parents | 47965760b1b8 |
children |
rev | line source |
---|---|
jbe@360 | 1 BEGIN; |
jbe@360 | 2 |
jbe@360 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@360 | 4 SELECT * FROM (VALUES ('2.2.1', 2, 2, 1)) |
jbe@360 | 5 AS "subquery"("string", "major", "minor", "revision"); |
jbe@360 | 6 |
jbe@360 | 7 ALTER TABLE "initiative" ADD COLUMN "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE; |
jbe@360 | 8 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time'; |
jbe@360 | 9 |
jbe@360 | 10 ALTER TABLE "suggestion" ADD COLUMN "proportional_order" INT4; |
jbe@378 | 11 COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; NULL values sort last; updated by "lf_update_suggestion_order"'; |
jbe@360 | 12 |
jbe@360 | 13 CREATE VIEW "initiative_suggestion_order_calculation" AS |
jbe@360 | 14 SELECT |
jbe@360 | 15 "initiative"."id" AS "initiative_id", |
jbe@360 | 16 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final" |
jbe@360 | 17 FROM "initiative" JOIN "issue" |
jbe@360 | 18 ON "initiative"."issue_id" = "issue"."id" |
jbe@360 | 19 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL) |
jbe@360 | 20 OR ("initiative"."final_suggestion_order_calculated" = FALSE); |
jbe@360 | 21 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated'; |
jbe@360 | 22 COMMENT ON COLUMN "initiative_suggestion_order_calculation"."final" IS 'Set to TRUE, if the issue is fully frozen or closed, and the calculation has to be done only once for one last time'; |
jbe@360 | 23 |
jbe@360 | 24 CREATE VIEW "individual_suggestion_ranking" AS |
jbe@360 | 25 SELECT |
jbe@360 | 26 "opinion"."initiative_id", |
jbe@360 | 27 "opinion"."member_id", |
jbe@360 | 28 "direct_interest_snapshot"."weight", |
jbe@360 | 29 CASE WHEN |
jbe@360 | 30 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR |
jbe@360 | 31 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE) |
jbe@360 | 32 THEN 1 ELSE |
jbe@360 | 33 CASE WHEN |
jbe@360 | 34 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR |
jbe@360 | 35 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE) |
jbe@360 | 36 THEN 2 ELSE |
jbe@360 | 37 CASE WHEN |
jbe@360 | 38 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR |
jbe@360 | 39 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE) |
jbe@360 | 40 THEN 3 ELSE 4 END |
jbe@360 | 41 END |
jbe@360 | 42 END AS "preference", |
jbe@360 | 43 "opinion"."suggestion_id" |
jbe@360 | 44 FROM "opinion" |
jbe@360 | 45 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id" |
jbe@360 | 46 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" |
jbe@360 | 47 JOIN "direct_interest_snapshot" |
jbe@360 | 48 ON "direct_interest_snapshot"."issue_id" = "issue"."id" |
jbe@360 | 49 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@360 | 50 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id"; |
jbe@360 | 51 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative'; |
jbe@360 | 52 |
jbe@360 | 53 COMMIT; |