[240712 TIL] 자동 like count 기록 기능 만들기 최종본

기본 세팅

가장 먼저 supabase 기본 auth 를 보강해줄 users public table 이 필요합니다.

 

 

컬럼명	타입	용도	입력방법	외래키
id	uuid	auth 스키마 연결	자동입력(유니크)	O
created_at	timestampz	작성일	자동입력	X
email	varchar	회원식별	회원가입 시 자동입력	X
nickname	text	회원식별	회원가입 시 자동입력	X
avatar	text	마이페이지 렌더링	회원가입 시 자동입력	X
introduction	text	마이페이지 렌더링	마이페이지에서 입력	X

 

위와 같이 테이블을 생성하고, 아래 sql 을 적용합니다.

-- 새로운 트리거 함수 생성
CREATE OR REPLACE FUNCTION public.handle_new_user_custom()
RETURNS TRIGGER AS $$ BEGIN INSERT
INTO public.users (id, email, nickname)
VALUES (NEW.id, NEW.email, NEW.raw_user_meta_data->>'user_name');
RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER;
-- 새로운 트리거 생성
CREATE TRIGGER on_auth_user_created_custom
AFTER INSERT ON auth.users FOR EACH ROW EXECUTE FUNCTION public.handle_new_user_custom();

 

그리고 주요 콘텐츠가 저장될 posts 테이블은 아래와 같습니다.

 

컬럼명	타입	용도	입력방법	외래키
id	uuid	고유 아이디	자동입력(유니크)	X
created_at	timestamptz	작성일	자동입력	X
contents	text	글 내용	글 생성시	X
nickname	text	작성자	글 생성시	X
email	text	작성자 이메일	글 생성시	X
avatar	text	유저 프로필 사진	글 생성시	X

 

또한 아래와 같이 likes를 관리하는 테이블이 있어야 합니다.

 

컬럼명	타입	용도	입력방법	외래키
id	uuid	고유 아이디	자동입력(유니크)	X
created_at	timestamptz	작성일	자동입력	X
postid	uuid	posts의 id와 연결(카운트용)	자동입력	O
userid	uuid	users의 id와 연결(카운트용)	자동입력	O

 

자동 컬럼 값 변경 SQL

1. add likes column

먼저 posts 테이블에 likecount 컬럼을 추가합니다.

ALTER TABLE posts ADD COLUMN likecount INTEGER DEFAULT 0;

2. manual update column likes

그 후 필요하다면 수동으로 한번 likecount 값을 업데이트 해줍니다.

UPDATE posts SET likecount = (SELECT COUNT(*) FROM cheerup WHERE cheerup.postid = posts.id);

3. function

likes 테이블이 insert, delete 될 때마다 작동하는 함수를 만들어 줍니다.
이때 마지막에 SECURITY DEFINER 를 꼭 붙여줘야 합니다!

CREATE OR REPLACE FUNCTION update_likecount()
RETURNS TRIGGER AS $$
BEGIN

IF (TG_OP = 'INSERT') THEN
    UPDATE posts
    SET likecount = (
        SELECT COUNT(*)
        FROM cheerup
        WHERE cheerup.postid = NEW.postid
    )
    WHERE id = NEW.postid;

ELSIF (TG_OP = 'DELETE') THEN
    UPDATE posts
    SET likecount = (
        SELECT COUNT(*)
        FROM cheerup
        WHERE cheerup.postid = OLD.postid
    )
    WHERE id = OLD.postid;
    END IF;

RETURN NULL;
END;

$$ LANGUAGE plpgsql SECURITY DEFINER;

4. trigger

위에서 작성한 함수를 트리거로 등록해줍니다.

-- After INSERT or DELETE on cheerup
CREATE TRIGGER trigger_update_likecount
AFTER INSERT OR DELETE ON cheerup
FOR EACH ROW
EXECUTE FUNCTION update_likecount();

야호! 끝!! 이제 posts 테이블의 likecount 컬럼은 likes(여기서는 cheerup)에 insert, delete 가 있을 때마다 자동으로 값이 변경됩니다.

5. drop trigger

테스트등을 위해 트리거를 드랍할 수 있는 방법입니다.

DROP TRIGGER IF EXISTS trigger_update_likecount ON cheerup;

번외. likes view 만들기

직접 posts 테이블에 likecount 컬럼을 추가하지 않고 따로 view 를 만들려면 아래와 같이 할 수 있습니다.

CREATE VIEW cheerup_likes AS
SELECT
posts.id AS postid,
posts.contents,
COUNT(cheerup.id) AS likecount
FROM posts
LEFT JOIN cheerup ON posts.id = cheerup.postid
GROUP BY posts.id;