liquid_feedback_core
changeset 470:a96c920cd6a5
Work on view "new_updated_issue_for_notification"
author | jbe |
---|---|
date | Sun Mar 27 00:58:37 2016 +0100 (2016-03-27) |
parents | c1e283fd6483 |
children | 124b9e7c3c23 |
files | core.sql |
line diff
1.1 --- a/core.sql Sat Mar 26 23:44:56 2016 +0100 1.2 +++ b/core.sql Sun Mar 27 00:58:37 2016 +0100 1.3 @@ -1276,19 +1276,27 @@ 1.4 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.'; 1.5 1.6 1.7 -CREATE TABLE "advertised_initiative" ( -- TODO: two tables: one for initiatives, one for suggestions 1.8 - PRIMARY KEY ("time_serial", "initiative_id", "member_id"), 1.9 - "time_serial" SERIAL8, 1.10 +CREATE TABLE "advertised_initiative" ( 1.11 + PRIMARY KEY ("member_id", "initiative_id", "time_serial"), 1.12 + "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.13 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.14 - "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.15 + "time_serial" SERIAL8 ); 1.16 CREATE INDEX "advertised_initiative_initiative_id_idx" ON "advertised_initiative" ("initiative_id"); 1.17 -CREATE INDEX "advertised_initiative_member_id_idx" ON "advertised_initiative" ("member_id"); 1.18 1.19 COMMENT ON TABLE "advertised_initiative" IS 'Stores which initiatives have been advertised to a member in a mail digest'; 1.20 1.21 COMMENT ON COLUMN "advertised_initiative"."time_serial" IS 'An increasing integer that may be used to determine which issue or initiative was advertised the longest ago (may be equal or consecutive for those initiatives that are sent in the same notification)'; 1.22 1.23 1.24 +CREATE TABLE "advertised_suggestion" ( 1.25 + PRIMARY KEY ("member_id", "suggestion_id"), 1.26 + "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.27 + "suggestion_id" INT4 NOT NULL REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.28 +CREATE INDEX "advertised_suggestion_suggestion_id_idx" ON "advertised_suggestion" ("suggestion_id"); 1.29 + 1.30 +COMMENT ON TABLE "advertised_initiative" IS 'Stores which suggestions have been advertised to a member in a mail digest'; 1.31 + 1.32 + 1.33 1.34 ---------------------------------------------- 1.35 -- Writing of history entries and event log -- 1.36 @@ -2463,13 +2471,46 @@ 1.37 1.38 1.39 CREATE VIEW "new_updated_issue_for_notification" AS 1.40 - SELECT 1.41 - "member"."id" AS "member_id", 1.42 - "issue"."id" AS "issue_id" 1.43 - FROM "member" CROSS JOIN "issue" 1.44 - WHERE FALSE; -- TODO 1.45 - 1.46 -COMMENT ON VIEW "new_updated_issue_for_notification" IS 'TODO'; 1.47 + SELECT DISTINCT ON ("member_id", "area_id") * FROM ( 1.48 + SELECT DISTINCT ON ("member_id", "area_id", "issue_id") 1.49 + "member"."id" AS "member_id", 1.50 + "issue"."area_id" AS "area_id", 1.51 + "issue"."id" AS "issue_id", 1.52 + "advertised_initiative"."time_serial" AS "time_serial" 1.53 + FROM "member" CROSS JOIN "issue" 1.54 + JOIN "area" ON "area"."id" = "issue"."area_id" 1.55 + LEFT JOIN "privilege" 1.56 + ON "privilege"."member_id" = "member"."id" 1.57 + AND "privilege"."unit_id" = "area"."unit_id" 1.58 + LEFT JOIN "subscription" 1.59 + ON "subscription"."member_id" = "member"."id" 1.60 + AND "subscription"."unit_id" = "area"."unit_id" 1.61 + LEFT JOIN "interest" 1.62 + ON "interest"."member_id" = "member"."id" 1.63 + AND "interest"."issue_id" = "issue"."id" 1.64 + LEFT JOIN "ignored_area" 1.65 + ON "ignored_area"."member_id" = "member"."id" 1.66 + AND "ignored_area"."area_id" = "issue"."area_id" 1.67 + LEFT JOIN 1.68 + ( "advertised_initiative" JOIN "initiative" 1.69 + ON "advertised_initiative"."initiative_id" = "initiative"."id" ) 1.70 + ON "advertised_initiative"."member_id" = "member"."id" 1.71 + AND "initiative"."issue_id" = "issue"."id" 1.72 + WHERE 1.73 + ( "privilege"."initiative_right" OR "privilege"."voting_right" OR 1.74 + "subscription"."member_id" NOTNULL ) AND 1.75 + "issue"."state" IN ('admission') AND 1.76 + "interest"."member_id" ISNULL AND 1.77 + "advertised_initiative"."member_id" ISNULL 1.78 + ORDER BY 1.79 + "member_id", 1.80 + "area_id", 1.81 + "issue_id", 1.82 + "time_serial" DESC 1.83 + ) AS "subquery" 1.84 + ORDER BY "member_id", "area_id", "time_serial" ASC; -- TODO: require new initiatives or new initiative draft since last digest 1.85 + 1.86 +COMMENT ON VIEW "new_updated_issue_for_notification" IS 'Helper view for "issue_for_notification" containing one previously advertised issue per "area" which is in ''admission'' phase and has new or updated initiatives for the recipient'; 1.87 1.88 1.89 CREATE VIEW "issue_for_notification" AS