15936026264

Committed 27 Jun 2025 09:10PM UTC coverage: 39.947% (-0.1%) from 40.058%

Build # 15936026264

Build Type

Pull #12505

github

Committed by

web-flow

Commit Message

Merge 625dbe137 into f644f13e6

Pull Request Pull Request #12505: Contests/Voting e2e test

Run Details

1844 of 5005 branches covered (36.84%)

Branch coverage included in aggregate %.

0 of 1 new or added line in 1 file covered. (0.0%)

168 existing lines in 10 files now uncovered.

3280 of 7822 relevant lines covered (41.93%)

37.06 hits per line

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

65.57

/libs/model/src/aggregates/thread/GetThreads.query.ts

import { InvalidInput, Query } from '@hicommonwealth/core';
import * as schemas from '@hicommonwealth/schemas';
import { QueryTypes } from 'sequelize';
import { z } from 'zod';
import { models } from '../../database';
import { authOptional } from '../../middleware';
import { filterGates, joinGates, withGates } from '../../utils/gating';

export function GetThreads(): Query<typeof schemas.GetThreads> {
  return {
    ...schemas.GetThreads,
    auth: [authOptional],
    secure: true,
    body: async ({ actor, context, payload }) => {
      const {
        community_id,
        stage,
        topic_id,
        cursor,
        limit,
        order_by,
        from_date,
        to_date,
        archived,
        contestAddress,
        status,
        withXRecentComments = 0,
      } = payload;

      if (stage && status)
        throw new InvalidInput('Cannot provide both stage and status');

      if (status && !contestAddress)
        throw new InvalidInput(
          'Must provide contestAddress if status is provided',
        );

      // query params that bind to sql query
      const _limit = limit ? Math.min(limit, 500) : 20;
      const _page = cursor || 1;
      const replacements = {
        page: _page,
        limit: _limit,
        offset: _limit * (_page - 1) || 0,
        from_date,
        to_date: to_date || new Date().toISOString(),
        community_id,
        address_id: actor.address_id,
        stage,
        topic_id,
        contestAddress,
        status,
        withXRecentComments:
          withXRecentComments > 10 ? 10 : withXRecentComments, // cap to 10
      };

      // sql query parts that order results by provided query param
      const orderByQueries = {
        newest: 'created_at DESC',
        oldest: 'created_at ASC',
        mostLikes: 'reaction_count DESC',
        mostComments: 'comment_count DESC',
        latestActivity: 'updated_at DESC',
      };

      const contestStatus = {
        active: ' AND CON.end_time > NOW()',
        pastWinners: ' AND CON.end_time <= NOW()',
        all: '',
      };

      const sql = `
            ${withGates(actor)},
            contest_ids as (
              SELECT DISTINCT(CA.thread_id)
              FROM "Contests" CON
              JOIN "ContestActions" CA ON CON.contest_id = CA.contest_id
              ${
                contestAddress
                  ? ` WHERE CA.contest_address = '${contestAddress}' `
                  : ''
              }
              ${contestAddress ? contestStatus[status!] || contestStatus.all : ''}
            ),
            top_threads AS (
              SELECT
                T.id,
                T.title,
                T.url,
                T.body,
                T.kind,
                T.stage,
                T.read_only,
                T.discord_meta,
                T.content_url,
                T.pinned,
                T.community_id,
                T.created_at,
                T.updated_at,
                T.locked_at as thread_locked,
                T.links,
                T.has_poll,
                T.last_commented_on,
                T.comment_count,
                T.marked_as_spam_at,
                T.archived_at,
                T.topic_id,
                T.reaction_weights_sum,
                T.canvas_signed_data,
                T.canvas_msg_id,
                T.last_edited,
                T.address_id,
                T.reaction_count,
                (COUNT(id) OVER())::INTEGER AS total_num_thread_results
              FROM
                "Threads" T
                ${joinGates(actor)}
              WHERE
                community_id = :community_id
                AND deleted_at IS NULL
                AND archived_at IS ${archived ? 'NOT' : ''} NULL
                ${filterGates(actor)}
                ${topic_id ? ' AND T.topic_id = :topic_id' : ''}
                ${stage ? ' AND stage = :stage' : ''}
                ${from_date ? ' AND T.created_at > :from_date' : ''}
                ${to_date ? ' AND T.created_at < :to_date' : ''}
                ${contestAddress ? ' AND id IN (SELECT * FROM "contest_ids")' : ''}
              ORDER BY
                pinned DESC, ${orderByQueries[order_by ?? 'newest']}
              LIMIT :limit OFFSET :offset
            ), thread_metadata AS ( -- get the thread authors and their profiles
              SELECT
                TH.id as thread_id,
                json_build_object(
                  'id', T.id,
                  'name', T.name,
                  'description', T.description,
                  'community_id', T.community_id,
                  'telegram', T.telegram,
                  'weighted_voting', T.weighted_voting,
                  'token_decimals', T.token_decimals,
                  'vote_weight_multiplier', T.vote_weight_multiplier,
                  'token_symbol', T.token_symbol
                ) as topic,
                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
              FROM top_threads TH JOIN "Topics" T ON TH.topic_id = T.id
              LEFT JOIN "Addresses" A ON TH.address_id = A.id
              LEFT JOIN "Users" U ON A.user_id = U.id
            ), collaborator_data AS ( -- get the thread collaborators and their profiles
            SELECT
              TT.id as thread_id,
              CASE WHEN max(A.id) IS NOT NULL THEN
                json_agg(json_strip_nulls(json_build_object(
                  'id', editor_profiles.id,
                  'address', A.address,
                  'community_id', A.community_id,
                  'User', json_build_object(
                    'id', editor_profiles.id,
                    'profile', json_build_object(
                      'userId', editor_profiles.id,
                      'name', editor_profiles.profile->>'name',
                      'address', A.address,
                      'lastActive', A.last_active::text,
                      'avatarUrl', editor_profiles.profile->>'avatar_url'
                    ),
                    'tier', editor_profiles.tier
                  )
                )))
              ELSE '[]'::json
              END AS collaborators
            FROM top_threads TT LEFT JOIN "Collaborations" AS C ON TT.id = C.thread_id
            LEFT JOIN "Addresses" A ON C.address_id = A.id
            LEFT JOIN "Users" editor_profiles ON A.user_id = editor_profiles.id
            WHERE A.user_id IS NOT NULL
            GROUP BY TT.id
          ), reaction_data AS ( -- get the thread reactions and the address/profile of the user who reacted
            SELECT
              TT.id as thread_id,
              json_agg(json_strip_nulls(json_build_object(
              'id', R.id,
              'address_id', R.address_id,
              'reaction', R.reaction,
              'updated_at', R.updated_at::text,
              'calculated_voting_weight', R.calculated_voting_weight::text,
              'profile_name', U.profile->>'name',
              'avatar_url', U.profile->>'avatar_url',
              'address', A.address,
              'last_active', A.last_active::text
            ))) as "reactions"
            FROM "Reactions" R JOIN top_threads TT ON TT.id = R.thread_id
            JOIN "Addresses" A ON A.id = R.address_id
            JOIN "Users" U ON U.id = A.user_id
            -- where clause doesn't change query result but forces DB to use the correct indexes
            WHERE R.thread_id = TT.id
            GROUP BY TT.id
          ), contest_data AS ( -- get the contest data associated with the thread
            SELECT
              TT.id as thread_id,
              json_agg(json_strip_nulls(json_build_object(
              'contest_id', CON.contest_id,
              'contest_name', CM.name,
              'contest_cancelled', CM.cancelled,
              'contest_interval', CM.interval,
              'contest_address', CON.contest_address,
              'score', CON.score,
              'thread_id', TT.id,
              'content_id', CA.content_id,
              'start_time', CON.start_time,
              'end_time', CON.end_time,
              'ContestManager', json_build_object(
                'name', CM.name,
                'cancelled', CM.cancelled,
                'interval', CM.interval
              )
            ))) as "associatedContests"
            FROM "Contests" CON
            JOIN "ContestManagers" CM ON CM.contest_address = CON.contest_address
            JOIN "ContestActions" CA ON CON.contest_id = CA.contest_id
            AND CON.contest_address = CA.contest_address AND CA.action = 'upvoted'
            JOIN top_threads TT ON TT.id = CA.thread_id
            GROUP BY TT.id
          )${
            withXRecentComments
              ? `, recent_comments AS ( -- get the recent comments data associated with the thread
              SELECT
                TT.id as thread_id,
                json_agg(json_strip_nulls(json_build_object(
                'id', COM.id,
                'address', A.address,
                'body', COM.body,
                'created_at', COM.created_at::text,
                'updated_at', COM.updated_at::text,
                'deleted_at', COM.deleted_at::text,
                'marked_as_spam_at', COM.marked_as_spam_at::text,
                'discord_meta', COM.discord_meta,
                'profile_name', U.profile->>'name',
                'profile_avatar', U.profile->>'avatar_url',
                'user_id', U.id,
                'user_tier', U.tier,
                'content_url', COM.content_url
              ))) as "recentComments"
              FROM (
                Select tempC.* FROM "Comments" tempC
                JOIN top_threads tempTT ON tempTT.id = tempC.thread_id
                WHERE deleted_at IS NULL
                ORDER BY created_at DESC
                LIMIT :withXRecentComments
              ) COM
              JOIN top_threads TT ON TT.id = COM.thread_id
              JOIN "Addresses" A ON A.id = COM.address_id
              JOIN "Users" U ON U.id = A.user_id
              GROUP BY TT.id
            )`
              : ''
          }
        SELECT
          TT.*, TM.*, CD.*, RD.*, COND.*
          ${withXRecentComments ? `, RC.*` : ''}
        FROM top_threads TT
        LEFT JOIN thread_metadata TM ON TT.id = TM.thread_id
        LEFT JOIN collaborator_data CD ON TT.id = CD.thread_id
        LEFT JOIN reaction_data RD ON TT.id = RD.thread_id
        LEFT JOIN contest_data COND ON TT.id = COND.thread_id
        ${
          withXRecentComments
            ? `LEFT JOIN recent_comments RC ON TT.id = RC.thread_id;`
            : ''
        }
      `;

      const threads = await models.sequelize.query<
        z.infer<typeof schemas.ThreadView>
      >(sql, {
        replacements,
        type: QueryTypes.SELECT,
      });

      return schemas.buildPaginatedResponse(
        threads,
        threads.at(0)?.total_num_thread_results || 0,
        { limit: replacements.limit, cursor: replacements.page },
      );
    },
  };
}

