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  

Impressum / About Us