liquid_feedback_core
changeset 474:5cafa61745bd
New view "initiative_for_notification" using helper views/functions "updated_initiative", "featured_initiative", and "leading_complement_initiative"
author | jbe |
---|---|
date | Wed Mar 30 02:15:30 2016 +0200 (2016-03-30) |
parents | 234c9760589d |
children | ecac8ab6ca04 |
files | core.sql |
line diff
1.1 --- a/core.sql Tue Mar 29 21:46:14 2016 +0200 1.2 +++ b/core.sql Wed Mar 30 02:15:30 2016 +0200 1.3 @@ -120,6 +120,8 @@ 1.4 "notify_email_secret_expiry" TIMESTAMPTZ, 1.5 "notify_email_lock_expiry" TIMESTAMPTZ, 1.6 "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE, 1.7 + "notification_counter" INT4 NOT NULL DEFAULT 0, 1.8 + "sample_size" INT4 NOT NULL DEFAULT 3, 1.9 "last_notified_suggestion_id" INT8, 1.10 "login_recovery_expiry" TIMESTAMPTZ, 1.11 "password_reset_secret" TEXT UNIQUE, 1.12 @@ -2390,7 +2392,7 @@ 1.13 JOIN "supporter" ON 1.14 "supporter"."member_id" = "member"."id" AND 1.15 "supporter"."initiative_id" = "initiative"."id" 1.16 - WHERE "issue"."half_frozen" ISNULL AND "issue"."closed" ISNULL 1.17 + WHERE "issue"."state" IN ('admission', 'discussion') ISNULL 1.18 AND ( 1.19 EXISTS ( 1.20 SELECT NULL FROM "draft" 1.21 @@ -2406,6 +2408,93 @@ 1.22 ) 1.23 ); 1.24 1.25 +CREATE FUNCTION "featured_initiative" 1.26 + ( "member_id_p" "member"."id"%TYPE, 1.27 + "area_id_p" "area"."id"%TYPE ) 1.28 + RETURNS SETOF "initiative" 1.29 + LANGUAGE 'plpgsql' STABLE AS $$ 1.30 + DECLARE 1.31 + "sample_size_v" INT4; 1.32 + "member_id_v" "member"."id"%TYPE; 1.33 + "seed_v" TEXT; 1.34 + "result_row" "initiative"%ROWTYPE; 1.35 + "match_v" BOOLEAN; 1.36 + "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[] 1.37 + BEGIN 1.38 + SELECT INTO "sample_size_v" "sample_size" FROM "member" WHERE "id" = "member_id_p"; 1.39 + "initiative_id_ary" := '{}'; 1.40 + LOOP 1.41 + "match_v" := FALSE; 1.42 + FOR "member_id_v", "seed_v" IN 1.43 + SELECT * FROM ( 1.44 + SELECT DISTINCT 1.45 + "supporter"."member_id", 1.46 + md5("member_id" || '-' || "member"."notification_counter" || '-' || "area_id_p") AS "seed" 1.47 + FROM "supporter" 1.48 + JOIN "member" ON "member"."id" = "supporter"."member_id" 1.49 + JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id" 1.50 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.51 + WHERE "supporter"."member_id" != "member_id_p" 1.52 + AND "issue"."area_id" = "area_id_p" 1.53 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 1.54 + ) AS "subquery" 1.55 + ORDER BY "seed" 1.56 + LOOP 1.57 + FOR "result_row" IN 1.58 + SELECT "initiative".* FROM "initiative" 1.59 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.60 + JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id" 1.61 + LEFT JOIN "supporter" AS "self_support" ON 1.62 + "self_support"."initiative_id" = "initiative"."id" AND 1.63 + "self_support"."member_id" = "member_id_p" 1.64 + WHERE "supporter"."member_id" = "member_id_v" 1.65 + AND "issue"."area_id" = "area_id_p" 1.66 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 1.67 + AND "self_support"."member_id" ISNULL 1.68 + ORDER BY md5("seed_v" || '-' || "initiative"."id") 1.69 + LOOP 1.70 + IF NOT "initiative_id_ary" @> ARRAY["result_row"."id"] THEN 1.71 + "match_v" := TRUE; 1.72 + "initiative_id_ary" := "initiative_id_ary" || "result_row"."id"; 1.73 + RETURN NEXT "result_row"; 1.74 + IF array_length("initiative_id_ary", 1) >= "sample_size_v" THEN 1.75 + RETURN; 1.76 + END IF; 1.77 + END IF; 1.78 + END LOOP; 1.79 + END LOOP; 1.80 + EXIT WHEN NOT "match_v"; 1.81 + END LOOP; 1.82 + RETURN; 1.83 + END; 1.84 + $$; 1.85 + 1.86 +CREATE VIEW "updated_or_featured_initiative" AS 1.87 + SELECT * FROM "updated_initiative" 1.88 + UNION ALL 1.89 + SELECT 1.90 + "member"."id" AS "seen_by_member_id", 1.91 + "featured_initiative".* 1.92 + FROM "member" CROSS JOIN "area" 1.93 + CROSS JOIN LATERAL "featured_initiative"("member"."id", "area"."id"); 1.94 + 1.95 +CREATE VIEW "leading_complement_initiative" AS 1.96 + SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id") 1.97 + "updated_or_featured_initiative"."seen_by_member_id", 1.98 + "initiative".* 1.99 + FROM "updated_or_featured_initiative" 1.100 + JOIN "initiative" 1.101 + ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id" 1.102 + ORDER BY 1.103 + "seen_by_member_id", 1.104 + "initiative"."issue_id", 1.105 + "initiative"."harmonic_weight" DESC; 1.106 + 1.107 +CREATE VIEW "initiative_for_notification" AS 1.108 + SELECT * FROM "updated_or_featured_initiative" 1.109 + UNION 1.110 + SELECT * FROM "leading_complement_initiative"; 1.111 + 1.112 1.113 1.114 ------------------------------------------------------