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",

Impressum / About Us