# HG changeset patch
# User jbe
# Date 1459699207 -7200
# Node ID 16536937933ea345812f3c71a8575cd27aa5ec33
# Parent  5abcd0043fffbcd0ac24257c001ba25dba48f12d
Require new drafts or new suggestion in area for view "initiative_for_notification"

diff -r 5abcd0043fff -r 16536937933e core.sql
--- a/core.sql	Sun Apr 03 17:04:55 2016 +0200
+++ b/core.sql	Sun Apr 03 18:00:07 2016 +0200
@@ -2590,11 +2590,24 @@
     AND "other"."id" = "subquery"."id"
   );
 
-CREATE VIEW "initiative_for_notification" AS
+CREATE VIEW "unfiltered_initiative_for_notification" AS
   SELECT * FROM "updated_or_featured_initiative"
   UNION ALL
   SELECT * FROM "leading_complement_initiative";
 
+CREATE VIEW "initiative_for_notification" AS
+  SELECT "initiative1".*
+  FROM "unfiltered_initiative_for_notification" "initiative1"
+  JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id"
+  WHERE EXISTS (
+    SELECT NULL
+    FROM "unfiltered_initiative_for_notification" "initiative2"
+    JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id"
+    WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id"
+    AND "issue1"."area_id" = "issue2"."area_id"
+    AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 )
+  );
+
 
 
 ------------------------------------------------------