Skip to content
Snippets Groups Projects

Jsalyers muting at sql level

Merged Jsalyers muting at sql level
jsalyers-muting-at-sql-level into develop
Merged Jason Salyers requested to merge jsalyers-muting-at-sql-level into develop
Compare
and
1 file
+ 65
− 16
Compare changes
  • Side-by-side
  • Inline
hive/db/sql_scripts/bridge_get_ranked_post_for_observer_communities.sql
+ 65
− 16
@@ -9,7 +9,11 @@ DECLARE
BEGIN
__post_id = find_comment_id( _author, _permlink, True );
__account_id = find_account_id( _observer, True );
RETURN QUERY SELECT
RETURN QUERY
WITH muted_accounts AS (SELECT following as muted_account_id from hive_follows WHERE follower = __account_id AND state = 2 UNION
SELECT hive_follows_indirect.following as muted_account_id FROM hive_follows hive_follows_direct JOIN hive_follows hive_follows_indirect ON hive_follows_direct.following = hive_follows_indirect.follower
WHERE hive_follows_direct.follower = __account_id AND hive_follows_direct.follow_muted AND hive_follows_indirect.state = 2)
SELECT
hp.id,
hp.author,
hp.parent_author,
@@ -45,12 +49,14 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
hive_posts_view hp
JOIN hive_subscriptions hs ON hp.community_id = hs.community_id
JOIN hive_accounts_view ha ON ha.id = hp.author_id
WHERE hs.account_id = __account_id AND hp.depth = 0 AND NOT ha.is_grayed AND ( __post_id = 0 OR hp.id < __post_id )
LEFT JOIN muted_accounts ON hp.author_id = muted_accounts.muted_account_id
WHERE hs.account_id = __account_id AND hp.depth = 0 AND NOT ha.is_grayed AND ( __post_id = 0 OR hp.id < __post_id ) AND muted_accounts.muted_account_id IS NULL
ORDER BY hp.id DESC
LIMIT _limit;
END
@@ -72,7 +78,11 @@ BEGIN
SELECT hp.sc_hot INTO __hot_limit FROM hive_posts hp WHERE hp.id = __post_id;
END IF;
__account_id = find_account_id( _observer, True );
RETURN QUERY SELECT
RETURN QUERY
WITH muted_accounts AS (SELECT following as muted_account_id from hive_follows WHERE follower = __account_id AND state = 2 UNION
SELECT hive_follows_indirect.following as muted_account_id FROM hive_follows hive_follows_direct JOIN hive_follows hive_follows_indirect ON hive_follows_direct.following = hive_follows_indirect.follower
WHERE hive_follows_direct.follower = __account_id AND hive_follows_direct.follow_muted AND hive_follows_indirect.state = 2)
SELECT
hp.id,
hp.author,
hp.parent_author,
@@ -108,12 +118,14 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
hive_posts_view hp
JOIN hive_subscriptions hs ON hp.community_id = hs.community_id
LEFT JOIN muted_accounts ON hp.author_id = muted_accounts.muted_account_id
WHERE hs.account_id = __account_id AND NOT hp.is_paidout AND hp.depth = 0
AND ( __post_id = 0 OR hp.sc_hot < __hot_limit OR ( hp.sc_hot = __hot_limit AND hp.id < __post_id ) )
+1
AND ( __post_id = 0 OR hp.sc_hot < __hot_limit OR ( hp.sc_hot = __hot_limit AND hp.id < __post_id ) ) AND muted_accounts.muted_account_id IS NULL
ORDER BY hp.sc_hot DESC, hp.id DESC
LIMIT _limit;
END
@@ -135,7 +147,11 @@ BEGIN
SELECT ( hp.payout + hp.pending_payout ) INTO __payout_limit FROM hive_posts hp WHERE hp.id = __post_id;
END IF;
__account_id = find_account_id( _observer, True );
RETURN QUERY SELECT
RETURN QUERY
WITH muted_accounts AS (SELECT following as muted_account_id from hive_follows WHERE follower = __account_id AND state = 2 UNION
SELECT hive_follows_indirect.following as muted_account_id FROM hive_follows hive_follows_direct JOIN hive_follows hive_follows_indirect ON hive_follows_direct.following = hive_follows_indirect.follower
WHERE hive_follows_direct.follower = __account_id AND hive_follows_direct.follow_muted AND hive_follows_indirect.state = 2)
SELECT
hp.id,
hp.author,
hp.parent_author,
@@ -171,7 +187,8 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
(
SELECT
@@ -186,6 +203,8 @@ BEGIN
LIMIT _limit
) as payout
JOIN hive_posts_view hp ON hp.id = payout.id
LEFT JOIN muted_accounts ON hp.author_id = muted_accounts.muted_account_id
WHERE muted_accounts.muted_account_id IS NULL
ORDER BY payout.all_payout DESC, payout.id DESC
LIMIT _limit;
END
@@ -209,7 +228,11 @@ BEGIN
END IF;
__account_id = find_account_id( _observer, True );
__head_block_time = head_block_time();
RETURN QUERY SELECT
RETURN QUERY
WITH muted_accounts AS (SELECT following as muted_account_id from hive_follows WHERE follower = __account_id AND state = 2 UNION
SELECT hive_follows_indirect.following as muted_account_id FROM hive_follows hive_follows_direct JOIN hive_follows hive_follows_indirect ON hive_follows_direct.following = hive_follows_indirect.follower
WHERE hive_follows_direct.follower = __account_id AND hive_follows_direct.follow_muted AND hive_follows_indirect.state = 2)
SELECT
hp.id,
hp.author,
hp.parent_author,
@@ -245,12 +268,15 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
hive_posts_view hp
JOIN hive_subscriptions hs ON hp.community_id = hs.community_id
LEFT JOIN muted_accounts ON hp.author_id = muted_accounts.muted_account_id
WHERE hs.account_id = __account_id AND NOT hp.is_paidout AND hp.payout_at BETWEEN __head_block_time + interval '12 hours' AND __head_block_time + interval '36 hours'
AND ( __post_id = 0 OR ( hp.payout + hp.pending_payout ) < __payout_limit OR ( ( hp.payout + hp.pending_payout ) = __payout_limit AND hp.id < __post_id ) )
AND muted_accounts.muted_account_id IS NULL
ORDER BY ( hp.payout + hp.pending_payout ) DESC, hp.id DESC
LIMIT _limit;
END
@@ -272,7 +298,11 @@ BEGIN
SELECT hp.promoted INTO __promoted_limit FROM hive_posts hp WHERE hp.id = __post_id;
END IF;
__account_id = find_account_id( _observer, True );
RETURN QUERY SELECT
RETURN QUERY
WITH muted_accounts AS (SELECT following as muted_account_id from hive_follows WHERE follower = __account_id AND state = 2 UNION
SELECT hive_follows_indirect.following as muted_account_id FROM hive_follows hive_follows_direct JOIN hive_follows hive_follows_indirect ON hive_follows_direct.following = hive_follows_indirect.follower
WHERE hive_follows_direct.follower = __account_id AND hive_follows_direct.follow_muted AND hive_follows_indirect.state = 2)
SELECT
hp.id,
hp.author,
hp.parent_author,
@@ -308,12 +338,15 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
hive_posts_view hp
JOIN hive_subscriptions hs ON hp.community_id = hs.community_id
LEFT JOIN muted_accounts ON hp.author_id = muted_accounts.muted_account_id
WHERE hs.account_id = __account_id AND NOT hp.is_paidout AND hp.promoted > 0
AND ( __post_id = 0 OR hp.promoted < __promoted_limit OR ( hp.promoted = __promoted_limit AND hp.id < __post_id ) )
AND muted_accounts.muted_account_id IS NULL
ORDER BY hp.promoted DESC, hp.id DESC
LIMIT _limit;
END
@@ -327,13 +360,19 @@ AS
$function$
DECLARE
__post_id INT;
__account_id INT;
__trending_limit FLOAT := 0;
BEGIN
__post_id = find_comment_id( _author, _permlink, True );
__account_id = find_account_id( _observer, True );
IF __post_id <> 0 THEN
SELECT hp.sc_trend INTO __trending_limit FROM hive_posts hp WHERE hp.id = __post_id;
END IF;
RETURN QUERY SELECT
RETURN QUERY
WITH muted_accounts AS (SELECT following as muted_account_id from hive_follows WHERE follower = __account_id AND state = 2 UNION
SELECT hive_follows_indirect.following as muted_account_id FROM hive_follows hive_follows_direct JOIN hive_follows hive_follows_indirect ON hive_follows_direct.following = hive_follows_indirect.follower
WHERE hive_follows_direct.follower = __account_id AND hive_follows_direct.follow_muted AND hive_follows_indirect.state = 2)
SELECT
hp.id,
hp.author,
hp.parent_author,
@@ -369,7 +408,8 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
(
SELECT
@@ -386,6 +426,8 @@ BEGIN
LIMIT _limit
) trending
JOIN hive_posts_view hp ON trending.id = hp.id
LEFT JOIN muted_accounts ON hp.author_id = muted_accounts.muted_account_id
WHERE muted_accounts.muted_account_id IS NULL
ORDER BY trending.sc_trend DESC, trending.id DESC
LIMIT _limit;
END
@@ -407,7 +449,11 @@ BEGIN
SELECT ( hp.payout + hp.pending_payout ) INTO __payout_limit FROM hive_posts hp WHERE hp.id = __post_id;
END IF;
__account_id = find_account_id( _observer, True );
RETURN QUERY SELECT
RETURN QUERY
WITH muted_accounts AS (SELECT following as muted_account_id from hive_follows WHERE follower = __account_id AND state = 2 UNION
SELECT hive_follows_indirect.following as muted_account_id FROM hive_follows hive_follows_direct JOIN hive_follows hive_follows_indirect ON hive_follows_direct.following = hive_follows_indirect.follower
WHERE hive_follows_direct.follower = __account_id AND hive_follows_direct.follow_muted AND hive_follows_indirect.state = 2)
SELECT
hp.id,
hp.author,
hp.parent_author,
@@ -443,13 +489,16 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
hive_posts_view hp
JOIN hive_subscriptions hs ON hp.community_id = hs.community_id
JOIN hive_accounts_view ha ON ha.id = hp.author_id
LEFT JOIN muted_accounts ON hp.author_id = muted_accounts.muted_account_id
WHERE hs.account_id = __account_id AND NOT hp.is_paidout AND ha.is_grayed AND ( hp.payout + hp.pending_payout ) > 0
AND ( __post_id = 0 OR ( hp.payout + hp.pending_payout ) < __payout_limit OR ( ( hp.payout + hp.pending_payout ) = __payout_limit AND hp.id < __post_id ) )
AND muted_accounts.muted_account_id IS NULL
ORDER BY ( hp.payout + hp.pending_payout ) DESC, hp.id DESC
LIMIT _limit;
END