mirror of https://github.com/rocboss/paopao-ce
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
143 lines
4.0 KiB
143 lines
4.0 KiB
--------------------------------------------------------------------------------
|
|
-- comment sql dml
|
|
--------------------------------------------------------------------------------
|
|
|
|
-- name: GetNewestComments :many
|
|
SELECT *
|
|
FROM p_comment
|
|
WHERE post_id=$1 AND is_del=0
|
|
ORDER BY is_essence DESC, id DESC
|
|
LIMIT $2 OFFSET $3;
|
|
|
|
-- name: GetHotsComments :many
|
|
SELECT c.*
|
|
FROM p_comment c
|
|
LEFT JOIN p_comment_metric m
|
|
ON c.id=m.comment_id
|
|
WHERE c.post_id=$1 AND c.is_del=0 AND m.is_del=0
|
|
ORDER BY is_essence DESC, m.rank_score DESC, c.id DESC
|
|
LIMIT $2 OFFSET $3;
|
|
|
|
-- name: GetDefaultComments :many
|
|
SELECT *
|
|
FROM p_comment
|
|
WHERE post_id=$1 AND is_del=0
|
|
ORDER BY is_essence DESC, id ASC
|
|
LIMIT $2 OFFSET $3;
|
|
|
|
-- name: GetCommentById :one
|
|
SELECT * FROM p_comment WHERE id=$1 AND is_del=0;
|
|
|
|
-- name: GetCommentCount :one
|
|
SELECT count(*) FROM p_comment WHERE post_id=$1 AND is_del=0;
|
|
|
|
-- name: GetCommentReplyById :one
|
|
SELECT * FROM p_comment_reply WHERE id=$1 AND is_del=0;
|
|
|
|
-- name: GetCommentContentsByIds :many
|
|
SELECT * FROM p_comment_content WHERE comment_id = ANY(@ids::BIGINT[]);
|
|
|
|
-- name: GetCommentRepliesByIds :many
|
|
SELECT *
|
|
FROM p_comment_reply
|
|
WHERE comment_id = ANY(@ids::BIGINT[])
|
|
ORDER BY id ASC;
|
|
|
|
-- name: GetCommentThumbs :many
|
|
SELECT user_id,
|
|
tweet_id,
|
|
comment_id,
|
|
reply_id,
|
|
comment_type,
|
|
is_thumbs_up,
|
|
is_thumbs_down
|
|
FROM p_tweet_comment_thumbs
|
|
WHERE user_id=$1 AND tweet_id=$2;
|
|
|
|
--------------------------------------------------------------------------------
|
|
-- comment_manage sql dml
|
|
--------------------------------------------------------------------------------
|
|
|
|
-- name: DeleteComment :exec
|
|
UPDATE p_comment SET deleted_on=$1, is_del=1 WHERE id=$2 AND is_del=0;
|
|
|
|
-- name: DeleteCommentThumbs :exec
|
|
UPDATE p_tweet_comment_thumbs
|
|
SET deleted_on=$1, is_del=1
|
|
WHERE user_id=$2 AND tweet_id=$3 AND comment_id=$4 AND is_del=0;
|
|
|
|
-- name: CreateComment :one
|
|
INSERT INTO p_comment (post_id, user_id, ip, ip_loc, created_on)
|
|
VALUES ($1, $2, $3, $4, $5)
|
|
RETURNING id;
|
|
|
|
-- name: CreateCommentReply :one
|
|
INSERT INTO p_comment_reply (comment_id, user_id, content, at_user_id, ip, ip_loc, created_on)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7)
|
|
RETURNING id;
|
|
|
|
-- name: HighlightComment :one
|
|
UPDATE p_comment
|
|
SET is_essence=1-is_essence,
|
|
modified_on=$1
|
|
WHERE id=$2 AND user_id=$3 AND is_del=0
|
|
RETURNING is_essence;
|
|
|
|
-- name: IncrCommentReplyCount :exec
|
|
UPDATE p_comment
|
|
SET reply_count=reply_count+1,
|
|
modified_on=$1
|
|
WHERE id=$2 AND is_del=0;
|
|
|
|
-- name: DecrCommentReplyCount :exec
|
|
UPDATE p_comment
|
|
SET reply_count=reply_count-1,
|
|
modified_on=$1
|
|
WHERE id=$2 AND is_del=0;
|
|
|
|
-- name: DeleteCommentReply :exec
|
|
UPDATE p_comment_reply SET deleted_on=$1, is_del=1 WHERE id=$2 AND is_del=0;
|
|
|
|
-- name: DeleteReplyThumbs :exec
|
|
UPDATE p_tweet_comment_thumbs
|
|
SET deleted_on=$1, is_del=1
|
|
WHERE user_id=$2 AND comment_id=$3 AND reply_id=$4 AND is_del=0;
|
|
|
|
-- name: CreateCommentContent :one
|
|
INSERT INTO p_comment_content (comment_id, user_id, content, type, sort, created_on)
|
|
VALUES ($1, $2, $3, $4, $5, $6)
|
|
RETURNING id;
|
|
|
|
-- name: UpdateThumbsUpDownComment :exec
|
|
UPDATE p_tweet_comment_thumbs
|
|
SET is_thumbs_up=$1,
|
|
is_thumbs_down=$2,
|
|
modified_on=$3
|
|
WHERE id=$4 AND is_del=0;
|
|
|
|
-- name: CreateThumbsUpDownComment :one
|
|
INSERT INTO p_tweet_comment_thumbs (user_id, tweet_id, comment_id,
|
|
reply_id, is_thumbs_up, is_thumbs_down, comment_type, created_on)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
|
|
RETURNING id;
|
|
|
|
-- name: UpdateCommentThumbsCount :exec
|
|
UPDATE p_comment
|
|
SET thumbs_up_count=$1, Thumbs_down_count=$2, modified_on=$3
|
|
WHERE id=$4 AND is_del=0;
|
|
|
|
-- name: GetTweetCommentThumb :one
|
|
SELECT *
|
|
FROM p_tweet_comment_thumbs
|
|
WHERE user_id=$1 AND tweet_id=$2 AND comment_id=$3 AND comment_type=0 AND is_del=0;
|
|
|
|
-- name: GetCommentReplyThumb :one
|
|
SELECT *
|
|
FROM p_tweet_comment_thumbs
|
|
WHERE user_id=$1 AND tweet_id=$2 AND comment_id=$3 AND reply_id=$4 AND comment_type=1 AND is_del=0;
|
|
|
|
-- name: UpdateReplyThumbsCount :exec
|
|
UPDATE p_comment_reply
|
|
SET thumbs_up_count=$1, thumbs_down_count=$2, modified_on=$3
|
|
WHERE id=$4 AND is_del=0;
|