15976506053

Committed 30 Jun 2025 03:01PM UTC coverage: 39.972% (+0.01%) from 39.958%

Build # 15976506053

Build Type

push

github

Committed by

web-flow

Commit Message

Merge pull request #12564 from hicommonwealth/rotorsoft/12548-fix-activity-search-for-gating

Fixes feed and search queries with general topic gating - not scoped to any community

Coverage Stats

1847 of 5007 branches covered (36.89%)

Branch coverage included in aggregate %.

4 of 13 new or added lines in 4 files covered. (30.77%)

1 existing line in 1 file now uncovered.

3283 of 7827 relevant lines covered (41.94%)

37.04 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

11.11

/libs/model/src/utils/getBaseActivityFeed.ts

import { Actor } from '@hicommonwealth/core';
import { CommunityTierMap } from '@hicommonwealth/shared';
import { buildGatedOutput, buildOpenGates } from './gating';

/**
 * Base query for global and user activity feeds
 * - Based on `gated_output` CTE from the builder functions
 */
const baseActivityQuery = `
  SELECT
    T.community_id,
    T.icon_url as community_icon,
    T.id,
    T.address_id,
    json_build_object(
        'id', A.id,
        'address', A.address,
        'community_id', A.community_id
    ) as "Address",
    U.id as user_id,
    U.tier as user_tier,
    A.last_active as address_last_active,
    U.profile->>'avatar_url' as avatar_url,
    U.profile->>'name' as profile_name,
    T.body,
    T.content_url,
    T.title,
    T.kind,
    T.stage,
    T.comment_count,
    T.created_at::text,
    T.updated_at::text,
    T.deleted_at::text,
    T.locked_at::text,
    T.archived_at::text,
    T.marked_as_spam_at::text,
    T.read_only,
    T.has_poll,
    T.discord_meta,
    T.is_linking_token,
    T.topic_id,
    jsonb_build_object(
      'community_id', T.community_id,
      'id', T.topic_id,
      'name', Tp.name,
      'description', Tp.description
    ) as topic,
    COALESCE(
      (SELECT jsonb_agg(jsonb_strip_nulls(jsonb_build_object(
        'id', C.id,
        'address', C.address,
        'user_id', C.user_id,
        'user_tier', C.user_tier,
        'profile_name', C.profile_name,
        'profile_avatar', C.profile_avatar,
        'body', C.body,
        'content_url', C.content_url,
        'created_at', C.created_at::text,
        'updated_at', C.updated_at::text,
        'deleted_at', C.deleted_at::text,
        'marked_as_spam_at', C.marked_as_spam_at::text,
        'discord_meta', C.discord_meta
      )) ORDER BY C.created_at DESC)
        FROM (
          SELECT
            C.*,
            A.address,
            U.id as user_id,
            U.tier as user_tier,
            U.profile->>'name' as profile_name,
            U.profile->>'avatar_url' as profile_avatar,
            ROW_NUMBER() OVER (PARTITION BY C.thread_id ORDER BY C.created_at DESC) AS rn
          FROM "Comments" C
                  JOIN "Addresses" A on C.address_id = A.id
                  JOIN "Users" U on A.user_id = U.id
          WHERE
            C.thread_id = T.id
            AND C.deleted_at IS NULL
        ) C WHERE C.rn <= :comment_limit), '[]') as comments
  FROM
    gated_output T
      JOIN "Addresses" A ON A.id = T.address_id AND A.community_id = T.community_id
      JOIN "Users" U ON U.id = A.user_id
      JOIN "Topics" Tp ON Tp.id = T.topic_id
`;

/**
 * Global activity feed query builder
 * - Can be scoped to a specific community or search term
 */
export function buildGlobalActivityQuery(
  actor: Actor,
  community_id?: string,
  search?: string,
) {
  const query = `
WITH output_with_topics AS (
  SELECT
    T.*,
    C.icon_url,
    count(*) OVER () AS total
  FROM
    "Threads" T
    JOIN "Communities" C ON C.id = T.community_id 
  WHERE
    T.id IN (:threadIds)
    ${community_id ? 'AND T.community_id = :community_id' : ''}
    ${search ? 'AND T.title ILIKE :search' : ''}
),
${buildGatedOutput(actor)}
${baseActivityQuery}
ORDER BY ARRAY_POSITION(ARRAY[:threadIds], T.id);
`;

  return query;
}

/**
 * User activity feed query builder
 */
export function buildUserActivityQuery(actor: Actor) {
  const query = `
WITH user_communities AS (
  SELECT DISTINCT community_id FROM "Addresses" WHERE user_id = ${actor.user?.id}
),
${buildOpenGates(actor)},
gated_output AS (
  SELECT
    T.*,
    count(*) OVER() AS total,
    C.icon_url
  FROM
    "Threads" T
    JOIN open_gates og ON T.topic_id = og.topic_id
    JOIN user_communities UC ON UC.community_id = T.community_id
    JOIN "Communities" C ON C.id = T.community_id
  WHERE
    T.deleted_at IS NULL 
    AND T.marked_as_spam_at IS NULL 
    AND C.active IS TRUE 
    AND C.tier != ${CommunityTierMap.SpamCommunity}
    AND C.id NOT IN ('ethereum', 'cosmos', 'polkadot')
  ORDER BY
    T.activity_rank_date DESC NULLS LAST
  LIMIT :limit OFFSET :offset 
)
${baseActivityQuery}
ORDER BY T.activity_rank_date DESC NULLS LAST
`;

  return query;
}

