liquid_feedback_core
changeset 392:86b4b44fe3a3
Work on new issue sorting algorithm
author | jbe |
---|---|
date | Thu Oct 10 11:37:00 2013 +0200 (2013-10-10) |
parents | 75c7985a11dc |
children | 25fbce923cf2 |
files | core.sql |
line diff
1.1 --- a/core.sql Mon Aug 12 22:02:34 2013 +0200 1.2 +++ b/core.sql Thu Oct 10 11:37:00 2013 +0200 1.3 @@ -539,6 +539,8 @@ 1.4 "population" INT4, 1.5 "voter_count" INT4, 1.6 "status_quo_schulze_rank" INT4, 1.7 + "order_in_admission_state" INT4, 1.8 + "order_in_open_states" INT4, 1.9 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK ( 1.10 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ), 1.11 CONSTRAINT "valid_state" CHECK ( 1.12 @@ -604,6 +606,8 @@ 1.13 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"'; 1.14 COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting'; 1.15 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; 1.16 +COMMENT ON COLUMN "issue"."order_in_admission_state" IS 'To be used for sorting issues within an area, when showing only issues in admission state; NULL values sort last; updated by "lf_update_issue_order"'; 1.17 +COMMENT ON COLUMN "issue"."order_in_open_states" IS 'To be used for sorting issues within an area, when showing all open issues; NULL values sort last; updated by "lf_update_issue_order"'; 1.18 1.19 1.20 CREATE TABLE "issue_setting" ( 1.21 @@ -2061,6 +2065,23 @@ 1.22 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction'; 1.23 1.24 1.25 +CREATE VIEW "issue_supporter_in_admission_state" AS 1.26 + SELECT DISTINCT 1.27 + "issue"."area_id", 1.28 + "issue"."id" AS "issue_id", 1.29 + "supporter"."member_id", 1.30 + "direct_interest_snapshot"."weight" 1.31 + FROM "issue" 1.32 + JOIN "supporter" ON "supporter"."issue_id" = "issue"."id" 1.33 + JOIN "direct_interest_snapshot" 1.34 + ON "direct_interest_snapshot"."issue_id" = "issue"."id" 1.35 + AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" 1.36 + AND "direct_interest_snapshot"."member_id" = "supporter"."member_id" 1.37 + WHERE "issue"."state" = 'admission'::"issue_state"; 1.38 + 1.39 +COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area'; 1.40 + 1.41 + 1.42 CREATE VIEW "initiative_suggestion_order_calculation" AS 1.43 SELECT 1.44 "initiative"."id" AS "initiative_id",