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