286 lines
8.8 KiB
SQL
286 lines
8.8 KiB
SQL
-- name: CreatePost :one
|
||
INSERT INTO posts(title, cover_id, slug, content, summary, status, sort, published_at)
|
||
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
|
||
RETURNING id;
|
||
|
||
-- name: ListPosts :many
|
||
WITH paginated_posts AS (
|
||
-- 第一步：先精准查出当前页需要的文章 ID
|
||
SELECT id,
|
||
title,
|
||
cover_id,
|
||
slug,
|
||
summary,
|
||
status,
|
||
sort,
|
||
published_at,
|
||
created_at,
|
||
updated_at
|
||
FROM posts
|
||
ORDER BY sort DESC, published_at DESC, id DESC
|
||
LIMIT $1 OFFSET $2)
|
||
-- 第二步：用这极少量的记录去进行 JOIN
|
||
SELECT p.*,
|
||
f.file_url AS cover,
|
||
f.file_size AS cover_size,
|
||
c.name AS category_name,
|
||
c.id AS category_id,
|
||
COALESCE(ps.view_count, 0) AS view_count,
|
||
COALESCE(
|
||
jsonb_agg(
|
||
jsonb_build_object(
|
||
'id', t.id,
|
||
'name', t.name,
|
||
'code', t.code
|
||
)
|
||
ORDER BY t.sort DESC, t.id
|
||
) FILTER (WHERE t.id IS NOT NULL),
|
||
'[]'::jsonb
|
||
) AS tags
|
||
FROM paginated_posts p
|
||
LEFT JOIN files f ON f.id = p.cover_id
|
||
LEFT JOIN post_category pc ON pc.post_id = p.id
|
||
LEFT JOIN categories c ON c.id = pc.category_id
|
||
LEFT JOIN post_stats ps ON ps.post_id = p.id
|
||
LEFT JOIN post_tag pt ON pt.post_id = p.id
|
||
LEFT JOIN tags t ON t.id = pt.tag_id
|
||
GROUP BY p.id,
|
||
p.title,
|
||
p.cover_id,
|
||
p.slug,
|
||
p.summary,
|
||
p.status,
|
||
p.sort,
|
||
p.published_at,
|
||
p.created_at,
|
||
p.updated_at,
|
||
f.file_url,
|
||
f.file_size,
|
||
c.name,
|
||
c.id,
|
||
ps.view_count
|
||
ORDER BY p.sort DESC, p.published_at DESC, p.id DESC;
|
||
|
||
-- name: CountPosts :one
|
||
SELECT COUNT(*)
|
||
FROM posts;
|
||
|
||
-- name: GetPostByID :one
|
||
SELECT p.*,
|
||
f.file_url AS cover,
|
||
f.file_size AS cover_size,
|
||
c.name AS category_name,
|
||
c.id AS category_id,
|
||
COALESCE(
|
||
array_agg(pt.tag_id ORDER BY pt.tag_id) FILTER (WHERE pt.tag_id IS NOT NULL),
|
||
ARRAY []::integer[]
|
||
) AS tags
|
||
FROM posts p
|
||
LEFT JOIN files f ON f.id = p.cover_id
|
||
LEFT JOIN post_category pc ON pc.post_id = p.id
|
||
LEFT JOIN categories c ON c.id = pc.category_id
|
||
LEFT JOIN post_tag pt ON pt.post_id = p.id
|
||
WHERE p.id = $1
|
||
GROUP BY p.id,
|
||
f.file_url,
|
||
f.file_size,
|
||
c.name,
|
||
c.id
|
||
LIMIT 1;
|
||
|
||
-- name: UpdatePost :execrows
|
||
UPDATE posts
|
||
SET title = coalesce(sqlc.narg('title'), title),
|
||
cover_id = CASE WHEN @update_cover_id::boolean THEN @cover_id ELSE cover_id END,
|
||
slug = coalesce(sqlc.narg('slug'), slug),
|
||
content = coalesce(sqlc.narg('content'), content),
|
||
summary = coalesce(sqlc.narg('summary'), summary),
|
||
status = coalesce(sqlc.narg('status'), status),
|
||
sort = coalesce(sqlc.narg('sort'), sort),
|
||
published_at = coalesce(sqlc.narg('published_at'), published_at)
|
||
WHERE id = sqlc.arg('id');
|
||
|
||
-- name: DeletePost :execrows
|
||
DELETE
|
||
FROM posts
|
||
WHERE id = $1;
|
||
|
||
-- web -------------------------------------------------------
|
||
|
||
-- name: GetPublicPostBySlug :one
|
||
SELECT p.id,
|
||
p.title,
|
||
p.slug,
|
||
p.content,
|
||
p.summary,
|
||
p.published_at,
|
||
f.file_url AS cover,
|
||
COALESCE(ps.view_count, 0) AS view_count,
|
||
c.code AS category_code,
|
||
c.name AS category_name,
|
||
COALESCE(t.tags, '[]') AS tags
|
||
FROM posts p
|
||
LEFT JOIN files f ON f.id = p.cover_id
|
||
LEFT JOIN post_stats ps ON ps.post_id = p.id
|
||
LEFT JOIN post_category pc ON pc.post_id = p.id
|
||
LEFT JOIN categories c ON pc.category_id = c.id
|
||
LEFT JOIN LATERAL (
|
||
SELECT jsonb_agg(
|
||
jsonb_build_object('name', tag.name, 'code', tag.code)
|
||
) AS tags
|
||
FROM post_tag pt
|
||
JOIN tags tag ON tag.id = pt.tag_id
|
||
WHERE pt.post_id = p.id
|
||
) t ON true
|
||
WHERE p.slug = $1
|
||
AND p.status = 1
|
||
AND p.published_at < NOW()
|
||
LIMIT 1;
|
||
|
||
-- name: ListPublishedPosts :many
|
||
WITH paginated_posts AS (
|
||
-- 第一步：先精准查出当前页需要的文章 ID
|
||
SELECT id,
|
||
title,
|
||
cover_id,
|
||
slug,
|
||
summary,
|
||
sort,
|
||
published_at
|
||
FROM posts
|
||
WHERE status = 1
|
||
AND published_at < NOW()
|
||
ORDER BY sort DESC, published_at DESC, id DESC
|
||
LIMIT $1 OFFSET $2)
|
||
SELECT p.*,
|
||
f.file_url AS cover,
|
||
c.name AS category_name,
|
||
c.id AS category_id,
|
||
c.code AS category_code,
|
||
COALESCE(ps.view_count, 0) AS view_count,
|
||
COALESCE(
|
||
jsonb_agg(
|
||
jsonb_build_object(
|
||
'id', t.id,
|
||
'name', t.name,
|
||
'code', t.code
|
||
)
|
||
ORDER BY t.sort DESC, t.id
|
||
) FILTER (WHERE t.id IS NOT NULL),
|
||
'[]'::jsonb
|
||
) AS tags
|
||
FROM paginated_posts p
|
||
LEFT JOIN files f ON f.id = p.cover_id
|
||
LEFT JOIN post_category pc ON pc.post_id = p.id
|
||
LEFT JOIN categories c ON c.id = pc.category_id
|
||
LEFT JOIN post_stats ps ON ps.post_id = p.id
|
||
LEFT JOIN post_tag pt ON pt.post_id = p.id
|
||
LEFT JOIN tags t ON t.id = pt.tag_id
|
||
GROUP BY p.id,
|
||
p.title,
|
||
p.cover_id,
|
||
p.slug,
|
||
p.summary,
|
||
p.sort,
|
||
p.published_at,
|
||
f.file_url,
|
||
c.name,
|
||
c.id,
|
||
c.code,
|
||
ps.view_count
|
||
ORDER BY p.sort DESC, p.published_at DESC, p.id DESC;
|
||
|
||
-- name: CountPublishedPosts :one
|
||
SELECT COUNT(*)
|
||
FROM posts
|
||
WHERE status = 1
|
||
AND published_at < NOW();
|
||
|
||
-- name: ListArchives :many
|
||
SELECT p.id,
|
||
p.slug,
|
||
p.title,
|
||
p.published_at,
|
||
c."name" AS category_name
|
||
FROM posts p
|
||
LEFT JOIN post_category pc ON p.id = pc.post_id
|
||
LEFT JOIN categories c ON c.id = pc.category_id
|
||
WHERE p.status = 1
|
||
AND p.published_at < NOW()
|
||
ORDER BY p.published_at DESC, p.id DESC;
|
||
|
||
-- name: ListCategoryStats :many
|
||
SELECT c.id,
|
||
c.name,
|
||
c.code,
|
||
COUNT(p.id) AS post_count
|
||
FROM categories c
|
||
LEFT JOIN post_category pc ON c.id = pc.category_id
|
||
LEFT JOIN posts p ON p.id = pc.post_id
|
||
AND p.status = 1
|
||
AND p.published_at < NOW()
|
||
GROUP BY c.id,
|
||
c.sort,
|
||
c.name,
|
||
c.code
|
||
ORDER BY c.sort DESC, c.id;
|
||
|
||
-- name: ListPublishedPostsWithFilters :many
|
||
SELECT p.id,
|
||
p.title,
|
||
p.slug,
|
||
p.summary,
|
||
p.sort,
|
||
p.published_at,
|
||
f.file_url AS cover,
|
||
c."name" AS category_name,
|
||
c.code AS category_code,
|
||
COALESCE(t.tags, '[]') AS tags,
|
||
COALESCE(ps."view_count", 0) AS view_count
|
||
FROM posts p
|
||
LEFT JOIN post_category pc ON pc.post_id = p.id
|
||
LEFT JOIN categories c ON c.id = pc.category_id
|
||
LEFT JOIN post_stats ps ON ps.post_id = p.id
|
||
LEFT JOIN files f ON f.id = p.cover_id
|
||
LEFT JOIN LATERAL (
|
||
SELECT jsonb_agg(
|
||
jsonb_build_object('name', tag.name, 'code', tag.code)
|
||
) AS tags
|
||
FROM post_tag pt
|
||
JOIN tags tag ON tag.id = pt.tag_id
|
||
WHERE pt.post_id = p.id
|
||
) t ON true
|
||
WHERE
|
||
-- 只显示已发布的文章
|
||
p.status = 1
|
||
AND p.published_at < NOW()
|
||
AND
|
||
-- category code 过滤（NULL 或空字符串时不过滤）
|
||
(
|
||
COALESCE(sqlc.narg('category_code')::text, '') = ''
|
||
OR EXISTS (SELECT 1
|
||
FROM post_category pc2
|
||
JOIN categories c2 ON c2.id = pc2.category_id
|
||
WHERE pc2.post_id = p.id
|
||
AND c2.code = sqlc.narg('category_code')::text)
|
||
)
|
||
AND
|
||
-- tag code 过滤（NULL 或空字符串时不过滤）
|
||
(
|
||
COALESCE(sqlc.narg('tag_code')::text, '') = ''
|
||
OR EXISTS (SELECT 1
|
||
FROM post_tag pt2
|
||
JOIN tags t2 ON t2.id = pt2.tag_id
|
||
WHERE pt2.post_id = p.id
|
||
AND t2.code = sqlc.narg('tag_code')::text)
|
||
)
|
||
ORDER BY p.sort DESC,
|
||
p.published_at DESC,
|
||
p.id DESC;
|
||
|
||
-- name: GetPostsForSitemap :many
|
||
SELECT slug, created_at, updated_at
|
||
FROM posts
|
||
WHERE status = 1
|
||
AND published_at < NOW()
|
||
ORDER BY sort DESC, published_at DESC, id DESC; |