liquid_feedback_core

changeset 474:5cafa61745bd

New view "initiative_for_notification" using helper views/functions "updated_initiative", "featured_initiative", and "leading_complement_initiative"
author jbe
date Wed Mar 30 02:15:30 2016 +0200 (2016-03-30)
parents 234c9760589d
children ecac8ab6ca04
files core.sql
line diff
     1.1 --- a/core.sql	Tue Mar 29 21:46:14 2016 +0200
     1.2 +++ b/core.sql	Wed Mar 30 02:15:30 2016 +0200
     1.3 @@ -120,6 +120,8 @@
     1.4          "notify_email_secret_expiry"   TIMESTAMPTZ,
     1.5          "notify_email_lock_expiry"     TIMESTAMPTZ,
     1.6          "disable_notifications" BOOLEAN         NOT NULL DEFAULT FALSE,
     1.7 +        "notification_counter"  INT4            NOT NULL DEFAULT 0,
     1.8 +        "sample_size"           INT4            NOT NULL DEFAULT 3,
     1.9          "last_notified_suggestion_id" INT8,
    1.10          "login_recovery_expiry"        TIMESTAMPTZ,
    1.11          "password_reset_secret"        TEXT     UNIQUE,
    1.12 @@ -2390,7 +2392,7 @@
    1.13    JOIN "supporter" ON
    1.14      "supporter"."member_id" = "member"."id" AND
    1.15      "supporter"."initiative_id" = "initiative"."id"
    1.16 -  WHERE "issue"."half_frozen" ISNULL AND "issue"."closed" ISNULL
    1.17 +  WHERE "issue"."state" IN ('admission', 'discussion') ISNULL
    1.18    AND (
    1.19      EXISTS (
    1.20        SELECT NULL FROM "draft"
    1.21 @@ -2406,6 +2408,93 @@
    1.22      )
    1.23    );
    1.24  
    1.25 +CREATE FUNCTION "featured_initiative"
    1.26 +  ( "member_id_p" "member"."id"%TYPE,
    1.27 +    "area_id_p"   "area"."id"%TYPE )
    1.28 +  RETURNS SETOF "initiative"
    1.29 +  LANGUAGE 'plpgsql' STABLE AS $$
    1.30 +    DECLARE
    1.31 +      "sample_size_v"     INT4;
    1.32 +      "member_id_v"       "member"."id"%TYPE;
    1.33 +      "seed_v"            TEXT;
    1.34 +      "result_row"        "initiative"%ROWTYPE;
    1.35 +      "match_v"           BOOLEAN;
    1.36 +      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
    1.37 +    BEGIN
    1.38 +      SELECT INTO "sample_size_v" "sample_size" FROM "member" WHERE "id" = "member_id_p";
    1.39 +      "initiative_id_ary" := '{}';
    1.40 +      LOOP
    1.41 +        "match_v" := FALSE;
    1.42 +        FOR "member_id_v", "seed_v" IN
    1.43 +          SELECT * FROM (
    1.44 +            SELECT DISTINCT
    1.45 +              "supporter"."member_id",
    1.46 +              md5("member_id" || '-' || "member"."notification_counter" || '-' || "area_id_p") AS "seed"
    1.47 +            FROM "supporter"
    1.48 +            JOIN "member" ON "member"."id" = "supporter"."member_id"
    1.49 +            JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
    1.50 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.51 +            WHERE "supporter"."member_id" != "member_id_p"
    1.52 +            AND "issue"."area_id" = "area_id_p"
    1.53 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
    1.54 +          ) AS "subquery"
    1.55 +          ORDER BY "seed"
    1.56 +        LOOP
    1.57 +          FOR "result_row" IN
    1.58 +            SELECT "initiative".* FROM "initiative"
    1.59 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.60 +            JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
    1.61 +            LEFT JOIN "supporter" AS "self_support" ON
    1.62 +              "self_support"."initiative_id" = "initiative"."id" AND
    1.63 +              "self_support"."member_id" = "member_id_p"
    1.64 +            WHERE "supporter"."member_id" = "member_id_v"
    1.65 +            AND "issue"."area_id" = "area_id_p"
    1.66 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
    1.67 +            AND "self_support"."member_id" ISNULL
    1.68 +            ORDER BY md5("seed_v" || '-' || "initiative"."id")
    1.69 +          LOOP
    1.70 +            IF NOT "initiative_id_ary" @> ARRAY["result_row"."id"] THEN
    1.71 +              "match_v" := TRUE;
    1.72 +              "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
    1.73 +              RETURN NEXT "result_row";
    1.74 +              IF array_length("initiative_id_ary", 1) >= "sample_size_v" THEN
    1.75 +                RETURN;
    1.76 +              END IF;
    1.77 +            END IF;
    1.78 +          END LOOP;
    1.79 +        END LOOP;
    1.80 +        EXIT WHEN NOT "match_v";
    1.81 +      END LOOP;
    1.82 +      RETURN;
    1.83 +    END;
    1.84 +  $$;
    1.85 +
    1.86 +CREATE VIEW "updated_or_featured_initiative" AS
    1.87 +  SELECT * FROM "updated_initiative"
    1.88 +  UNION ALL
    1.89 +  SELECT
    1.90 +    "member"."id" AS "seen_by_member_id",
    1.91 +    "featured_initiative".*
    1.92 +  FROM "member" CROSS JOIN "area"
    1.93 +  CROSS JOIN LATERAL "featured_initiative"("member"."id", "area"."id");
    1.94 +
    1.95 +CREATE VIEW "leading_complement_initiative" AS
    1.96 +  SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
    1.97 +    "updated_or_featured_initiative"."seen_by_member_id",
    1.98 +    "initiative".*
    1.99 +  FROM "updated_or_featured_initiative"
   1.100 +  JOIN "initiative"
   1.101 +  ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id"
   1.102 +  ORDER BY
   1.103 +    "seen_by_member_id",
   1.104 +    "initiative"."issue_id",
   1.105 +    "initiative"."harmonic_weight" DESC;
   1.106 +
   1.107 +CREATE VIEW "initiative_for_notification" AS
   1.108 +  SELECT * FROM "updated_or_featured_initiative"
   1.109 +  UNION
   1.110 +  SELECT * FROM "leading_complement_initiative";
   1.111 +
   1.112  
   1.113  
   1.114  ------------------------------------------------------

Impressum / About Us