15443233707

Committed 04 Jun 2025 01:12PM UTC coverage: 41.129% (+0.06%) from 41.072%

Build # 15443233707

Build Type

push

github

Committed by

web-flow

Commit Message

Merge pull request #12277 from hicommonwealth/rotorsoft/12261-update-gated-thread-queries

Updates gated thread queries

Run Details

1727 of 4620 branches covered (37.38%)

Branch coverage included in aggregate %.

13 of 33 new or added lines in 8 files covered. (39.39%)

3 existing lines in 3 files now uncovered.

3171 of 7289 relevant lines covered (43.5%)

37.27 hits per line

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

0.0

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

import { 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 GetActiveThreads(): Query<typeof schemas.GetActiveThreads> {
  return {
    ...schemas.GetActiveThreads,
    auth: [authOptional],
    secure: true,
    body: async ({ context, payload }) => {
      const { community_id, threads_per_topic, withXRecentComments } = payload;
      const address_id = context?.address?.id;

      const sql = `
${withGates(address_id)},
TH 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,
                row_number() OVER (
      PARTITION BY T.topic_id ORDER BY T.created_at DESC,        T.last_commented_on DESC) AS topic_rank
        FROM
                "Threads" T
    ${joinGates(address_id)}
        WHERE
                community_id = :community_id
                AND deleted_at IS NULL
                AND archived_at IS NULL
                ${filterGates(address_id)}
),
T AS ( -- select top by topic and get the thread authors and their profiles
SELECT
  TH.*,
  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
  ) 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
  TH
  JOIN "Topics" T ON TH.topic_id = T.id
  JOIN "Addresses" A ON TH.address_id = A.id
  JOIN "Users" U ON A.user_id = U.id
WHERE
  TH.topic_rank <= :threads_per_topic
ORDER BY
  TH.created_at DESC,
  TH.last_commented_on DESC
), 
collaborator_data AS ( -- get the thread collaborators and their profiles
  SELECT
    T.id as thread_id,
    CASE WHEN max(A.id) IS NOT NULL THEN
        json_agg(json_strip_nulls(json_build_object(
          '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
    T
    LEFT JOIN "Collaborations" AS C ON T.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
  GROUP BY
    T.id
)
${
  withXRecentComments
    ? `, recent_comments AS ( -- get the recent comments data associated with the thread
      SELECT
        T.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 T tempTT ON tempTT.id = tempC.thread_id
        WHERE deleted_at IS NULL
        ORDER BY created_at DESC
        LIMIT :withXRecentComments
      ) COM
      JOIN T ON T.id = COM.thread_id
      JOIN "Addresses" A ON A.id = COM.address_id
      JOIN "Users" U ON U.id = A.user_id
      GROUP BY T.id
  )`
    : ''
}
SELECT
  T.*, CD.*${withXRecentComments ? `, RC.*` : ''}
  FROM
    T
    LEFT JOIN collaborator_data CD ON T.id = CD.thread_id
    ${withXRecentComments ? `LEFT JOIN recent_comments RC ON T.id = RC.thread_id;` : ''}
`;

      return await models.sequelize.query<z.infer<typeof schemas.ThreadView>>(
        sql,
        {
          replacements: {
            community_id,
            address_id,
            threads_per_topic: threads_per_topic || 3,
            withXRecentComments,
          },
          type: QueryTypes.SELECT,
        },
      );
    },
  };
}

