博客开发日志 - day12

今天完善了博客文章访问统计，30分钟内重复访问同一篇文章不计入次数。

今天把访问量统计这一块的逻辑完善了，具体是新建一张表，存post+ip，同一ip 30分钟内，重复访问则不计数。具体的逻辑其实很简单，但是写sql有点麻烦。我都有点忘了为什么要用sqlc了，貌似是后端无法区分传入的是nil还是null

sql

复制代码

-- name: IncrementPostStatsView :exec
WITH visitor AS (
    INSERT INTO post_viewers (
        post_id,
        ip,
        last_viewed_at
    )
    VALUES (
        $1,
        $2,
        NOW()
    )
    ON CONFLICT (post_id, ip)
    DO UPDATE
    SET last_viewed_at = NOW()
    WHERE post_viewers.last_viewed_at < NOW() - INTERVAL '30 minutes'
    RETURNING post_id
)
INSERT INTO post_stats (
    post_id,
    view
)
SELECT
    post_id,
    1
FROM visitor
ON CONFLICT (post_id)
DO UPDATE
SET view = post_stats.view + 1;