liquid_feedback_core
changeset 490:16536937933e
Require new drafts or new suggestion in area for view "initiative_for_notification"
author | jbe |
---|---|
date | Sun Apr 03 18:00:07 2016 +0200 (2016-04-03) |
parents | 5abcd0043fff |
children | aa94c7dbb20f |
files | core.sql |
line diff
1.1 --- a/core.sql Sun Apr 03 17:04:55 2016 +0200 1.2 +++ b/core.sql Sun Apr 03 18:00:07 2016 +0200 1.3 @@ -2590,11 +2590,24 @@ 1.4 AND "other"."id" = "subquery"."id" 1.5 ); 1.6 1.7 -CREATE VIEW "initiative_for_notification" AS 1.8 +CREATE VIEW "unfiltered_initiative_for_notification" AS 1.9 SELECT * FROM "updated_or_featured_initiative" 1.10 UNION ALL 1.11 SELECT * FROM "leading_complement_initiative"; 1.12 1.13 +CREATE VIEW "initiative_for_notification" AS 1.14 + SELECT "initiative1".* 1.15 + FROM "unfiltered_initiative_for_notification" "initiative1" 1.16 + JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id" 1.17 + WHERE EXISTS ( 1.18 + SELECT NULL 1.19 + FROM "unfiltered_initiative_for_notification" "initiative2" 1.20 + JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id" 1.21 + WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id" 1.22 + AND "issue1"."area_id" = "issue2"."area_id" 1.23 + AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 ) 1.24 + ); 1.25 + 1.26 1.27 1.28 ------------------------------------------------------