1	import { 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 GetActiveThreads(): Query<typeof schemas.GetActiveThreads> {
10	return {	×
11	...schemas.GetActiveThreads,
12	auth: [authOptional],
13	secure: true,
14	body: async ({ context, payload }) => {
UNCOV 15	const { community_id, threads_per_topic, withXRecentComments } = payload;	×
NEW 16	const address_id = context?.address?.id;	×
17
NEW 18	const sql = `	×
19	${withGates(address_id)},
20	TH AS (
21	SELECT
22	T.id,
23	T.title,
24	T.url,
25	T.body,
26	T.kind,
27	T.stage,
28	T.READ_ONLY,
29	T.discord_meta,
30	T.content_url,
31	T.pinned,
32	T.community_id,
33	T.created_at,
34	T.updated_at,
35	T.locked_at AS thread_locked,
36	T.links,
37	T.has_poll,
38	T.last_commented_on,
39	T.comment_count,
40	T.marked_as_spam_at,
41	T.archived_at,
42	T.topic_id,
43	T.reaction_weights_sum,
44	T.canvas_signed_data,
45	T.canvas_msg_id,
46	T.last_edited,
47	T.address_id,
48	T.reaction_count,
49	row_number() OVER (
50	PARTITION BY T.topic_id ORDER BY T.created_at DESC, T.last_commented_on DESC) AS topic_rank
51	FROM
52	"Threads" T
53	${joinGates(address_id)}
54	WHERE
55	community_id = :community_id
56	AND deleted_at IS NULL
57	AND archived_at IS NULL
58	${filterGates(address_id)}
59	),
60	T AS ( -- select top by topic and get the thread authors and their profiles
61	SELECT
62	TH.*,
63	json_build_object(
64	'id', T.id,
65	'name', T.name,
66	'description', T.description,
67	'community_id', T.community_id,
68	'telegram', T.telegram,
69	'weighted_voting', T.weighted_voting,
70	'token_decimals', T.token_decimals,
71	'vote_weight_multiplier', T.vote_weight_multiplier
72	) as topic,
73	json_build_object(
74	'id', A.id,
75	'address', A.address,
76	'community_id', A.community_id
77	) as "Address",
78	U.id as user_id,
79	U.tier as user_tier,
80	A.last_active as address_last_active,
81	U.profile->>'avatar_url' as avatar_url,
82	U.profile->>'name' as profile_name
83	FROM
84	TH
85	JOIN "Topics" T ON TH.topic_id = T.id
86	JOIN "Addresses" A ON TH.address_id = A.id
87	JOIN "Users" U ON A.user_id = U.id
88	WHERE
89	TH.topic_rank <= :threads_per_topic
90	ORDER BY
91	TH.created_at DESC,
92	TH.last_commented_on DESC
93	),
94	collaborator_data AS ( -- get the thread collaborators and their profiles
95	SELECT
96	T.id as thread_id,
97	CASE WHEN max(A.id) IS NOT NULL THEN
98	json_agg(json_strip_nulls(json_build_object(
99	'address', A.address,
100	'community_id', A.community_id,
101	'User', json_build_object(
102	'id', editor_profiles.id,
103	'profile', json_build_object(
104	'userId', editor_profiles.id,
105	'name', editor_profiles.profile->>'name',
106	'address', A.address,
107	'lastActive', A.last_active::text,
108	'avatarUrl', editor_profiles.profile->>'avatar_url'
109	),
110	'tier', editor_profiles.tier
111	)
112	)))
113	ELSE '[]'::json
114	END AS collaborators
115	FROM
116	T
117	LEFT JOIN "Collaborations" AS C ON T.id = C.thread_id
118	LEFT JOIN "Addresses" A ON C.address_id = A.id
119	LEFT JOIN "Users" editor_profiles ON A.user_id = editor_profiles.id
120	GROUP BY
121	T.id
122	)
123	${
124	withXRecentComments	×
125	? `, recent_comments AS ( -- get the recent comments data associated with the thread
126	SELECT
127	T.id as thread_id,
128	json_agg(json_strip_nulls(json_build_object(
129	'id', COM.id,
130	'address', A.address,
131	'body', COM.body,
132	'created_at', COM.created_at::text,
133	'updated_at', COM.updated_at::text,
134	'deleted_at', COM.deleted_at::text,
135	'marked_as_spam_at', COM.marked_as_spam_at::text,
136	'discord_meta', COM.discord_meta,
137	'profile_name', U.profile->>'name',
138	'profile_avatar', U.profile->>'avatar_url',
139	'user_id', U.id,
140	'user_tier', U.tier,
141	'content_url', COM.content_url
142	))) as "recentComments"
143	FROM (
144	Select tempC.* FROM "Comments" tempC
145	JOIN T tempTT ON tempTT.id = tempC.thread_id
146	WHERE deleted_at IS NULL
147	ORDER BY created_at DESC
148	LIMIT :withXRecentComments
149	) COM
150	JOIN T ON T.id = COM.thread_id
151	JOIN "Addresses" A ON A.id = COM.address_id
152	JOIN "Users" U ON U.id = A.user_id
153	GROUP BY T.id
154	)`
155	: ''
156	}
157	SELECT
158	T., CD.${withXRecentComments ? `, RC.*` : ''}	×
159	FROM
160	T
161	LEFT JOIN collaborator_data CD ON T.id = CD.thread_id
162	${withXRecentComments ? `LEFT JOIN recent_comments RC ON T.id = RC.thread_id;` : ''}	×
163	`;
164
NEW 165	return await models.sequelize.query<z.infer<typeof schemas.ThreadView>>(	×
166	sql,
167	{
168	replacements: {
169	community_id,
170	address_id,
171	threads_per_topic: threads_per_topic \|\| 3,	×
172	withXRecentComments,
173	},
174	type: QueryTypes.SELECT,
175	},
176	);
177	},
178	};
179	}

hicommonwealth / commonwealth / 15443233707

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