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 8cb20ba0
Prev Next
Show latest version
1 file
+ 1
− 1
Compare changes
  • Side-by-side
  • Inline
hive/db/sql_scripts/bridge_get_ranked_post_for_tag.sql
+ 28
− 14
DROP FUNCTION IF EXISTS bridge_get_ranked_post_by_created_for_tag;
CREATE FUNCTION bridge_get_ranked_post_by_created_for_tag( in _tag VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT )
CREATE FUNCTION bridge_get_ranked_post_by_created_for_tag( in _tag VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT, in _observer VARCHAR )
RETURNS SETOF bridge_api_post
AS
$function$
@@ -45,7 +45,8 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
(
SELECT
@@ -60,6 +61,7 @@ BEGIN
LIMIT _limit
) as created
JOIN hive_posts_view hp ON hp.id = created.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 created.id DESC
LIMIT _limit;
END
@@ -67,7 +69,7 @@ $function$
language plpgsql STABLE;
DROP FUNCTION IF EXISTS bridge_get_ranked_post_by_hot_for_tag;
CREATE FUNCTION bridge_get_ranked_post_by_hot_for_tag( in _tag VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT )
CREATE FUNCTION bridge_get_ranked_post_by_hot_for_tag( in _tag VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT, in _observer VARCHAR )
RETURNS SETOF bridge_api_post
AS
$function$
@@ -117,7 +119,8 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
(
SELECT
@@ -132,6 +135,7 @@ BEGIN
LIMIT _limit
) as hot
JOIN hive_posts_view hp ON hp.id = hot.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 hot.hot DESC, hot.id DESC
LIMIT _limit;
END
@@ -139,7 +143,7 @@ $function$
language plpgsql STABLE;
DROP FUNCTION IF EXISTS bridge_get_ranked_post_by_muted_for_tag;
CREATE FUNCTION bridge_get_ranked_post_by_muted_for_tag( in _tag VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT )
CREATE FUNCTION bridge_get_ranked_post_by_muted_for_tag( in _tag VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT, in _observer VARCHAR )
RETURNS SETOF bridge_api_post
AS
$function$
@@ -189,7 +193,8 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
(
SELECT
@@ -205,6 +210,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
@@ -212,7 +218,7 @@ $function$
language plpgsql STABLE;
DROP FUNCTION IF EXISTS bridge_get_ranked_post_by_payout_comments_for_category;
CREATE FUNCTION bridge_get_ranked_post_by_payout_comments_for_category( in _category VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT )
CREATE FUNCTION bridge_get_ranked_post_by_payout_comments_for_category( in _category VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT, in _observer VARCHAR )
RETURNS SETOF bridge_api_post
AS
$function$
@@ -262,7 +268,8 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
(
SELECT
@@ -276,6 +283,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
@@ -283,7 +291,7 @@ $function$
language plpgsql STABLE;
DROP FUNCTION IF EXISTS bridge_get_ranked_post_by_payout_for_category;
CREATE FUNCTION bridge_get_ranked_post_by_payout_for_category( in _category VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT, in _bridge_api BOOLEAN )
CREATE FUNCTION bridge_get_ranked_post_by_payout_for_category( in _category VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT, in _bridge_api BOOLEAN, in _observer VARCHAR )
RETURNS SETOF bridge_api_post
AS
$function$
@@ -335,7 +343,8 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
(
SELECT
@@ -350,6 +359,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
@@ -357,7 +367,7 @@ $function$
language plpgsql STABLE;
DROP FUNCTION IF EXISTS bridge_get_ranked_post_by_promoted_for_tag;
CREATE FUNCTION bridge_get_ranked_post_by_promoted_for_tag( in _tag VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT )
CREATE FUNCTION bridge_get_ranked_post_by_promoted_for_tag( in _tag VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT, in _observer VARCHAR )
RETURNS SETOF bridge_api_post
AS
$function$
@@ -407,7 +417,8 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
(
SELECT
@@ -422,6 +433,7 @@ BEGIN
LIMIT _limit
) as promoted
JOIN hive_posts_view hp ON hp.id = promoted.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 promoted.promoted DESC, promoted.id DESC
LIMIT _limit;
END
@@ -429,7 +441,7 @@ $function$
language plpgsql STABLE;
DROP FUNCTION IF EXISTS bridge_get_ranked_post_by_trends_for_tag;
CREATE FUNCTION bridge_get_ranked_post_by_trends_for_tag( in _tag VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT )
CREATE FUNCTION bridge_get_ranked_post_by_trends_for_tag( in _tag VARCHAR, in _author VARCHAR, in _permlink VARCHAR, in _limit SMALLINT, in _observer VARCHAR )
RETURNS SETOF bridge_api_post
AS
$function$
@@ -479,7 +491,8 @@ BEGIN
hp.community_title,
hp.role_id,
hp.is_pinned,
hp.curator_payout_value
hp.curator_payout_value,
hp.is_muted
FROM
(
SELECT
@@ -494,6 +507,7 @@ BEGIN
LIMIT _limit
) as trends
JOIN hive_posts_view hp ON hp.id = trends.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 trends.trend DESC, trends.id DESC
LIMIT _limit;
END