Jsalyers muting at sql level

hive/db/sql_scripts/bridge_get_ranked_post_for_observer_communities.sql

@@ -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