msb-public
/
paopao-ce
mirror of https://github.com/rocboss/paopao-ce
1
0
Code Issues Projects Releases Wiki Activity
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.
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'r/paopao-ce-xtra'
${ noResults }
paopao-ce/internal/dao/slonik/sqlc/postgres/query_pgc/tweets.sql

721 lines
18 KiB
Raw Permalink Blame History

--------------------------------------------------------------------------------
-- tweet sql dml
--------------------------------------------------------------------------------
-- name: GetPostById :one
SELECT * FROM p_post WHERE id=$1 AND is_del=0;
-- name: GetUserPosts :many
SELECT * FROM p_post
WHERE user_id=$1 AND visibility IN ($1) AND is_del=0
ORDER BY latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: GetAnyPosts :many
SELECT * FROM p_post WHERE visibility IN ($1) AND is_del=0 LIMIT $2 OFFSET $3;
-- name: CountUserPosts :one
SELECT count(*) FROM p_post WHERE user_id=$1 AND visibility IN ($2) AND is_del=0;
-- name: CountAnyPost :one
SELECT count(*) FROM p_post WHERE visibility IN ($1) AND is_del=0;
-- name: GetUserPostStar :one
SELECT
s.*,
P.ID "post.id",
P.user_id "post.user_id",
P.comment_count "post.comment_count",
P.collection_count "post.collection_count",
P.upvote_count "post.upvote_count",
P.share_count "post.share_count",
P.visibility "post.visibility",
P.is_top "post.is_top",
P.is_essence "post.is_essence",
P.is_lock "post.is_lock",
P.latest_replied_on "post.latest_replied_on",
P.tags "post.tags",
P.attachment_price "post.attachment_price",
P.ip "post.ip",
P.ip_loc "post.ip_loc",
P.is_del "post.is_del",
P.created_on "post.created_on",
P.modified_on "post.modified_on",
P.deleted_on "post.deleted_on"
FROM
p_post_star s
JOIN p_post P ON s.post_id = P.ID
WHERE
s.post_id = $1
AND s.user_id = $2
AND s.is_del = 0
AND (visibility >= 50 OR (visibility = 0 AND P.user_id = $3 ));
-- name: GetUserPostStars :many
SELECT
s.*,
P.ID "post.id",
P.user_id "post.user_id",
P.comment_count "post.comment_count",
P.collection_count "post.collection_count",
P.upvote_count "post.upvote_count",
P.share_count "post.share_count",
P.visibility "post.visibility",
P.is_top "post.is_top",
P.is_essence "post.is_essence",
P.is_lock "post.is_lock",
P.latest_replied_on "post.latest_replied_on",
P.tags "post.tags",
P.attachment_price "post.attachment_price",
P.ip "post.ip",
P.ip_loc "post.ip_loc",
P.is_del "post.is_del",
P.created_on "post.created_on",
P.modified_on "post.modified_on",
P.deleted_on "post.deleted_on"
FROM
p_post_star s
JOIN p_post P ON s.post_id = P.ID
WHERE
s.user_id = $1
AND s.is_del = 0
AND (visibility >= 50 OR (visibility = 0 AND P.user_id = $2))
ORDER BY
s.ID DESC,
P.ID DESC
LIMIT $3 OFFSET $4;
-- name: CountUserPostStars :one
SELECT
count(*)
FROM
p_post_star s
JOIN p_post P ON s.post_id = P.ID
WHERE
s.user_id = $1
AND s.is_del = 0
AND (visibility >= 50 OR (visibility = 0 AND P.user_id = $2));
-- name: GetUserPostCollection :one
SELECT
s.*,
P.ID "post.id",
P.user_id "post.user_id",
P.comment_count "post.comment_count",
P.collection_count "post.collection_count",
P.upvote_count "post.upvote_count",
P.share_count "post.share_count",
P.visibility "post.visibility",
P.is_top "post.is_top",
P.is_essence "post.is_essence",
P.is_lock "post.is_lock",
P.latest_replied_on "post.latest_replied_on",
P.tags "post.tags",
P.attachment_price "post.attachment_price",
P.ip "post.ip",
P.ip_loc "post.ip_loc",
P.is_del "post.is_del",
P.created_on "post.created_on",
P.modified_on "post.modified_on",
P.deleted_on "post.deleted_on"
FROM
p_post_collection s
JOIN p_post P ON s.post_id = P.ID
WHERE
s.post_id = $1
AND s.user_id = $2
AND s.is_del = 0
AND (visibility >= 50 OR (visibility = 0 AND P.user_id = $3));
-- name: GetUserPostCollections :many
SELECT
s.*,
P.ID "post.id",
P.user_id "post.user_id",
P.comment_count "post.comment_count",
P.collection_count "post.collection_count",
P.upvote_count "post.upvote_count",
P.share_count "post.share_count",
P.visibility "post.visibility",
P.is_top "post.is_top",
P.is_essence "post.is_essence",
P.is_lock "post.is_lock",
P.latest_replied_on "post.latest_replied_on",
P.tags "post.tags",
P.attachment_price "post.attachment_price",
P.ip "post.ip",
P.ip_loc "post.ip_loc",
P.is_del "post.is_del",
P.created_on "post.created_on",
P.modified_on "post.modified_on",
P.deleted_on "post.deleted_on"
FROM
p_post_collection s
JOIN p_post P ON s.post_id = P.ID
WHERE
s.user_id = $1
AND s.is_del = 0
AND (visibility >= 50 OR ( visibility = 0 AND P.user_id = $2 ) )
ORDER BY
s.ID DESC,
P.ID DESC
LIMIT $3 OFFSET $4;
-- CountGetUserPostCollection :one
SELECT
count(*)
FROM
p_post_collection s
JOIN p_post P ON s.post_id = P.ID
WHERE
s.user_id = $1
AND s.is_del = 0
AND (visibility >= 50 OR (visibility = 0 AND P.user_id = $2));
-- name: GetPostAttachementBill :one
SELECT * FROM p_post_attachment_bill WHERE post_id=$1 AND user_id=$2 AND is_del=0;
-- name: GetPostContentsByIds :many
SELECT *
FROM p_post_content
WHERE post_id = ANY(@ids::BIGINT[]) AND is_del=0;
-- name: GetPostContentById :one
SELECT * FROM p_post_content WHERE id=$1 AND is_del=0;
-- name: UserMediaTweetsByGuest :many
SELECT id,
user_id,
comment_count,
collection_count,
upvote_count,
share_count,
visibility,
is_top,
is_essence,
is_lock,
latest_replied_on,
tags,
attachment_price,
ip,
ip_loc,
created_on,
modified_on,
deleted_on,
is_del
FROM p_post_by_media
WHERE is_del=0 AND user_id=$1 AND visibility=90
ORDER BY latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountUserMediaTweetsByGuest :one
SELECT count(*)
FROM p_post_by_media
WHERE is_del=0 AND user_id=$1 AND visibility>=90;
-- name: UserMediaTweetsByFriend :many
SELECT id,
user_id,
comment_count,
collection_count,
upvote_count,
share_count,
visibility,
is_top,
is_essence,
is_lock,
latest_replied_on,
tags,
attachment_price,
ip,
ip_loc,
created_on,
modified_on,
deleted_on,
is_del
FROM p_post_by_media
WHERE is_del=0 AND user_id=$1 AND visibility>=50
ORDER BY latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountUserMediaTweetsByFriend :one
SELECT count(*)
FROM p_post_by_media
WHERE is_del=0 AND user_id=$1 AND visibility>=50;
-- name: UserMediaTweetsBySelf :many
SELECT id,
user_id,
comment_count,
collection_count,
upvote_count,
share_count,
visibility,
is_top,
is_essence,
is_lock,
latest_replied_on,
tags,
attachment_price,
ip,
ip_loc,
created_on,
modified_on,
deleted_on,
is_del
FROM p_post_by_media
WHERE is_del=0 AND user_id=$1
ORDER BY latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountUserMediaTweetsBySelf :one
SELECT count(*)
FROM p_post_by_media
WHERE is_del=0 AND user_id=$1;
-- name: UserCommentTweetsByGuest :many
SELECT id,
user_id,
comment_count,
collection_count,
upvote_count,
share_count,
visibility,
is_top,
is_essence,
is_lock,
latest_replied_on,
tags,
attachment_price,
ip,
ip_loc,
created_on,
modified_on,
deleted_on,
is_del
FROM p_post_by_comment
WHERE is_del=0 AND comment_user_id=$1 AND visibility>=90
ORDER BY latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountUserCommentTweetsByGuest :one
SELECT count(*)
FROM p_post_by_comment
WHERE is_del=0 AND comment_user_id=$1 AND visibility>=90;
-- name: UserCommentTweetsByFriend :many
SELECT id,
user_id,
comment_count,
collection_count,
upvote_count,
share_count,
visibility,
is_top,
is_essence,
is_lock,
latest_replied_on,
tags,
attachment_price,
ip,
ip_loc,
created_on,
modified_on,
deleted_on,
is_del
FROM p_post_by_comment
WHERE is_del=0 AND comment_user_id=$1 AND visibility>=50
ORDER BY latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountUserCommentTweetsByFriend :one
SELECT count(*)
FROM p_post_by_comment
WHERE is_del=0 AND comment_user_id=$1 AND visibility>=50;
-- name: UserCommentTweetsBySelf :many
SELECT id,
user_id,
comment_count,
collection_count,
upvote_count,
share_count,
visibility,
is_top,
is_essence,
is_lock,
latest_replied_on,
tags,
attachment_price,
ip,
ip_loc,
created_on,
modified_on,
deleted_on,
is_del
FROM p_post_by_comment
WHERE is_del=0 AND comment_user_id=$1
ORDER BY latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountUserCommentTweetsBySelf :one
SELECT count(*)
FROM p_post_by_comment
WHERE is_del=0 AND comment_user_id=$1;
-- name: UserStarTweetsByGuest :many
SELECT
star.*,
post.ID "post.id",
post.created_on "post.created_on",
post.modified_on "post.modified_on",
post.deleted_on "post.deleted_on",
post.is_del "post.is_del",
post.user_id "post.user_id",
post.comment_count "post.comment_count",
post.collection_count "post.collection_count",
post.share_count "post.share_count",
post.upvote_count "post.upvote_count",
post.visibility "post.visibility",
post.is_top "post.is_top",
post.is_essence "post.is_essence",
post.is_lock "post.is_lock",
post.latest_replied_on "post.latest_replied_on",
post.tags "post.tags",
post.attachment_price "post.attachment_price",
post.ip "post.ip",
post.ip_loc "post.ip_loc"
FROM
p_post_star star
JOIN p_post post ON star.post_id = post.ID
WHERE
star.is_del=0
AND star.user_id=$1
AND post.visibility>=90
ORDER BY post.latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountUserStarTweetsByGuest :one
SELECT count(*)
FROM
p_post_star star
JOIN p_post post ON star.post_id = post.ID
WHERE star.is_del=0 AND star.user_id=$1 AND post.visibility>=90;
-- name: UserStarTweetsByFriend :many
SELECT
star.*,
post.ID "post.id",
post.created_on "post.created_on",
post.modified_on "post.modified_on",
post.deleted_on "post.deleted_on",
post.is_del "post.is_del",
post.user_id "post.user_id",
post.comment_count "post.comment_count",
post.collection_count "post.collection_count",
post.share_count "post.share_count",
post.upvote_count "post.upvote_count",
post.visibility "post.visibility",
post.is_top "post.is_top",
post.is_essence "post.is_essence",
post.is_lock "post.is_lock",
post.latest_replied_on "post.latest_replied_on",
post.tags "post.tags",
post.attachment_price "post.attachment_price",
post.ip "post.ip",
post.ip_loc "post.ip_loc"
FROM
p_post_star star
JOIN p_post post ON star.post_id = post.ID
WHERE star.is_del=0 AND star.user_id=$1 AND post.visibility>=50
ORDER BY post.latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountUserStarTweetsByFriend :one
SELECT count(*)
FROM
p_post_star star
JOIN p_post post ON star.post_id = post.ID
WHERE star.is_del=0 AND star.user_id=$1 AND post.visibility>=50;
-- name: UserStarTweetsBySelf :many
SELECT
star.*,
post.ID "post.id",
post.created_on "post.created_on",
post.modified_on "post.modified_on",
post.deleted_on "post.deleted_on",
post.is_del "post.is_del",
post.user_id "post.user_id",
post.comment_count "post.comment_count",
post.collection_count "post.collection_count",
post.share_count "post.share_count",
post.upvote_count "post.upvote_count",
post.visibility "post.visibility",
post.is_top "post.is_top",
post.is_essence "post.is_essence",
post.is_lock "post.is_lock",
post.latest_replied_on "post.latest_replied_on",
post.tags "post.tags",
post.attachment_price "post.attachment_price",
post.ip "post.ip",
post.ip_loc "post.ip_loc"
FROM
p_post_star star
JOIN p_post post ON star.post_id = post.ID
WHERE star.is_del=0 AND star.user_id=$1 AND (post.visibility<>90 OR (post.visibility>=90 AND post.user_id=$2))
ORDER BY post.latest_replied_on DESC
LIMIT $3 OFFSET $4;
-- name: CountUserStarTweetsBySelf :one
SELECT count(*)
FROM
p_post_star star
JOIN p_post post ON star.post_id = post.ID
WHERE star.is_del=0 AND star.user_id=$1 AND (post.visibility<>90 OR (post.visibility>=90 AND post.user_id=$2));
-- name: UserStarTweetsByAdmin :many
SELECT
star.*,
post.ID "post.id",
post.created_on "post.created_on",
post.modified_on "post.modified_on",
post.deleted_on "post.deleted_on",
post.is_del "post.is_del",
post.user_id "post.user_id",
post.comment_count "post.comment_count",
post.collection_count "post.collection_count",
post.share_count "post.share_count",
post.upvote_count "post.upvote_count",
post.visibility "post.visibility",
post.is_top "post.is_top",
post.is_essence "post.is_essence",
post.is_lock "post.is_lock",
post.latest_replied_on "post.latest_replied_on",
post.tags "post.tags",
post.attachment_price "post.attachment_price",
post.ip "post.ip",
post.ip_loc "post.ip_loc"
FROM
p_post_star star
JOIN p_post post ON star.post_id = post.ID
WHERE star.is_del=0 AND star.user_id=$1
ORDER BY post.latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountUserStarTweetsByAdmin :one
SELECT count(*)
FROM
p_post_star star
JOIN p_post post ON star.post_id = post.ID
WHERE star.is_del=0 AND star.user_id=$1;
-- name: ListUserTweets :many
SELECT *
FROM p_post
WHERE user_id=$1 AND visibility>=$2 AND is_essence=$3 AND is_del=0
ORDER BY is_top DESC, latest_replied_on DESC
LIMIT $4 OFFSET $5;
-- name: CountUserTweets :one
SELECT count(*)
FROM p_post
WHERE user_id=$1 AND visibility>=$2 AND is_essence=$3 AND is_del=0;
-- name: ListIndexNewestTweets :many
SELECT *
FROM p_post
WHERE visibility>=90 AND is_del=0
ORDER BY is_top DESC, latest_replied_on DESC
LIMIT $1 OFFSET $2;
-- name: CountIndexNewestTweets :one
SELECT count(*)
FROM p_post
WHERE visibility>=90 AND is_del=0;
-- name: ListIndexHotsTweets :many
SELECT post.*
FROM p_post post
LEFT JOIN p_post_metric metric
ON post.id=metric.post_id
WHERE post.visibility>=90 AND post.is_del=0
ORDER BY post.is_top DESC, metric.rank_score DESC, post.latest_replied_on DESC
LIMIT $1 OFFSET $2;
-- name: CountIndexHotsTweets :one
SELECT count(*)
FROM p_post post
LEFT JOIN p_post_metric metric
ON post.id=metric.post_id AND metric.is_del=0
WHERE post.visibility>=90 AND post.is_del=0;
-- name: ListSyncSearchTweets :many
SELECT *
FROM p_post
WHERE visibility>=50 AND is_del=0
LIMIT $1 OFFSET $2;
-- name: CountSyncSearchTweets :one
SELECT count(*)
FROM p_post
WHERE visibility>=50 AND is_del=0;
-- name: ListFollowingTweetsFriendFollow :many
SELECT *
FROM p_post
WHERE (user_id=$1 OR (visibility>=50 AND user_id = ANY(@friendIds::BIGINT[])) OR (visibility>=60 AND user_id = ANY(@followIds::BIGINT[]))) AND is_del=0
ORDER BY is_top DESC, latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountFollowingTweetsFriendFollow :one
SELECT count(*)
FROM p_post
WHERE (user_id=$1 OR (visibility>=50 AND user_id = ANY(@fiendIds::BIGINT[])) OR (visibility>=60 AND user_id = ANY(@followIds::BIGINT[]))) AND is_del=0;
-- name: ListFollowingTweetsFriend :many
SELECT *
FROM p_post
WHERE (user_id=$1 OR (visibility>=50 AND user_id = ANY(@friendIds::BIGINT[]))) AND is_del=0
ORDER BY is_top DESC, latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountListFollowingTweetsFriend :one
SELECT count(*)
FROM p_post
WHERE (user_id=$1 OR (visibility>=50 AND user_id = ANY(@friendIds::BIGINT[]))) AND is_del=0;
-- name: ListFollowingTweetsFollow :many
SELECT *
FROM p_post
WHERE (user_id=$1 OR (visibility>=60 AND user_id = ANY(@followIds::BIGINT[]))) AND is_del=0
ORDER BY is_top DESC, latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountFollowingTweetsFollow :one
SELECT count(*)
FROM p_post
WHERE (user_id=$1 OR (visibility>=60 AND user_id = ANY(@followIds::BIGINT[]))) AND is_del=0;
-- name: ListFollowingTweets :many
SELECT *
FROM p_post
WHERE user_id=$1 AND is_del=0
ORDER BY is_top DESC, latest_replied_on DESC
LIMIT $2 OFFSET $3;
-- name: CountFollowingTweets :one
SELECT count(*)
FROM p_post
WHERE user_id=$1 AND is_del=0;
-- name: GetBeFriendIds :many
SELECT user_id FROM p_contact WHERE friend_id=$1 AND is_del=0;
-- name: GetBeFollowIds :many
SELECT follow_id FROM p_following WHERE user_id=$1 AND is_del=0;
--------------------------------------------------------------------------------
-- tweet_manage sql dml
--------------------------------------------------------------------------------
-- name: AddPost :exec
INSERT INTO p_post (user_id, tags, ip, ip_loc, attachment_price, visibility, latest_replied_on, created_on)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
RETURNING id;
-- name: MediaContentByPostId :many
SELECT content FROM p_post_content WHERE post_id=$1 AND is_del=0 AND type IN (3, 4, 5, 7, 8);
-- name: DeletePostById :exec
UPDATE p_post SET is_del=1, deleted_on=$1 WHERE id=$2 AND is_del=0;
-- name: LockPost :one
UPDATE p_post
SET is_lock=1-is_lock, modified_on=$1
WHERE id=$2 AND is_del=0
RETURNING is_lock;
-- name: StickPost :one
UPDATE p_post
SET is_top=1-is_top, modified_on=$1
WHERE id=$2 AND is_del=0
RETURNING is_top;
-- name: VisiblePost :one
UPDATE p_post
SET visibility=$1, is_top=$2, modified_on=$3
WHERE id=$4 AND is_del=0
RETURNING visibility;
-- name: HighlightPost :one
UPDATE p_post
SET is_essence=1-is_essence
WHERE id=$1 AND is_del=0
RETURNING is_essence;
-- name: PostHighlightStatus :one
SELECT user_id, is_essence
FROM p_post
WHERE id=$1 AND is_del=0;
-- name: UpdatePost :exec
UPDATE p_post SET comment_count=$1,
upvote_count=$2,
collection_count=$3,
latest_replied_on=$4,
modified_on=$5
WHERE id=$6 AND is_del=0;
-- name: AddPostStar :one
INSERT INTO p_post_star (post_id, user_id, created_on)
VALUES ($1, $2, $3)
RETURNING id;
-- name: DeletePostStar :exec
UPDATE p_post_star
SET is_del=1, deleted_on=$1
WHERE id=$2 AND is_del=0;
-- name: AddPostCollection :one
INSERT INTO p_post_collection (post_id, user_id, created_on)
VALUES ($1, $2, $3)
RETURNING id;
-- name: DeletePostCollecton :exec
UPDATE p_post_collection SET is_del=1, deleted_on=$1 WHERE id=$2 AND is_del=0;
-- name: AddPostContent :one
INSERT INTO p_post_content (post_id, user_id, content, type, sort, created_on)
VALUES ($1, $2, $3, $4, $5, $6)
RETURNING id;
-- name: AddAttachment :one
INSERT INTO p_attachment (user_id, file_size, img_width, img_height, type, content, created_on)
VALUES ($1, $2, $3, $4, $5, $6, $7)
RETURNING id;
-- name: CommentIdsByPostId :many
SELECT id FROM p_comment WHERE post_id=$1 AND is_del=0;
-- name: CommentMediaFromCommentIds :many
SELECT content FROM p_comment_content WHERE comment_id = ANY(@ids::BIGINT[]) AND type=3 AND is_del=0;
-- name: DeleteCommentByPostId :exec
UPDATE p_comment SET deleted_on=$1, is_del=1 WHERE post_id=$2 AND is_del=0;
-- name: DeleteCommentContentByCommentIds :exec
UPDATE p_comment_content SET deleted_on=$1, is_del=1 WHERE comment_id = ANY(@ids::BIGINT[]) AND is_del=0;
-- name: DeleteReplyByCommentIds :exec
UPDATE p_comment_reply SET deleted_on=$1, is_del=1 WHERE comment_id = ANY(@ids::BIGINT[]) AND is_del=0;
--------------------------------------------------------------------------------
-- tweet_help sql dml
--------------------------------------------------------------------------------
-- name: GetPostConetentByIds :many
SELECT id, post_id, content, type, sort
FROM p_post_content
WHERE post_id = ANY(@ids::BIGINT[]) AND is_del=0;
Reference in new issue View Git Blame Copy Permalink