1 -- Need to add immutable to the controversy_rank function in order to index by it
4 -- if downvotes <= 0 or upvotes <= 0:
7 -- (upvotes + downvotes) * min(upvotes, downvotes) / max(upvotes, downvotes)
8 create or replace function controversy_rank(upvotes numeric, downvotes numeric)
11 if downvotes <= 0 or upvotes <= 0 then
14 return (upvotes + downvotes) *
15 case when upvotes > downvotes
16 then downvotes::float / upvotes::float
17 else upvotes::float / downvotes::float
25 alter table post_aggregates add column controversy_rank float not null default 0;
26 alter table comment_aggregates add column controversy_rank float not null default 0;
28 -- Populate them initially
29 -- Note: After initial population, these are updated with vote triggers
30 update post_aggregates set controversy_rank = controversy_rank(upvotes::numeric, downvotes::numeric);
31 update comment_aggregates set controversy_rank = controversy_rank(upvotes::numeric, downvotes::numeric);
33 -- Create single column indexes
34 create index idx_post_aggregates_featured_local_controversy on post_aggregates (featured_local desc, controversy_rank desc);
35 create index idx_post_aggregates_featured_community_controversy on post_aggregates (featured_community desc, controversy_rank desc);
36 create index idx_comment_aggregates_controversy on comment_aggregates (controversy_rank desc);
38 -- Update post_aggregates_score trigger function to include controversy_rank update
39 create or replace function post_aggregates_score()
40 returns trigger language plpgsql
43 IF (TG_OP = 'INSERT') THEN
44 update post_aggregates pa
45 set score = score + NEW.score,
46 upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
47 downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end,
48 controversy_rank = controversy_rank(pa.upvotes + case when NEW.score = 1 then 1 else 0 end::numeric,
49 pa.downvotes + case when NEW.score = -1 then 1 else 0 end::numeric)
50 where pa.post_id = NEW.post_id;
52 ELSIF (TG_OP = 'DELETE') THEN
53 -- Join to post because that post may not exist anymore
54 update post_aggregates pa
55 set score = score - OLD.score,
56 upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
57 downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end,
58 controversy_rank = controversy_rank(pa.upvotes + case when NEW.score = 1 then 1 else 0 end::numeric,
59 pa.downvotes + case when NEW.score = -1 then 1 else 0 end::numeric)
61 where pa.post_id = p.id
62 and pa.post_id = OLD.post_id;
68 -- Update comment_aggregates_score trigger function to include controversy_rank update
69 create or replace function comment_aggregates_score()
70 returns trigger language plpgsql
73 IF (TG_OP = 'INSERT') THEN
74 update comment_aggregates ca
75 set score = score + NEW.score,
76 upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
77 downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end,
78 controversy_rank = controversy_rank(ca.upvotes + case when NEW.score = 1 then 1 else 0 end::numeric,
79 ca.downvotes + case when NEW.score = -1 then 1 else 0 end::numeric)
80 where ca.comment_id = NEW.comment_id;
82 ELSIF (TG_OP = 'DELETE') THEN
83 -- Join to comment because that comment may not exist anymore
84 update comment_aggregates ca
85 set score = score - OLD.score,
86 upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
87 downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end,
88 controversy_rank = controversy_rank(ca.upvotes + case when NEW.score = 1 then 1 else 0 end::numeric,
89 ca.downvotes + case when NEW.score = -1 then 1 else 0 end::numeric)
91 where ca.comment_id = c.id
92 and ca.comment_id = OLD.comment_id;