1	import { InvalidInput, Query } from '@hicommonwealth/core';
2	import * as schemas from '@hicommonwealth/schemas';
3	import { QueryTypes } from 'sequelize';
4	import { z } from 'zod';
5	import { models } from '../../database';
6	import { authOptional } from '../../middleware';
7	import { filterGates, joinGates, withGates } from '../../utils/gating';
8
9	export function GetThreads(): Query<typeof schemas.GetThreads> {
10	return {	3✔
11	...schemas.GetThreads,
12	auth: [authOptional],
13	secure: true,
14	body: async ({ actor, context, payload }) => {
15	const {
16	community_id,
17	stage,
18	topic_id,
19	cursor,
20	limit,
21	order_by,
22	from_date,
23	to_date,
24	archived,
25	contestAddress,
26	status,
27	withXRecentComments = 0,	3✔
28	} = payload;	3✔
29
30	if (stage && status)	3!
UNCOV 31	throw new InvalidInput('Cannot provide both stage and status');	×
32
33	if (status && !contestAddress)	3!
UNCOV 34	throw new InvalidInput(	×
35	'Must provide contestAddress if status is provided',
36	);
37
38	// query params that bind to sql query
39	const _limit = limit ? Math.min(limit, 500) : 20;	3!
40	const _page = cursor \|\| 1;	3!
41	const replacements = {	3✔
42	page: _page,
43	limit: _limit,
44	offset: _limit * (_page - 1) \|\| 0,	6✔
45	from_date,
46	to_date: to_date \|\| new Date().toISOString(),	6✔
47	community_id,
48	address_id: actor.address_id,
49	stage,
50	topic_id,
51	contestAddress,
52	status,
53	withXRecentComments:
54	withXRecentComments > 10 ? 10 : withXRecentComments, // cap to 10	3!
55	};
56
57	// sql query parts that order results by provided query param
58	const orderByQueries = {	3✔
59	newest: 'created_at DESC',
60	oldest: 'created_at ASC',
61	mostLikes: 'reaction_count DESC',
62	mostComments: 'comment_count DESC',
63	latestActivity: 'updated_at DESC',
64	};
65
66	const contestStatus = {	3✔
67	active: ' AND CON.end_time > NOW()',
68	pastWinners: ' AND CON.end_time <= NOW()',
69	all: '',
70	};
71
72	const sql = `	3✔
73	${withGates(actor)},
74	contest_ids as (
75	SELECT DISTINCT(CA.thread_id)
76	FROM "Contests" CON
77	JOIN "ContestActions" CA ON CON.contest_id = CA.contest_id
78	${
79	contestAddress	3!
80	? ` WHERE CA.contest_address = '${contestAddress}' `
81	: ''
82	}
83	${contestAddress ? contestStatus[status!] \|\| contestStatus.all : ''}	3!
84	),
85	top_threads AS (
86	SELECT
87	T.id,
88	T.title,
89	T.url,
90	T.body,
91	T.kind,
92	T.stage,
93	T.read_only,
94	T.discord_meta,
95	T.content_url,
96	T.pinned,
97	T.community_id,
98	T.created_at,
99	T.updated_at,
100	T.locked_at as thread_locked,
101	T.links,
102	T.has_poll,
103	T.last_commented_on,
104	T.comment_count,
105	T.marked_as_spam_at,
106	T.archived_at,
107	T.topic_id,
108	T.reaction_weights_sum,
109	T.canvas_signed_data,
110	T.canvas_msg_id,
111	T.last_edited,
112	T.address_id,
113	T.reaction_count,
114	(COUNT(id) OVER())::INTEGER AS total_num_thread_results
115	FROM
116	"Threads" T
117	${joinGates(actor)}
118	WHERE
119	community_id = :community_id
120	AND deleted_at IS NULL
121	AND archived_at IS ${archived ? 'NOT' : ''} NULL	3!
122	${filterGates(actor)}
123	${topic_id ? ' AND T.topic_id = :topic_id' : ''}	3✔
124	${stage ? ' AND stage = :stage' : ''}	3!
125	${from_date ? ' AND T.created_at > :from_date' : ''}	3!
126	${to_date ? ' AND T.created_at < :to_date' : ''}	3!
127	${contestAddress ? ' AND id IN (SELECT * FROM "contest_ids")' : ''}	3!
128	ORDER BY
129	pinned DESC, ${orderByQueries[order_by ?? 'newest']}	6✔
130	LIMIT :limit OFFSET :offset
131	), thread_metadata AS ( -- get the thread authors and their profiles
132	SELECT
133	TH.id as thread_id,
134	json_build_object(
135	'id', T.id,
136	'name', T.name,
137	'description', T.description,
138	'community_id', T.community_id,
139	'telegram', T.telegram,
140	'weighted_voting', T.weighted_voting,
141	'token_decimals', T.token_decimals,
142	'vote_weight_multiplier', T.vote_weight_multiplier,
143	'token_symbol', T.token_symbol
144	) as topic,
145	json_build_object(
146	'id', A.id,
147	'address', A.address,
148	'community_id', A.community_id
149	) as "Address",
150	U.id as user_id,
151	U.tier as user_tier,
152	A.last_active as address_last_active,
153	U.profile->>'avatar_url' as avatar_url,
154	U.profile->>'name' as profile_name
155	FROM top_threads TH JOIN "Topics" T ON TH.topic_id = T.id
156	LEFT JOIN "Addresses" A ON TH.address_id = A.id
157	LEFT JOIN "Users" U ON A.user_id = U.id
158	), collaborator_data AS ( -- get the thread collaborators and their profiles
159	SELECT
160	TT.id as thread_id,
161	CASE WHEN max(A.id) IS NOT NULL THEN
162	json_agg(json_strip_nulls(json_build_object(
163	'id', editor_profiles.id,
164	'address', A.address,
165	'community_id', A.community_id,
166	'User', json_build_object(
167	'id', editor_profiles.id,
168	'profile', json_build_object(
169	'userId', editor_profiles.id,
170	'name', editor_profiles.profile->>'name',
171	'address', A.address,
172	'lastActive', A.last_active::text,
173	'avatarUrl', editor_profiles.profile->>'avatar_url'
174	),
175	'tier', editor_profiles.tier
176	)
177	)))
178	ELSE '[]'::json
179	END AS collaborators
180	FROM top_threads TT LEFT JOIN "Collaborations" AS C ON TT.id = C.thread_id
181	LEFT JOIN "Addresses" A ON C.address_id = A.id
182	LEFT JOIN "Users" editor_profiles ON A.user_id = editor_profiles.id
183	WHERE A.user_id IS NOT NULL
184	GROUP BY TT.id
185	), reaction_data AS ( -- get the thread reactions and the address/profile of the user who reacted
186	SELECT
187	TT.id as thread_id,
188	json_agg(json_strip_nulls(json_build_object(
189	'id', R.id,
190	'address_id', R.address_id,
191	'reaction', R.reaction,
192	'updated_at', R.updated_at::text,
193	'calculated_voting_weight', R.calculated_voting_weight::text,
194	'profile_name', U.profile->>'name',
195	'avatar_url', U.profile->>'avatar_url',
196	'address', A.address,
197	'last_active', A.last_active::text
198	))) as "reactions"
199	FROM "Reactions" R JOIN top_threads TT ON TT.id = R.thread_id
200	JOIN "Addresses" A ON A.id = R.address_id
201	JOIN "Users" U ON U.id = A.user_id
202	-- where clause doesn't change query result but forces DB to use the correct indexes
203	WHERE R.thread_id = TT.id
204	GROUP BY TT.id
205	), contest_data AS ( -- get the contest data associated with the thread
206	SELECT
207	TT.id as thread_id,
208	json_agg(json_strip_nulls(json_build_object(
209	'contest_id', CON.contest_id,
210	'contest_name', CM.name,
211	'contest_cancelled', CM.cancelled,
212	'contest_interval', CM.interval,
213	'contest_address', CON.contest_address,
214	'score', CON.score,
215	'thread_id', TT.id,
216	'content_id', CA.content_id,
217	'start_time', CON.start_time,
218	'end_time', CON.end_time,
219	'ContestManager', json_build_object(
220	'name', CM.name,
221	'cancelled', CM.cancelled,
222	'interval', CM.interval
223	)
224	))) as "associatedContests"
225	FROM "Contests" CON
226	JOIN "ContestManagers" CM ON CM.contest_address = CON.contest_address
227	JOIN "ContestActions" CA ON CON.contest_id = CA.contest_id
228	AND CON.contest_address = CA.contest_address AND CA.action = 'upvoted'
229	JOIN top_threads TT ON TT.id = CA.thread_id
230	GROUP BY TT.id
231	)${
232	withXRecentComments	3!
233	? `, recent_comments AS ( -- get the recent comments data associated with the thread
234	SELECT
235	TT.id as thread_id,
236	json_agg(json_strip_nulls(json_build_object(
237	'id', COM.id,
238	'address', A.address,
239	'body', COM.body,
240	'created_at', COM.created_at::text,
241	'updated_at', COM.updated_at::text,
242	'deleted_at', COM.deleted_at::text,
243	'marked_as_spam_at', COM.marked_as_spam_at::text,
244	'discord_meta', COM.discord_meta,
245	'profile_name', U.profile->>'name',
246	'profile_avatar', U.profile->>'avatar_url',
247	'user_id', U.id,
248	'user_tier', U.tier,
249	'content_url', COM.content_url
250	))) as "recentComments"
251	FROM (
252	Select tempC.* FROM "Comments" tempC
253	JOIN top_threads tempTT ON tempTT.id = tempC.thread_id
254	WHERE deleted_at IS NULL
255	ORDER BY created_at DESC
256	LIMIT :withXRecentComments
257	) COM
258	JOIN top_threads TT ON TT.id = COM.thread_id
259	JOIN "Addresses" A ON A.id = COM.address_id
260	JOIN "Users" U ON U.id = A.user_id
261	GROUP BY TT.id
262	)`
263	: ''
264	}
265	SELECT
266	TT., TM., CD., RD., COND.*
267	${withXRecentComments ? `, RC.*` : ''}	3!
268	FROM top_threads TT
269	LEFT JOIN thread_metadata TM ON TT.id = TM.thread_id
270	LEFT JOIN collaborator_data CD ON TT.id = CD.thread_id
271	LEFT JOIN reaction_data RD ON TT.id = RD.thread_id
272	LEFT JOIN contest_data COND ON TT.id = COND.thread_id
273	${
274	withXRecentComments	3!
275	? `LEFT JOIN recent_comments RC ON TT.id = RC.thread_id;`
276	: ''
277	}
278	`;
279
280	const threads = await models.sequelize.query<	3✔
281	z.infer<typeof schemas.ThreadView>
282	>(sql, {
283	replacements,
284	type: QueryTypes.SELECT,
285	});
286
287	return schemas.buildPaginatedResponse(	3✔
288	threads,
289	threads.at(0)?.total_num_thread_results \|\| 0,	4✔
290	{ limit: replacements.limit, cursor: replacements.page },
291	);
292	},
293	};
294	}

hicommonwealth / commonwealth / 15936026264

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