liquid_feedback_core
changeset 489:5abcd0043fff
Fill columns "new_draft" and "new_suggestion_count" in views "updated_or_featured_initiative" and "leading_complement_initiative"
author | jbe |
---|---|
date | Sun Apr 03 17:04:55 2016 +0200 (2016-04-03) |
parents | 056ad11bbe66 |
children | 16536937933e |
files | core.sql |
line diff
1.1 --- a/core.sql Sun Apr 03 16:42:01 2016 +0200 1.2 +++ b/core.sql Sun Apr 03 17:04:55 2016 +0200 1.3 @@ -2508,8 +2508,21 @@ 1.4 SELECT 1.5 "member"."id" AS "seen_by_member_id", 1.6 FALSE AS "supported", 1.7 - NULL::BOOLEAN AS "new_draft", 1.8 - NULL::INTEGER AS "new_suggestion_count", 1.9 + EXISTS ( 1.10 + SELECT NULL FROM "draft" 1.11 + WHERE "draft"."initiative_id" = "initiative"."id" 1.12 + AND COALESCE( 1.13 + "draft"."id" > "sent"."last_draft_id", 1.14 + TRUE 1.15 + ) 1.16 + ) AS "new_draft", 1.17 + ( SELECT count(1) FROM "suggestion" 1.18 + WHERE "suggestion"."initiative_id" = "initiative"."id" 1.19 + AND COALESCE( 1.20 + "suggestion"."id" > "sent"."last_suggestion_id", 1.21 + TRUE 1.22 + ) 1.23 + ) AS "new_suggestion_count", 1.24 TRUE AS "featured", 1.25 NOT EXISTS ( 1.26 SELECT NULL FROM "initiative" AS "better_initiative" 1.27 @@ -2524,21 +2537,47 @@ 1.28 "initiative".* 1.29 FROM "member" CROSS JOIN "area" 1.30 CROSS JOIN LATERAL 1.31 - "featured_initiative"("member"."id", "area"."id") AS "initiative"; 1.32 + "featured_initiative"("member"."id", "area"."id") AS "initiative" 1.33 + LEFT JOIN "initiative_notification_sent" AS "sent" 1.34 + ON "sent"."member_id" = "member"."id" 1.35 + AND "sent"."initiative_id" = "initiative"."id"; 1.36 1.37 CREATE VIEW "leading_complement_initiative" AS 1.38 SELECT * FROM ( 1.39 SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id") 1.40 - "updated_or_featured_initiative"."seen_by_member_id", 1.41 - FALSE AS "supported", 1.42 - NULL::BOOLEAN AS "new_draft", 1.43 - NULL::INTEGER AS "new_suggestion_count", 1.44 + "uf_initiative"."seen_by_member_id", 1.45 + "supporter"."member_id" NOTNULL AS "supported", 1.46 + CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE 1.47 + EXISTS ( 1.48 + SELECT NULL FROM "draft" 1.49 + WHERE "draft"."initiative_id" = "initiative"."id" 1.50 + AND COALESCE( 1.51 + "draft"."id" > "sent"."last_draft_id", 1.52 + TRUE 1.53 + ) 1.54 + ) 1.55 + END AS "new_draft", 1.56 + CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE 1.57 + ( SELECT count(1) FROM "suggestion" 1.58 + WHERE "suggestion"."initiative_id" = "initiative"."id" 1.59 + AND COALESCE( 1.60 + "suggestion"."id" > "sent"."last_suggestion_id", 1.61 + TRUE 1.62 + ) 1.63 + ) 1.64 + END AS "new_suggestion_count", 1.65 FALSE AS "featured", 1.66 TRUE AS "leading", 1.67 "initiative".* 1.68 - FROM "updated_or_featured_initiative" 1.69 - JOIN "initiative" 1.70 - ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id" 1.71 + FROM "updated_or_featured_initiative" AS "uf_initiative" 1.72 + JOIN "initiative" ON 1.73 + "uf_initiative"."issue_id" = "initiative"."issue_id" 1.74 + LEFT JOIN "supporter" ON 1.75 + "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND 1.76 + "supporter"."initiative_id" = "initiative"."id" 1.77 + LEFT JOIN "initiative_notification_sent" AS "sent" 1.78 + ON "sent"."member_id" = "uf_initiative"."seen_by_member_id" 1.79 + AND "sent"."initiative_id" = "initiative"."id" 1.80 ORDER BY 1.81 "seen_by_member_id", 1.82 "initiative"."issue_id",