liquid_feedback_core
view 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 |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('2.2.1', 2, 2, 1))
5 AS "subquery"("string", "major", "minor", "revision");
7 ALTER TABLE "initiative" ADD COLUMN "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE;
8 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
10 ALTER TABLE "suggestion" ADD COLUMN "proportional_order" INT4;
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"';
13 CREATE VIEW "initiative_suggestion_order_calculation" AS
14 SELECT
15 "initiative"."id" AS "initiative_id",
16 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
17 FROM "initiative" JOIN "issue"
18 ON "initiative"."issue_id" = "issue"."id"
19 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
20 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
21 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
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';
24 CREATE VIEW "individual_suggestion_ranking" AS
25 SELECT
26 "opinion"."initiative_id",
27 "opinion"."member_id",
28 "direct_interest_snapshot"."weight",
29 CASE WHEN
30 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
31 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
32 THEN 1 ELSE
33 CASE WHEN
34 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
35 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
36 THEN 2 ELSE
37 CASE WHEN
38 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
39 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
40 THEN 3 ELSE 4 END
41 END
42 END AS "preference",
43 "opinion"."suggestion_id"
44 FROM "opinion"
45 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
46 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
47 JOIN "direct_interest_snapshot"
48 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
49 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
50 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
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';
53 COMMIT;