65 lines
2.3 KiB
PL/PgSQL
65 lines
2.3 KiB
PL/PgSQL
-- =====================================================================
|
|
-- Migration 008: Adds comment_count Column with automatic Trigger
|
|
-- Mirrors Pattern from likes_count and dislikes_count.
|
|
-- =====================================================================
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 1: Adds comment_count Column to Contributions
|
|
-- ---------------------------------------------------------------------
|
|
ALTER TABLE contributions
|
|
ADD COLUMN comment_count INTEGER NOT NULL DEFAULT 0;
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 2: Backfills existing Comment Counts
|
|
-- ---------------------------------------------------------------------
|
|
UPDATE contributions c
|
|
SET comment_count = (
|
|
SELECT COUNT(*)
|
|
FROM comments cm
|
|
WHERE cm.contribution_id = c.contribution_id
|
|
AND cm.status = 'approved'
|
|
);
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 3: Trigger Function to update comment_count
|
|
-- Fires on Status Change on comments. Only counts approved Comments
|
|
-- ---------------------------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION update_comment_count()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
|
UPDATE contributions
|
|
SET comment_count = (
|
|
SELECT COUNT(*) FROM comments
|
|
WHERE contribution_id = NEW.contribution_id
|
|
AND status = 'approved'
|
|
)
|
|
WHERE contribution_id = NEW.contribution_id;
|
|
END IF;
|
|
|
|
IF TG_OP = 'DELETE' OR (TG_OP = 'UPDATE' AND OLD.contribution_id != NEW.contribution_id) THEN
|
|
UPDATE contributions
|
|
SET comment_count = (
|
|
SELECT COUNT(*) FROM comments
|
|
WHERE contribution_id = OLD.contribution_id
|
|
AND status = 'approved'
|
|
)
|
|
WHERE contribution_id = OLD.contribution_id;
|
|
END IF;
|
|
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Block 4: Attaches Trigger to comments Table
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TRIGGER trigger_update_comment_count
|
|
AFTER INSERT OR DELETE OR UPDATE OF status
|
|
ON comments
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_comment_count(); |