liquid_feedback_core

changeset 473:234c9760589d

New view "updated_initiative"
author jbe
date Tue Mar 29 21:46:14 2016 +0200 (2016-03-29)
parents 0fa0d2daa54a
children 5cafa61745bd
files core.sql
line diff
     1.1 --- a/core.sql	Mon Mar 28 14:41:28 2016 +0200
     1.2 +++ b/core.sql	Tue Mar 29 21:46:14 2016 +0200
     1.3 @@ -120,6 +120,7 @@
     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 +        "last_notified_suggestion_id" INT8,
     1.8          "login_recovery_expiry"        TIMESTAMPTZ,
     1.9          "password_reset_secret"        TEXT     UNIQUE,
    1.10          "password_reset_secret_expiry" TIMESTAMPTZ,
    1.11 @@ -2380,6 +2381,32 @@
    1.12  COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"';
    1.13  
    1.14  
    1.15 +CREATE VIEW "updated_initiative" AS
    1.16 +  SELECT
    1.17 +    "member"."id" AS "seen_by_member_id",
    1.18 +    "initiative".*
    1.19 +  FROM "member" CROSS JOIN "initiative"
    1.20 +  JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.21 +  JOIN "supporter" ON
    1.22 +    "supporter"."member_id" = "member"."id" AND
    1.23 +    "supporter"."initiative_id" = "initiative"."id"
    1.24 +  WHERE "issue"."half_frozen" ISNULL AND "issue"."closed" ISNULL
    1.25 +  AND (
    1.26 +    EXISTS (
    1.27 +      SELECT NULL FROM "draft"
    1.28 +      WHERE "draft"."initiative_id" = "initiative"."id"
    1.29 +      AND "draft"."id" > "supporter"."draft_id"
    1.30 +    ) OR EXISTS (
    1.31 +      SELECT NULL FROM "suggestion"
    1.32 +      WHERE "suggestion"."initiative_id" = "initiative"."id"
    1.33 +      AND COALESCE(
    1.34 +        "suggestion"."id" > "member"."last_notified_suggestion_id",
    1.35 +        TRUE
    1.36 +      )
    1.37 +    )
    1.38 +  );
    1.39 +
    1.40 +
    1.41  
    1.42  ------------------------------------------------------
    1.43  -- Row set returning function for delegation chains --

Impressum / About Us