liquid_feedback_core
changeset 477:eff90049fcff
New columns "supported", "new_draft", "new_suggestion_count", "featured", "leading" in view "initiative_for_notification" and associated helper views
author | jbe |
---|---|
date | Wed Mar 30 18:08:32 2016 +0200 (2016-03-30) |
parents | c463d280947c |
children | 2f79434d0226 |
files | core.sql |
line diff
1.1 --- a/core.sql Wed Mar 30 16:52:24 2016 +0200 1.2 +++ b/core.sql Wed Mar 30 18:08:32 2016 +0200 1.3 @@ -2386,6 +2386,26 @@ 1.4 CREATE VIEW "updated_initiative" AS 1.5 SELECT 1.6 "member"."id" AS "seen_by_member_id", 1.7 + TRUE AS "supported", 1.8 + EXISTS ( 1.9 + SELECT NULL FROM "draft" 1.10 + WHERE "draft"."initiative_id" = "initiative"."id" 1.11 + AND "draft"."id" > "supporter"."draft_id" 1.12 + ) AS "new_draft", 1.13 + ( SELECT count(1) FROM "suggestion" 1.14 + WHERE "suggestion"."initiative_id" = "initiative"."id" 1.15 + AND COALESCE( 1.16 + "suggestion"."id" > "member"."last_notified_suggestion_id", 1.17 + TRUE 1.18 + ) 1.19 + ) AS "new_suggestion_count", 1.20 + FALSE AS "featured", 1.21 + NOT EXISTS ( 1.22 + SELECT NULL FROM "initiative" AS "better_initiative" 1.23 + WHERE 1.24 + ("better_initiative"."harmonic_weight", -"better_initiative"."id") > 1.25 + ("initiative"."harmonic_weight", -"better_initiative"."id") 1.26 + ) AS "leading", 1.27 "initiative".* 1.28 FROM "member" CROSS JOIN "initiative" 1.29 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.30 @@ -2474,25 +2494,49 @@ 1.31 UNION ALL 1.32 SELECT 1.33 "member"."id" AS "seen_by_member_id", 1.34 - "featured_initiative".* 1.35 + FALSE AS "supported", 1.36 + NULL::BOOLEAN AS "new_draft", 1.37 + NULL::INTEGER AS "new_suggestion_count", 1.38 + TRUE AS "featured", 1.39 + NOT EXISTS ( 1.40 + SELECT NULL FROM "initiative" AS "better_initiative" 1.41 + WHERE 1.42 + ("better_initiative"."harmonic_weight", -"better_initiative"."id") > 1.43 + ("initiative"."harmonic_weight", -"better_initiative"."id") 1.44 + ) AS "leading", 1.45 + "initiative".* 1.46 FROM "member" CROSS JOIN "area" 1.47 - CROSS JOIN LATERAL "featured_initiative"("member"."id", "area"."id"); 1.48 + CROSS JOIN LATERAL 1.49 + "featured_initiative"("member"."id", "area"."id") AS "initiative"; 1.50 1.51 CREATE VIEW "leading_complement_initiative" AS 1.52 - SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id") 1.53 - "updated_or_featured_initiative"."seen_by_member_id", 1.54 - "initiative".* 1.55 - FROM "updated_or_featured_initiative" 1.56 - JOIN "initiative" 1.57 - ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id" 1.58 - ORDER BY 1.59 - "seen_by_member_id", 1.60 - "initiative"."issue_id", 1.61 - "initiative"."harmonic_weight" DESC; 1.62 + SELECT * FROM ( 1.63 + SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id") 1.64 + "updated_or_featured_initiative"."seen_by_member_id", 1.65 + FALSE AS "supported", 1.66 + NULL::BOOLEAN AS "new_draft", 1.67 + NULL::INTEGER AS "new_suggestion_count", 1.68 + FALSE AS "featured", 1.69 + TRUE AS "leading", 1.70 + "initiative".* 1.71 + FROM "updated_or_featured_initiative" 1.72 + JOIN "initiative" 1.73 + ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id" 1.74 + ORDER BY 1.75 + "seen_by_member_id", 1.76 + "initiative"."issue_id", 1.77 + "initiative"."harmonic_weight" DESC, 1.78 + "initiative"."id" 1.79 + ) AS "subquery" 1.80 + WHERE NOT EXISTS ( 1.81 + SELECT NULL FROM "updated_or_featured_initiative" AS "other" 1.82 + WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id" 1.83 + AND "other"."id" = "subquery"."id" 1.84 + ); 1.85 1.86 CREATE VIEW "initiative_for_notification" AS 1.87 SELECT * FROM "updated_or_featured_initiative" 1.88 - UNION 1.89 + UNION ALL 1.90 SELECT * FROM "leading_complement_initiative"; 1.91 1.92