1	import { Actor } from '@hicommonwealth/core';
2	import { CommunityTierMap } from '@hicommonwealth/shared';
3	import { buildGatedOutput, buildOpenGates } from './gating';
4
5	/**
6	* Base query for global and user activity feeds
7	* - Based on `gated_output` CTE from the builder functions
8	*/
9	const baseActivityQuery = `	35✔
10	SELECT
11	T.community_id,
12	T.icon_url as community_icon,
13	T.id,
14	T.address_id,
15	json_build_object(
16	'id', A.id,
17	'address', A.address,
18	'community_id', A.community_id
19	) as "Address",
20	U.id as user_id,
21	U.tier as user_tier,
22	A.last_active as address_last_active,
23	U.profile->>'avatar_url' as avatar_url,
24	U.profile->>'name' as profile_name,
25	T.body,
26	T.content_url,
27	T.title,
28	T.kind,
29	T.stage,
30	T.comment_count,
31	T.created_at::text,
32	T.updated_at::text,
33	T.deleted_at::text,
34	T.locked_at::text,
35	T.archived_at::text,
36	T.marked_as_spam_at::text,
37	T.read_only,
38	T.has_poll,
39	T.discord_meta,
40	T.is_linking_token,
41	T.topic_id,
42	jsonb_build_object(
43	'community_id', T.community_id,
44	'id', T.topic_id,
45	'name', Tp.name,
46	'description', Tp.description
47	) as topic,
48	COALESCE(
49	(SELECT jsonb_agg(jsonb_strip_nulls(jsonb_build_object(
50	'id', C.id,
51	'address', C.address,
52	'user_id', C.user_id,
53	'user_tier', C.user_tier,
54	'profile_name', C.profile_name,
55	'profile_avatar', C.profile_avatar,
56	'body', C.body,
57	'content_url', C.content_url,
58	'created_at', C.created_at::text,
59	'updated_at', C.updated_at::text,
60	'deleted_at', C.deleted_at::text,
61	'marked_as_spam_at', C.marked_as_spam_at::text,
62	'discord_meta', C.discord_meta
63	)) ORDER BY C.created_at DESC)
64	FROM (
65	SELECT
66	C.*,
67	A.address,
68	U.id as user_id,
69	U.tier as user_tier,
70	U.profile->>'name' as profile_name,
71	U.profile->>'avatar_url' as profile_avatar,
72	ROW_NUMBER() OVER (PARTITION BY C.thread_id ORDER BY C.created_at DESC) AS rn
73	FROM "Comments" C
74	JOIN "Addresses" A on C.address_id = A.id
75	JOIN "Users" U on A.user_id = U.id
76	WHERE
77	C.thread_id = T.id
78	AND C.deleted_at IS NULL
79	) C WHERE C.rn <= :comment_limit), '[]') as comments
80	FROM
81	gated_output T
82	JOIN "Addresses" A ON A.id = T.address_id AND A.community_id = T.community_id
83	JOIN "Users" U ON U.id = A.user_id
84	JOIN "Topics" Tp ON Tp.id = T.topic_id
85	`;
86
87	/**
88	* Global activity feed query builder
89	* - Can be scoped to a specific community or search term
90	*/
91	export function buildGlobalActivityQuery(
92	actor: Actor,
93	community_id?: string,
94	search?: string,
95	) {
96	const query = `	×
97	WITH output_with_topics AS (
98	SELECT
99	T.*,
100	C.icon_url,
101	count(*) OVER () AS total
102	FROM
103	"Threads" T
104	JOIN "Communities" C ON C.id = T.community_id
105	WHERE
106	T.id IN (:threadIds)
107	${community_id ? 'AND T.community_id = :community_id' : ''}	×
108	${search ? 'AND T.title ILIKE :search' : ''}	×
109	),
110	${buildGatedOutput(actor)}
111	${baseActivityQuery}
112	ORDER BY ARRAY_POSITION(ARRAY[:threadIds], T.id);
113	`;
114
NEW 115	return query;	×
116	}
117
118	/**
119	* User activity feed query builder
120	*/
121	export function buildUserActivityQuery(actor: Actor) {
NEW 122	const query = `	×
123	WITH user_communities AS (
124	SELECT DISTINCT community_id FROM "Addresses" WHERE user_id = ${actor.user?.id}
125	),
126	${buildOpenGates(actor)},
127	gated_output AS (
128	SELECT
129	T.*,
130	count(*) OVER() AS total,
131	C.icon_url
132	FROM
133	"Threads" T
134	JOIN open_gates og ON T.topic_id = og.topic_id
135	JOIN user_communities UC ON UC.community_id = T.community_id
136	JOIN "Communities" C ON C.id = T.community_id
137	WHERE
138	T.deleted_at IS NULL
139	AND T.marked_as_spam_at IS NULL
140	AND C.active IS TRUE
141	AND C.tier != ${CommunityTierMap.SpamCommunity}
142	AND C.id NOT IN ('ethereum', 'cosmos', 'polkadot')
143	ORDER BY
144	T.activity_rank_date DESC NULLS LAST
145	LIMIT :limit OFFSET :offset
146	)
147	${baseActivityQuery}
148	ORDER BY T.activity_rank_date DESC NULLS LAST
149	`;
150
NEW 151	return query;	×
152	}

hicommonwealth / commonwealth / 15976506053

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous