Skip to content
Snippets Groups Projects

Jsalyers muting at sql level

Merged Jsalyers muting at sql level
jsalyers-muting-at-sql-level into develop
All threads resolved!
Merged Jason Salyers requested to merge jsalyers-muting-at-sql-level into develop
All threads resolved!
Viewing commit 3e358504
Prev Next
Show latest version
8 files
+ 10
− 36
Compare changes
  • Side-by-side
  • Inline
Files
8
  • 3e358504 
    [Fix] get_account_reputations no longer fails with error when account_lower_bound is skipped; also added real default value for limit (1000)
[Fix] bridge_get_ranked_post_by_trends_for_observer_communities now validates observer like other functions of the same family
[Fix] all VOLATILE get_ranked_post related SQL functions turned into STABLE
hive/db/sql_scripts/bridge_get_ranked_post_for_observer_communities.sql
+ 23
− 8
@@ -45,12 +45,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 )
AND (CASE WHEN _observer IS NOT NULL THEN NOT EXISTS (SELECT 1 FROM muted_accounts_view WHERE observer = _observer AND muted = hp.author) ELSE True END)
ORDER BY hp.id DESC
LIMIT _limit;
END
@@ -72,7 +74,7 @@ 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 SELECT
hp.id,
hp.author,
hp.parent_author,
@@ -108,12 +110,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
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 ) )
AND (CASE WHEN _observer IS NOT NULL THEN NOT EXISTS (SELECT 1 FROM muted_accounts_view WHERE observer = _observer AND muted = hp.author) ELSE True END)
ORDER BY hp.sc_hot DESC, hp.id DESC
LIMIT _limit;
END
@@ -171,7 +175,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 +191,7 @@ BEGIN
LIMIT _limit
) as payout
JOIN hive_posts_view hp ON hp.id = payout.id
WHERE (CASE WHEN _observer IS NOT NULL THEN NOT EXISTS (SELECT 1 FROM muted_accounts_view WHERE observer = _observer AND muted = hp.author) ELSE True END)
ORDER BY payout.all_payout DESC, payout.id DESC
LIMIT _limit;
END
@@ -245,12 +251,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
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 (CASE WHEN _observer IS NOT NULL THEN NOT EXISTS (SELECT 1 FROM muted_accounts_view WHERE observer = _observer AND muted = hp.author) ELSE True END)
ORDER BY ( hp.payout + hp.pending_payout ) DESC, hp.id DESC
LIMIT _limit;
END
@@ -308,12 +316,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
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 (CASE WHEN _observer IS NOT NULL THEN NOT EXISTS (SELECT 1 FROM muted_accounts_view WHERE observer = _observer AND muted = hp.author) ELSE True END)
ORDER BY hp.promoted DESC, hp.id DESC
LIMIT _limit;
END
@@ -331,6 +341,7 @@ DECLARE
__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;
@@ -371,7 +382,8 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
(
SELECT
@@ -387,6 +399,7 @@ BEGIN
LIMIT _limit
) trending
JOIN hive_posts_view hp ON trending.id = hp.id
WHERE (CASE WHEN _observer IS NOT NULL THEN NOT EXISTS (SELECT 1 FROM muted_accounts_view WHERE observer = _observer AND muted = hp.author) ELSE True END)
ORDER BY trending.sc_trend DESC, trending.id DESC
LIMIT _limit;
END
@@ -444,13 +457,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
JOIN hive_accounts_view ha ON ha.id = hp.author_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 (CASE WHEN _observer IS NOT NULL THEN NOT EXISTS (SELECT 1 FROM muted_accounts_view WHERE observer = _observer AND muted = hp.author) ELSE True END)
ORDER BY ( hp.payout + hp.pending_payout ) DESC, hp.id DESC
LIMIT _limit;
END