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",

Impressum / About Us