1.1 --- a/update/core-update.v3.1.0-v3.2.0.sql	Sun Apr 03 20:57:44 2016 +0200
     1.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql	Mon Apr 04 18:42:56 2016 +0200
     1.3 @@ -4,6 +4,8 @@
     1.4    SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
     1.5    AS "subquery"("string", "major", "minor", "revision");
     1.6  
     1.7 +-- TODO: preliminary script
     1.8 +
     1.9  ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
    1.10  ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;
    1.11  ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
    1.12 @@ -11,6 +13,11 @@
    1.13  ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
    1.14  
    1.15  UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
    1.16 +
    1.17 +DROP TABLE "selected_event_seen_by_member";
    1.18 +DROP TABLE "event_seen_by_member";
    1.19 +ALTER TABLE "member" DROP COLUMN "notify_level";
    1.20 +DROP TYPE "notify_level";
    1.21   
    1.22  CREATE TABLE "subscription" (
    1.23          PRIMARY KEY ("member_id", "unit_id"),
    1.24 @@ -48,78 +55,93 @@
    1.25  CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
    1.26  CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
    1.27  
    1.28 +CREATE VIEW "event_for_notification" AS
    1.29 +  SELECT
    1.30 +    "member"."id" AS "recipient_id",
    1.31 +    "event".*
    1.32 +  FROM "member" CROSS JOIN "event"
    1.33 +  JOIN "issue" ON "issue"."id" = "event"."issue_id"
    1.34 +  JOIN "area" ON "area"."id" = "issue"."area_id"
    1.35 +  LEFT JOIN "privilege" ON
    1.36 +    "privilege"."member_id" = "member"."id" AND
    1.37 +    "privilege"."unit_id" = "area"."unit_id" AND
    1.38 +    "privilege"."voting_right" = TRUE
    1.39 +  LEFT JOIN "subscription" ON
    1.40 +    "subscription"."member_id" = "member"."id" AND
    1.41 +    "subscription"."unit_id" = "area"."unit_id"
    1.42 +  LEFT JOIN "ignored_area" ON
    1.43 +    "ignored_area"."member_id" = "member"."id" AND
    1.44 +    "ignored_area"."area_id" = "issue"."area_id"
    1.45 +  LEFT JOIN "interest" ON
    1.46 +    "interest"."member_id" = "member"."id" AND
    1.47 +    "interest"."issue_id" = "event"."issue_id"
    1.48 +  LEFT JOIN "supporter" ON
    1.49 +    "supporter"."member_id" = "member"."id" AND
    1.50 +    "supporter"."initiative_id" = "event"."initiative_id"
    1.51 +  WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
    1.52 +  AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
    1.53 +  AND (
    1.54 +    "event"."event" = 'issue_state_changed'::"event_type" OR
    1.55 +    ( "event"."event" = 'initiative_revoked'::"event_type" AND
    1.56 +      "supporter"."member_id" NOTNULL ) );
    1.57 +
    1.58  CREATE VIEW "updated_initiative" AS
    1.59    SELECT
    1.60 -    "supporter"."member_id" AS "seen_by_member_id",
    1.61 -    TRUE AS "supported",
    1.62 -    EXISTS (
    1.63 -      SELECT NULL FROM "draft"
    1.64 -      WHERE "draft"."initiative_id" = "initiative"."id"
    1.65 -      AND "draft"."id" > "supporter"."draft_id"
    1.66 -    ) AS "new_draft",
    1.67 -    ( SELECT count(1) FROM "suggestion"
    1.68 -      LEFT JOIN "opinion" ON
    1.69 -        "opinion"."member_id" = "supporter"."member_id" AND
    1.70 -        "opinion"."suggestion_id" = "suggestion"."id"
    1.71 -      WHERE "suggestion"."initiative_id" = "initiative"."id"
    1.72 -      AND "opinion"."member_id" ISNULL
    1.73 -      AND COALESCE(
    1.74 -        "suggestion"."id" > "sent"."last_suggestion_id",
    1.75 -        TRUE
    1.76 -      )
    1.77 -    ) AS "new_suggestion_count",
    1.78 +    "supporter"."member_id" AS "recipient_id",
    1.79      FALSE AS "featured",
    1.80 -    NOT EXISTS (
    1.81 -      SELECT NULL FROM "initiative" AS "better_initiative"
    1.82 -      WHERE
    1.83 -        "better_initiative"."issue_id" = "initiative"."issue_id"
    1.84 -      AND
    1.85 -        ( COALESCE("better_initiative"."harmonic_weight", -1),
    1.86 -          -"better_initiative"."id" ) >
    1.87 -        ( COALESCE("initiative"."harmonic_weight", -1),
    1.88 -          -"initiative"."id" )
    1.89 -    ) AS "leading",
    1.90 -    "initiative".*
    1.91 -  FROM "supporter" JOIN "initiative"
    1.92 -  ON "supporter"."initiative_id" = "initiative"."id"
    1.93 -  LEFT JOIN "initiative_notification_sent" AS "sent"
    1.94 -    ON "sent"."member_id" = "supporter"."member_id"
    1.95 -    AND "sent"."initiative_id" = "initiative"."id"
    1.96 +    "supporter"."initiative_id"
    1.97 +  FROM "supporter"
    1.98 +  JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
    1.99    JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.100 +  LEFT JOIN "initiative_notification_sent" AS "sent" ON
   1.101 +    "sent"."member_id" = "supporter"."member_id" AND
   1.102 +    "sent"."initiative_id" = "supporter"."initiative_id"
   1.103 +  LEFT JOIN "ignored_initiative" ON
   1.104 +    "ignored_initiative"."member_id" = "supporter"."member_id" AND
   1.105 +    "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
   1.106    WHERE "issue"."state" IN ('admission', 'discussion')
   1.107 +  AND "ignored_initiative"."member_id" ISNULL
   1.108    AND (
   1.109      EXISTS (
   1.110        SELECT NULL FROM "draft"
   1.111 -      WHERE "draft"."initiative_id" = "initiative"."id"
   1.112 +      LEFT JOIN "ignored_member" ON
   1.113 +        "ignored_member"."member_id" = "supporter"."member_id" AND
   1.114 +        "ignored_member"."other_member_id" = "draft"."author_id"
   1.115 +      WHERE "draft"."initiative_id" = "supporter"."initiative_id"
   1.116        AND "draft"."id" > "supporter"."draft_id"
   1.117 +      AND "ignored_member"."member_id" ISNULL
   1.118      ) OR EXISTS (
   1.119        SELECT NULL FROM "suggestion"
   1.120        LEFT JOIN "opinion" ON
   1.121          "opinion"."member_id" = "supporter"."member_id" AND
   1.122          "opinion"."suggestion_id" = "suggestion"."id"
   1.123 -      WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.124 +      LEFT JOIN "ignored_member" ON
   1.125 +        "ignored_member"."member_id" = "supporter"."member_id" AND
   1.126 +        "ignored_member"."other_member_id" = "suggestion"."author_id"
   1.127 +      WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
   1.128        AND "opinion"."member_id" ISNULL
   1.129 -      AND COALESCE(
   1.130 -        "suggestion"."id" > "sent"."last_suggestion_id",
   1.131 -        TRUE
   1.132 -      )
   1.133 +      AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   1.134 +      AND "ignored_member"."member_id" ISNULL
   1.135      )
   1.136    );
   1.137  
   1.138  CREATE FUNCTION "featured_initiative"
   1.139 -  ( "member_id_p" "member"."id"%TYPE,
   1.140 -    "area_id_p"   "area"."id"%TYPE )
   1.141 -  RETURNS SETOF "initiative"
   1.142 +  ( "recipient_id_p" "member"."id"%TYPE,
   1.143 +    "area_id_p"      "area"."id"%TYPE )
   1.144 +  RETURNS SETOF "initiative"."id"%TYPE
   1.145    LANGUAGE 'plpgsql' STABLE AS $$
   1.146      DECLARE
   1.147 -      "member_row"        "member"%ROWTYPE;
   1.148 +      "counter_v"         "member"."notification_counter"%TYPE;
   1.149 +      "sample_size_v"     "member"."notification_sample_size"%TYPE;
   1.150 +      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
   1.151 +      "match_v"           BOOLEAN;
   1.152        "member_id_v"       "member"."id"%TYPE;
   1.153        "seed_v"            TEXT;
   1.154 -      "result_row"        "initiative"%ROWTYPE;
   1.155 -      "match_v"           BOOLEAN;
   1.156 -      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
   1.157 +      "initiative_id_v"   "initiative"."id"%TYPE;
   1.158      BEGIN
   1.159 -      SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_id_p";
   1.160 +      SELECT "notification_counter", "notification_sample_size"
   1.161 +        INTO "counter_v", "sample_size_v"
   1.162 +        FROM "member" WHERE "id" = "recipient_id_p";
   1.163        "initiative_id_ary" := '{}';
   1.164        LOOP
   1.165          "match_v" := FALSE;
   1.166 @@ -127,38 +149,64 @@
   1.167            SELECT * FROM (
   1.168              SELECT DISTINCT
   1.169                "supporter"."member_id",
   1.170 -              md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed"
   1.171 +              md5(
   1.172 +                "recipient_id_p" || '-' ||
   1.173 +                "counter_v"      || '-' ||
   1.174 +                "area_id_p"      || '-' ||
   1.175 +                "supporter"."member_id"
   1.176 +              ) AS "seed"
   1.177              FROM "supporter"
   1.178 -            JOIN "member" ON "member"."id" = "supporter"."member_id"
   1.179              JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
   1.180              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.181 -            WHERE "supporter"."member_id" != "member_id_p"
   1.182 +            WHERE "supporter"."member_id" != "recipient_id_p"
   1.183              AND "issue"."area_id" = "area_id_p"
   1.184              AND "issue"."state" IN ('admission', 'discussion', 'verification')
   1.185            ) AS "subquery"
   1.186            ORDER BY "seed"
   1.187          LOOP
   1.188 -          SELECT "initiative".* INTO "result_row"
   1.189 +          SELECT "initiative"."id" INTO "initiative_id_v"
   1.190              FROM "initiative"
   1.191              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.192 +            JOIN "area" ON "area"."id" = "issue"."area_id"
   1.193              JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
   1.194              LEFT JOIN "supporter" AS "self_support" ON
   1.195                "self_support"."initiative_id" = "initiative"."id" AND
   1.196 -              "self_support"."member_id" = "member_id_p"
   1.197 +              "self_support"."member_id" = "recipient_id_p"
   1.198 +            LEFT JOIN "privilege" ON
   1.199 +              "privilege"."member_id" = "recipient_id_p" AND
   1.200 +              "privilege"."unit_id" = "area"."unit_id" AND
   1.201 +              "privilege"."voting_right" = TRUE
   1.202 +            LEFT JOIN "subscription" ON
   1.203 +              "subscription"."member_id" = "recipient_id_p" AND
   1.204 +              "subscription"."unit_id" = "area"."unit_id"
   1.205 +            LEFT JOIN "ignored_initiative" ON
   1.206 +              "ignored_initiative"."member_id" = "recipient_id_p" AND
   1.207 +              "ignored_initiative"."initiative_id" = "initiative"."id"
   1.208              WHERE "supporter"."member_id" = "member_id_v"
   1.209              AND "issue"."area_id" = "area_id_p"
   1.210              AND "issue"."state" IN ('admission', 'discussion', 'verification')
   1.211              AND "self_support"."member_id" ISNULL
   1.212              AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
   1.213 +            AND (
   1.214 +              "privilege"."member_id" NOTNULL OR
   1.215 +              "subscription"."member_id" NOTNULL )
   1.216 +            AND "ignored_initiative"."member_id" ISNULL
   1.217 +            AND NOT EXISTS (
   1.218 +              SELECT NULL FROM "draft"
   1.219 +              JOIN "ignored_member" ON
   1.220 +                "ignored_member"."member_id" = "recipient_id_p" AND
   1.221 +                "ignored_member"."other_member_id" = "draft"."author_id"
   1.222 +              WHERE "draft"."initiative_id" = "initiative"."id"
   1.223 +            )
   1.224              ORDER BY md5("seed_v" || '-' || "initiative"."id")
   1.225              LIMIT 1;
   1.226            IF FOUND THEN
   1.227              "match_v" := TRUE;
   1.228 -            "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
   1.229 -            RETURN NEXT "result_row";
   1.230 -            IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN
   1.231 +            RETURN NEXT "initiative_id_v";
   1.232 +            IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
   1.233                RETURN;
   1.234              END IF;
   1.235 +            "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
   1.236            END IF;
   1.237          END LOOP;
   1.238          EXIT WHEN NOT "match_v";
   1.239 @@ -168,115 +216,120 @@
   1.240    $$;
   1.241  
   1.242  CREATE VIEW "updated_or_featured_initiative" AS
   1.243 -  SELECT * FROM "updated_initiative"
   1.244 -  UNION ALL
   1.245    SELECT
   1.246 -    "member"."id" AS "seen_by_member_id",
   1.247 -    FALSE AS "supported",
   1.248 -    EXISTS (
   1.249 -      SELECT NULL FROM "draft"
   1.250 -      WHERE "draft"."initiative_id" = "initiative"."id"
   1.251 -      AND COALESCE(
   1.252 -        "draft"."id" > "sent"."last_draft_id",
   1.253 -        TRUE
   1.254 -      )
   1.255 -    ) AS "new_draft",
   1.256 -    ( SELECT count(1) FROM "suggestion"
   1.257 -      WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.258 -      AND COALESCE(
   1.259 -        "suggestion"."id" > "sent"."last_suggestion_id",
   1.260 -        TRUE
   1.261 -      )
   1.262 -    ) AS "new_suggestion_count",
   1.263 -    TRUE AS "featured",
   1.264 +    "subquery".*,
   1.265      NOT EXISTS (
   1.266        SELECT NULL FROM "initiative" AS "better_initiative"
   1.267 -      WHERE
   1.268 -        "better_initiative"."issue_id" = "initiative"."issue_id"
   1.269 +      WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
   1.270        AND
   1.271          ( COALESCE("better_initiative"."harmonic_weight", -1),
   1.272            -"better_initiative"."id" ) >
   1.273          ( COALESCE("initiative"."harmonic_weight", -1),
   1.274            -"initiative"."id" )
   1.275 -    ) AS "leading",
   1.276 -    "initiative".*
   1.277 -  FROM "member" CROSS JOIN "area"
   1.278 -  CROSS JOIN LATERAL
   1.279 -    "featured_initiative"("member"."id", "area"."id") AS "initiative"
   1.280 -  LEFT JOIN "initiative_notification_sent" AS "sent"
   1.281 -    ON "sent"."member_id" = "member"."id"
   1.282 -    AND "sent"."initiative_id" = "initiative"."id";
   1.283 +    ) AS "leading"
   1.284 +  FROM (
   1.285 +    SELECT * FROM "updated_initiative"
   1.286 +    UNION ALL
   1.287 +    SELECT
   1.288 +      "member"."id" AS "recipient_id",
   1.289 +      TRUE AS "featured",
   1.290 +      "featured_initiative_id" AS "initiative_id"
   1.291 +    FROM "member" CROSS JOIN "area"
   1.292 +    CROSS JOIN LATERAL
   1.293 +      "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
   1.294 +    JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
   1.295 +  ) AS "subquery"
   1.296 +  JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
   1.297  
   1.298  CREATE VIEW "leading_complement_initiative" AS
   1.299    SELECT * FROM (
   1.300 -    SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
   1.301 -      "uf_initiative"."seen_by_member_id",
   1.302 -      "supporter"."member_id" NOTNULL AS "supported",
   1.303 -      CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE
   1.304 -        EXISTS (
   1.305 -          SELECT NULL FROM "draft"
   1.306 -          WHERE "draft"."initiative_id" = "initiative"."id"
   1.307 -          AND COALESCE(
   1.308 -            "draft"."id" > "sent"."last_draft_id",
   1.309 -            TRUE
   1.310 -          )
   1.311 -        )
   1.312 -      END AS "new_draft",
   1.313 -      CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE
   1.314 -        ( SELECT count(1) FROM "suggestion"
   1.315 -          WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.316 -          AND COALESCE(
   1.317 -            "suggestion"."id" > "sent"."last_suggestion_id",
   1.318 -            TRUE
   1.319 -          )
   1.320 -        )
   1.321 -      END AS "new_suggestion_count",
   1.322 +    SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
   1.323 +      "uf_initiative"."recipient_id",
   1.324        FALSE AS "featured",
   1.325 -      TRUE AS "leading",
   1.326 -      "initiative".*
   1.327 +      "uf_initiative"."initiative_id",
   1.328 +      TRUE AS "leading"
   1.329      FROM "updated_or_featured_initiative" AS "uf_initiative"
   1.330 +    JOIN "initiative" AS "uf_initiative_full" ON
   1.331 +      "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
   1.332      JOIN "initiative" ON
   1.333 -      "uf_initiative"."issue_id" = "initiative"."issue_id"
   1.334 -    LEFT JOIN "supporter" ON
   1.335 -      "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND
   1.336 -      "supporter"."initiative_id" = "initiative"."id"
   1.337 -    LEFT JOIN "initiative_notification_sent" AS "sent"
   1.338 -      ON "sent"."member_id" = "uf_initiative"."seen_by_member_id"
   1.339 -      AND "sent"."initiative_id" = "initiative"."id"
   1.340 +      "initiative"."issue_id" = "uf_initiative_full"."issue_id"
   1.341      ORDER BY
   1.342 -      "seen_by_member_id",
   1.343 +      "uf_initiative"."recipient_id",
   1.344        "initiative"."issue_id",
   1.345        "initiative"."harmonic_weight" DESC,
   1.346        "initiative"."id"
   1.347    ) AS "subquery"
   1.348    WHERE NOT EXISTS (
   1.349      SELECT NULL FROM "updated_or_featured_initiative" AS "other"
   1.350 -    WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"
   1.351 -    AND "other"."id" = "subquery"."id"
   1.352 +    WHERE "other"."recipient_id" = "subquery"."recipient_id"
   1.353 +    AND "other"."initiative_id" = "subquery"."initiative_id"
   1.354    );
   1.355  
   1.356  CREATE VIEW "unfiltered_initiative_for_notification" AS
   1.357 -  SELECT * FROM "updated_or_featured_initiative"
   1.358 -  UNION ALL
   1.359 -  SELECT * FROM "leading_complement_initiative";
   1.360 +  SELECT
   1.361 +    "subquery".*,
   1.362 +    "supporter"."member_id" NOTNULL AS "supported",
   1.363 +    CASE WHEN "supporter"."member_id" NOTNULL THEN
   1.364 +      EXISTS (
   1.365 +        SELECT NULL FROM "draft"
   1.366 +        WHERE "draft"."initiative_id" = "subquery"."initiative_id"
   1.367 +        AND "draft"."id" > "supporter"."draft_id"
   1.368 +      )
   1.369 +    ELSE
   1.370 +      EXISTS (
   1.371 +        SELECT NULL FROM "draft"
   1.372 +        WHERE "draft"."initiative_id" = "subquery"."initiative_id"
   1.373 +        AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
   1.374 +      )
   1.375 +    END AS "new_draft",
   1.376 +    CASE WHEN "supporter"."member_id" NOTNULL THEN
   1.377 +      ( SELECT count(1) FROM "suggestion"
   1.378 +        LEFT JOIN "opinion" ON
   1.379 +          "opinion"."member_id" = "supporter"."member_id" AND
   1.380 +          "opinion"."suggestion_id" = "suggestion"."id"
   1.381 +        WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
   1.382 +        AND "opinion"."member_id" ISNULL
   1.383 +        AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   1.384 +      )
   1.385 +    ELSE
   1.386 +      ( SELECT count(1) FROM "suggestion"
   1.387 +        WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
   1.388 +        AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   1.389 +      )
   1.390 +    END AS "new_suggestion_count"
   1.391 +  FROM (
   1.392 +    SELECT * FROM "updated_or_featured_initiative"
   1.393 +    UNION ALL
   1.394 +    SELECT * FROM "leading_complement_initiative"
   1.395 +  ) AS "subquery"
   1.396 +  LEFT JOIN "supporter" ON
   1.397 +    "supporter"."member_id" = "subquery"."recipient_id" AND
   1.398 +    "supporter"."initiative_id" = "subquery"."initiative_id"
   1.399 +  LEFT JOIN "initiative_notification_sent" AS "sent" ON
   1.400 +    "sent"."member_id" = "subquery"."recipient_id" AND
   1.401 +    "sent"."initiative_id" = "subquery"."initiative_id";
   1.402  
   1.403  CREATE VIEW "initiative_for_notification" AS
   1.404 -  SELECT "initiative1".*
   1.405 -  FROM "unfiltered_initiative_for_notification" "initiative1"
   1.406 -  JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id"
   1.407 +  SELECT "unfiltered1".*
   1.408 +  FROM "unfiltered_initiative_for_notification" "unfiltered1"
   1.409 +  JOIN "initiative" AS "initiative1" ON
   1.410 +    "initiative1"."id" = "unfiltered1"."initiative_id"
   1.411 +  JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
   1.412    WHERE EXISTS (
   1.413      SELECT NULL
   1.414 -    FROM "unfiltered_initiative_for_notification" "initiative2"
   1.415 -    JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id"
   1.416 -    WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id"
   1.417 +    FROM "unfiltered_initiative_for_notification" "unfiltered2"
   1.418 +    JOIN "initiative" AS "initiative2" ON
   1.419 +      "initiative2"."id" = "unfiltered2"."initiative_id"
   1.420 +    JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
   1.421 +    WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
   1.422      AND "issue1"."area_id" = "issue2"."area_id"
   1.423 -    AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 )
   1.424 +    AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
   1.425    );
   1.426  
   1.427  CREATE VIEW "newsletter_to_send" AS
   1.428    SELECT
   1.429 -    "newsletter"."id" AS "newsletter_id",
   1.430 -    "member"."id" AS "member_id"
   1.431 +    "member"."id" AS "recipient_id",
   1.432 +    "newsletter"."id" AS "newsletter_id"
   1.433    FROM "newsletter" CROSS JOIN "member"
   1.434    LEFT JOIN "privilege" ON
   1.435      "privilege"."member_id" = "member"."id" AND
   1.436 @@ -309,19 +362,19 @@
   1.437        PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE;
   1.438        FOR "result_row" IN
   1.439          SELECT * FROM "initiative_for_notification"
   1.440 -        WHERE "seen_by_member_id" = "member_id_p"
   1.441 +        WHERE "member_id" = "member_id_p"
   1.442        LOOP
   1.443          SELECT "id" INTO "last_draft_id_v" FROM "draft"
   1.444 -          WHERE "draft"."initiative_id" = "result_row"."id"
   1.445 +          WHERE "draft"."initiative_id" = "result_row"."initiative_id"
   1.446            ORDER BY "id" DESC LIMIT 1;
   1.447          SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
   1.448 -          WHERE "suggestion"."initiative_id" = "result_row"."id"
   1.449 +          WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
   1.450            ORDER BY "id" DESC LIMIT 1;
   1.451          INSERT INTO "initiative_notification_sent"
   1.452            ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
   1.453            VALUES (
   1.454              "member_id_p",
   1.455 -            "result_row"."id",
   1.456 +            "result_row"."initiative_id",
   1.457              "last_draft_id_v",
   1.458              "last_suggestion_id_v" )
   1.459            ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET