1 -- Need to add immutable to the controversy_rank function in order to index by it
3 -- if downvotes <= 0 or upvotes <= 0:
6 -- (upvotes + downvotes) * min(upvotes, downvotes) / max(upvotes, downvotes)
7 CREATE OR REPLACE FUNCTION controversy_rank (upvotes numeric, downvotes numeric)
11 IF downvotes <= 0 OR upvotes <= 0 THEN
14 RETURN (upvotes + downvotes) * CASE WHEN upvotes > downvotes THEN
15 downvotes::float / upvotes::float
17 upvotes::float / downvotes::float
26 ALTER TABLE post_aggregates
27 ADD COLUMN controversy_rank float NOT NULL DEFAULT 0;
29 ALTER TABLE comment_aggregates
30 ADD COLUMN controversy_rank float NOT NULL DEFAULT 0;
32 -- Populate them initially
33 -- Note: After initial population, these are updated with vote triggers
37 controversy_rank = controversy_rank (upvotes::numeric, downvotes::numeric);
42 controversy_rank = controversy_rank (upvotes::numeric, downvotes::numeric);
44 -- Create single column indexes
45 CREATE INDEX idx_post_aggregates_featured_local_controversy ON post_aggregates (featured_local DESC, controversy_rank DESC);
47 CREATE INDEX idx_post_aggregates_featured_community_controversy ON post_aggregates (featured_community DESC, controversy_rank DESC);
49 CREATE INDEX idx_comment_aggregates_controversy ON comment_aggregates (controversy_rank DESC);
51 -- Update post_aggregates_score trigger function to include controversy_rank update
52 CREATE OR REPLACE FUNCTION post_aggregates_score ()
57 IF (TG_OP = 'INSERT') THEN
61 score = score + NEW.score,
62 upvotes = CASE WHEN NEW.score = 1 THEN
67 downvotes = CASE WHEN NEW.score = - 1 THEN
72 controversy_rank = controversy_rank (pa.upvotes + CASE WHEN NEW.score = 1 THEN
76 END::numeric, pa.downvotes + CASE WHEN NEW.score = - 1 THEN
82 pa.post_id = NEW.post_id;
83 ELSIF (TG_OP = 'DELETE') THEN
84 -- Join to post because that post may not exist anymore
88 score = score - OLD.score,
89 upvotes = CASE WHEN OLD.score = 1 THEN
94 downvotes = CASE WHEN OLD.score = - 1 THEN
99 controversy_rank = controversy_rank (pa.upvotes + CASE WHEN NEW.score = 1 THEN
103 END::numeric, pa.downvotes + CASE WHEN NEW.score = - 1 THEN
112 AND pa.post_id = OLD.post_id;
118 -- Update comment_aggregates_score trigger function to include controversy_rank update
119 CREATE OR REPLACE FUNCTION comment_aggregates_score ()
124 IF (TG_OP = 'INSERT') THEN
126 comment_aggregates ca
128 score = score + NEW.score,
129 upvotes = CASE WHEN NEW.score = 1 THEN
134 downvotes = CASE WHEN NEW.score = - 1 THEN
139 controversy_rank = controversy_rank (ca.upvotes + CASE WHEN NEW.score = 1 THEN
143 END::numeric, ca.downvotes + CASE WHEN NEW.score = - 1 THEN
149 ca.comment_id = NEW.comment_id;
150 ELSIF (TG_OP = 'DELETE') THEN
151 -- Join to comment because that comment may not exist anymore
153 comment_aggregates ca
155 score = score - OLD.score,
156 upvotes = CASE WHEN OLD.score = 1 THEN
161 downvotes = CASE WHEN OLD.score = - 1 THEN
166 controversy_rank = controversy_rank (ca.upvotes + CASE WHEN NEW.score = 1 THEN
170 END::numeric, ca.downvotes + CASE WHEN NEW.score = - 1 THEN
179 AND ca.comment_id = OLD.comment_id;