1.1 --- a/core.sql	Sun Feb 21 16:53:05 2010 +0100
     1.2 +++ b/core.sql	Thu Mar 04 00:42:47 2010 +0100
     1.3 @@ -6,7 +6,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('beta22', NULL, NULL, NULL))
     1.8 +  SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -2329,73 +2329,97 @@
    1.13            UPDATE "suggestion" SET
    1.14              "minus2_unfulfilled_count" = (
    1.15                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.16 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.17 -              ON "opinion"."member_id" = "snapshot"."member_id"
    1.18 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.19 -              AND "snapshot"."issue_id" = "issue_id_p"
    1.20 +              FROM "issue" CROSS JOIN "opinion"
    1.21 +              JOIN "direct_interest_snapshot" AS "snapshot"
    1.22 +              ON "snapshot"."issue_id" = "issue"."id"
    1.23 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
    1.24 +              AND "snapshot"."member_id" = "opinion"."member_id"
    1.25 +              WHERE "issue"."id" = "issue_id_p"
    1.26 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
    1.27                AND "opinion"."degree" = -2
    1.28                AND "opinion"."fulfilled" = FALSE
    1.29              ),
    1.30              "minus2_fulfilled_count" = (
    1.31                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.32 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.33 -              ON "opinion"."member_id" = "snapshot"."member_id"
    1.34 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.35 -              AND "snapshot"."issue_id" = "issue_id_p"
    1.36 +              FROM "issue" CROSS JOIN "opinion"
    1.37 +              JOIN "direct_interest_snapshot" AS "snapshot"
    1.38 +              ON "snapshot"."issue_id" = "issue"."id"
    1.39 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
    1.40 +              AND "snapshot"."member_id" = "opinion"."member_id"
    1.41 +              WHERE "issue"."id" = "issue_id_p"
    1.42 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
    1.43                AND "opinion"."degree" = -2
    1.44                AND "opinion"."fulfilled" = TRUE
    1.45              ),
    1.46              "minus1_unfulfilled_count" = (
    1.47                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.48 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.49 -              ON "opinion"."member_id" = "snapshot"."member_id"
    1.50 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.51 -              AND "snapshot"."issue_id" = "issue_id_p"
    1.52 +              FROM "issue" CROSS JOIN "opinion"
    1.53 +              JOIN "direct_interest_snapshot" AS "snapshot"
    1.54 +              ON "snapshot"."issue_id" = "issue"."id"
    1.55 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
    1.56 +              AND "snapshot"."member_id" = "opinion"."member_id"
    1.57 +              WHERE "issue"."id" = "issue_id_p"
    1.58 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
    1.59                AND "opinion"."degree" = -1
    1.60                AND "opinion"."fulfilled" = FALSE
    1.61              ),
    1.62              "minus1_fulfilled_count" = (
    1.63                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.64 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.65 -              ON "opinion"."member_id" = "snapshot"."member_id"
    1.66 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.67 -              AND "snapshot"."issue_id" = "issue_id_p"
    1.68 +              FROM "issue" CROSS JOIN "opinion"
    1.69 +              JOIN "direct_interest_snapshot" AS "snapshot"
    1.70 +              ON "snapshot"."issue_id" = "issue"."id"
    1.71 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
    1.72 +              AND "snapshot"."member_id" = "opinion"."member_id"
    1.73 +              WHERE "issue"."id" = "issue_id_p"
    1.74 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
    1.75                AND "opinion"."degree" = -1
    1.76                AND "opinion"."fulfilled" = TRUE
    1.77              ),
    1.78              "plus1_unfulfilled_count" = (
    1.79                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.80 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.81 -              ON "opinion"."member_id" = "snapshot"."member_id"
    1.82 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.83 -              AND "snapshot"."issue_id" = "issue_id_p"
    1.84 +              FROM "issue" CROSS JOIN "opinion"
    1.85 +              JOIN "direct_interest_snapshot" AS "snapshot"
    1.86 +              ON "snapshot"."issue_id" = "issue"."id"
    1.87 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
    1.88 +              AND "snapshot"."member_id" = "opinion"."member_id"
    1.89 +              WHERE "issue"."id" = "issue_id_p"
    1.90 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
    1.91                AND "opinion"."degree" = 1
    1.92                AND "opinion"."fulfilled" = FALSE
    1.93              ),
    1.94              "plus1_fulfilled_count" = (
    1.95                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.96 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.97 -              ON "opinion"."member_id" = "snapshot"."member_id"
    1.98 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.99 -              AND "snapshot"."issue_id" = "issue_id_p"
   1.100 +              FROM "issue" CROSS JOIN "opinion"
   1.101 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.102 +              ON "snapshot"."issue_id" = "issue"."id"
   1.103 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.104 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.105 +              WHERE "issue"."id" = "issue_id_p"
   1.106 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.107                AND "opinion"."degree" = 1
   1.108                AND "opinion"."fulfilled" = TRUE
   1.109              ),
   1.110              "plus2_unfulfilled_count" = (
   1.111                SELECT coalesce(sum("snapshot"."weight"), 0)
   1.112 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
   1.113 -              ON "opinion"."member_id" = "snapshot"."member_id"
   1.114 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
   1.115 -              AND "snapshot"."issue_id" = "issue_id_p"
   1.116 +              FROM "issue" CROSS JOIN "opinion"
   1.117 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.118 +              ON "snapshot"."issue_id" = "issue"."id"
   1.119 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.120 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.121 +              WHERE "issue"."id" = "issue_id_p"
   1.122 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.123                AND "opinion"."degree" = 2
   1.124                AND "opinion"."fulfilled" = FALSE
   1.125              ),
   1.126              "plus2_fulfilled_count" = (
   1.127                SELECT coalesce(sum("snapshot"."weight"), 0)
   1.128 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
   1.129 -              ON "opinion"."member_id" = "snapshot"."member_id"
   1.130 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
   1.131 -              AND "snapshot"."issue_id" = "issue_id_p"
   1.132 +              FROM "issue" CROSS JOIN "opinion"
   1.133 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.134 +              ON "snapshot"."issue_id" = "issue"."id"
   1.135 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.136 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.137 +              WHERE "issue"."id" = "issue_id_p"
   1.138 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.139                AND "opinion"."degree" = 2
   1.140                AND "opinion"."fulfilled" = TRUE
   1.141              )