[supabase] supabase function

이미지 가장 많은 태그

이미지 정보 테이블 내에 문자열 리스트 태그들의 정보를 가져와서

가장 많은 태그 중 탑 10까지만을 조회하는 함수

인기 이미지 정보

현재는 좋아요만을 기준으로 인기 이미지 정보를 출력하고 있으나
추후 다운로드 수, 공유 수 등의 기준을 추가하여 출력할 예정

DROP FUNCTION IF EXISTS get_popular_image_count();

CREATE OR REPLACE FUNCTION get_popular_image_count() 
RETURNS TABLE (image_id bigint, preview_url TEXT, cnt bigint) 
AS $$
BEGIN
    RETURN QUERY 
    SELECT
        tii.image_id,
        tii.preview_url,
        tlh.cnt
    FROM
        tb_image_info tii
    JOIN (
        SELECT 
            like_image_id, 
            count(*) AS cnt
        FROM 
            tb_like_history
        GROUP BY 
            like_image_id
    ) tlh ON tii.image_id = tlh.like_image_id
    ORDER BY
        tlh.cnt DESC
    LIMIT 10;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_popular_image_count();

24.03.01 수정

CREATE OR REPLACE FUNCTION get_new_popular_image_count() 
RETURNS TABLE (image_id bigint, preview_url TEXT, cnt bigint) 
AS $$
BEGIN
    RETURN QUERY 
    SELECT
        tii.image_id,
        tii.preview_url,
        COALESCE(tlh.like_cnt, 0) + COALESCE(tuc.comment_cnt, 0) + COALESCE(tdh.download_cnt, 0) + COALESCE(tsh.share_cnt, 0) as cnt
    FROM
        tb_image_info tii
    LEFT JOIN
        (SELECT like_image_id, count(*) as like_cnt FROM tb_like_history GROUP BY like_image_id) tlh ON tii.image_id = tlh.like_image_id
    LEFT JOIN
        (SELECT comment_image_id, count(*) as comment_cnt FROM tb_user_comment GROUP BY comment_image_id) tuc ON tii.image_id = tuc.comment_image_id
    LEFT JOIN
        (SELECT download_image_id, count(*) as download_cnt FROM tb_download_history GROUP BY download_image_id) tdh ON tii.image_id = tdh.download_image_id
    LEFT JOIN
        (SELECT share_image_id, count(*) as share_cnt FROM tb_share_history GROUP BY share_image_id) tsh ON tii.image_id = tsh.share_image_id
    ORDER BY
        cnt DESC
    LIMIT 10;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_new_popular_image_count();