[240723 TIL] SQL 팔로잉 수 측정

팔로잉을 측정하는 방법을 추가하기 위해 buddy_following_counts와 buddy_follower_counts 두 컬럼을 각각 업데이트하는 로직을 작성해야 합니다. 여기서는 buddy_following_counts는 사용자가 팔로우하는 사람의 수, buddy_follower_counts는 사용자를 팔로우하는 사람의 수를 의미합니다.

트리거 함수에서 팔로우 및 팔로잉 수를 모두 업데이트하도록 코드를 수정하겠습니다.

테이블 및 트리거 설정

먼저 buddies 테이블에 buddy_following_counts와 buddy_follower_counts 컬럼을 추가합니다.

ALTER TABLE buddies
ADD COLUMN buddy_following_counts INT DEFAULT 0,
ADD COLUMN buddy_follower_counts INT DEFAULT 0;

트리거 및 함수 생성

트리거와 함수를 다음과 같이 수정합니다:

-- 트리거 생성
CREATE TRIGGER trigger_update_buddies_follow_counts
AFTER INSERT OR DELETE ON follow
FOR EACH ROW
EXECUTE FUNCTION update_buddies_follow_counts();

-- 함수 생성
CREATE OR REPLACE FUNCTION update_buddies_follow_counts()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        -- 팔로워 수 업데이트
        UPDATE buddies
        SET buddy_follower_counts = (
            SELECT COUNT(*)
            FROM follow
            WHERE follow.follow_buddy_id = NEW.follow_buddy_id
        )
        WHERE buddy_id = NEW.follow_buddy_id;

        -- 팔로잉 수 업데이트
        UPDATE buddies
        SET buddy_following_counts = (
            SELECT COUNT(*)
            FROM follow
            WHERE follow.follow_follower_id = NEW.follow_follower_id
        )
        WHERE buddy_id = NEW.follow_follower_id;
    ELSIF (TG_OP = 'DELETE') THEN
        -- 팔로워 수 업데이트
        UPDATE buddies
        SET buddy_follower_counts = (
            SELECT COUNT(*)
            FROM follow
            WHERE follow.follow_buddy_id = OLD.follow_buddy_id
        )
        WHERE buddy_id = OLD.follow_buddy_id;

        -- 팔로잉 수 업데이트
        UPDATE buddies
        SET buddy_following_counts = (
            SELECT COUNT(*)
            FROM follow
            WHERE follow.follow_follower_id = OLD.follow_follower_id
        )
        WHERE buddy_id = OLD.follow_follower_id;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

주요 변경 사항

1. 팔로워 수 업데이트: follow_buddy_id를 기준으로 buddy_follower_counts를 업데이트합니다.
2. 팔로잉 수 업데이트: follow_follower_id를 기준으로 buddy_following_counts를 업데이트합니다.
3. INSERT 및 DELETE 처리: 삽입(INSERT) 및 삭제(DELETE) 작업에 대해 각각 팔로워 수와 팔로잉 수를 업데이트합니다.

이제, follow 테이블에 데이터가 삽입되거나 삭제될 때, buddies 테이블의 buddy_following_counts와 buddy_follower_counts가 자동으로 업데이트됩니다. 이 설정을 통해 팔로우 및 팔로잉 관계를 효율적으로 관리할 수 있습니다.