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.
76 lines
2.0 KiB
76 lines
2.0 KiB
--------------------------------------------------------------------------------
|
|
-- message sql dml
|
|
--------------------------------------------------------------------------------
|
|
|
|
-- name: GetUnreadCount :one
|
|
SELECT count(*) FROM p_message WHERE receiver_user_id=$1 AND is_read=0 AND is_del=0;
|
|
|
|
-- name: GetMessageById :one
|
|
SELECT * FROM p_message WHERE id=$1 AND is_del=0;
|
|
|
|
-- name: ReadMessage :exec
|
|
UPDATE p_message SET is_read=1, modified_on=$1 WHERE id=$2;
|
|
|
|
-- name: ReadAllMessage :exec
|
|
UPDATE p_message SET is_read=1, modified_on=$1 WHERE receiver_user_id=$2;
|
|
|
|
-- name: GetSystemMessages :many
|
|
SELECT *
|
|
FROM p_message
|
|
WHERE receiver_user_id=$1 AND type IN (1, 2, 3, 99) AND is_del=0
|
|
ORDER BY id DESC
|
|
LIMIT $2 OFFSET $3;
|
|
|
|
-- name: CountSystemMessages :one
|
|
SELECT count(*)
|
|
FROM p_message
|
|
WHERE receiver_user_id=$1 AND type IN (1, 2, 3, 99) AND is_del=0;
|
|
|
|
-- name: GetWhisperMessages :many
|
|
SELECT *
|
|
FROM p_message
|
|
WHERE ((receiver_user_id=$1 OR sender_user_id=$2) AND type=4) AND is_del=0
|
|
ORDER BY id DESC
|
|
LIMIT $3 OFFSET $4;
|
|
|
|
-- name: CountWhisperMessages :one
|
|
SELECT count(*)
|
|
FROM p_message
|
|
WHERE ((receiver_user_id=$1 OR sender_user_id=$2) AND type=4) AND is_del=0;
|
|
|
|
-- name: GetRequestingMessages :many
|
|
SELECT *
|
|
FROM p_message
|
|
WHERE receiver_user_id=$1 AND type=5 AND is_del=0
|
|
ORDER BY id DESC
|
|
LIMIT $2 OFFSET $3;
|
|
|
|
-- name: CountRequestingMessages :one
|
|
SELECT count(*)
|
|
FROM p_message
|
|
WHERE receiver_user_id=$1 AND type=5 AND is_del=0;
|
|
|
|
-- name: GetUnreadMessages :many
|
|
SELECT *
|
|
FROM p_message
|
|
WHERE receiver_user_id=$1 AND is_read=0 AND is_del=0
|
|
ORDER BY id DESC
|
|
LIMIT $2 OFFSET $3;
|
|
|
|
-- name: CountUnreadMessages :one
|
|
SELECT count(*)
|
|
FROM p_message
|
|
WHERE receiver_user_id=$1 AND is_read=0 AND is_del=0;
|
|
|
|
-- name: GetAllMessages :many
|
|
SELECT *
|
|
FROM p_message
|
|
WHERE (receiver_user_id=$1 OR (sender_user_id=$2 AND type=4)) AND is_del=0
|
|
ORDER BY id DESC
|
|
LIMIT $3 OFFSET $4;
|
|
|
|
-- name: CountAllMessages :one
|
|
SELECT count(*)
|
|
FROM p_message
|
|
WHERE (receiver_user_id=$1 OR (sender_user_id=$2 AND type=4)) AND is_del=0;
|