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/SearchThreads.query.ts

import { Query } from '@hicommonwealth/core';
import * as schemas from '@hicommonwealth/schemas';
import { ALL_COMMUNITIES } from '@hicommonwealth/shared';
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 SearchThreads(): Query<typeof schemas.SearchThreads> {
  return {
    ...schemas.SearchThreads,
    auth: [authOptional],
    secure: true,
    body: async ({ context, payload }) => {
      const {
        community_id,
        search_term,
        thread_title_only,
        limit,
        cursor,
        order_by,
        order_direction,
        include_count,
      } = payload;
      const address_id = context?.address?.id;

      const replacements = {
        address_id,
        community_id:
          community_id && community_id !== ALL_COMMUNITIES
            ? community_id
            : undefined,
        search_term,
        limit,
        offset: limit * (cursor - 1),
      };

      const sql = `
${withGates(address_id)}
SELECT 
  'thread' as type,
  T.community_id,
  T.id,
  T.title,
  ${thread_title_only ? `''` : `T.body`} as body,
  T.kind,
  T.created_at,
  T.address_id,
  JSONB_BUILD_OBJECT(
    'id', A.id,
    'user_id', A.user_id,
    'address', A.address
  ) as Address,
  A.last_active as address_last_active,
  U.id as user_id,
  U.tier as user_tier,
  U.profile->>'avatar_url' as avatar_url,
  U.profile->>'name' as profile_name,
  COUNT(*) OVER()::INTEGER AS total_count, 
  ts_rank_cd(T.search, tsquery) as rank
FROM 
    "Threads" T
    JOIN "Addresses" A ON T.address_id = A.id
    JOIN "Users" U ON A.user_id = U.id
    ${joinGates(address_id)}
    , websearch_to_tsquery('english', :search_term) as tsquery
WHERE
  T.deleted_at IS NULL
  AND T.marked_as_spam_at IS NULL
  ${replacements.community_id ? 'AND T.community_id = :community_id' : ''} 
  ${filterGates(address_id)}
  AND (T.title ILIKE '%' || :search_term || '%' ${!thread_title_only ? 'OR tsquery @@ T.search' : ''})
ORDER BY
  ${order_by === 'created_at' ? `T.${order_by} ${order_direction || 'DESC'}` : `rank, T.created_at DESC`}
LIMIT :limit OFFSET :offset
`;

      const results = await models.sequelize.query<
        z.infer<typeof schemas.ThreadView> & { total_count: number }
      >(sql, {
        type: QueryTypes.SELECT,
        replacements,
        raw: true,
        logging: true,
      });

      const totalResults = include_count
        ? results.length > 0
          ? results[0].total_count
          : 0
        : results.length;

      return schemas.buildPaginatedResponse(results, totalResults, {
        cursor,
        limit,
      });
    },
  };
}

1	import { Query } from '@hicommonwealth/core';
2	import * as schemas from '@hicommonwealth/schemas';
3	import { ALL_COMMUNITIES } from '@hicommonwealth/shared';
4	import { QueryTypes } from 'sequelize';
5	import { z } from 'zod';
6	import { models } from '../../database';
7	import { authOptional } from '../../middleware';
8	import { filterGates, joinGates, withGates } from '../../utils/gating';
9
10	export function SearchThreads(): Query<typeof schemas.SearchThreads> {
11	return {	×
12	...schemas.SearchThreads,
13	auth: [authOptional],
14	secure: true,
15	body: async ({ context, payload }) => {
16	const {
17	community_id,
18	search_term,
19	thread_title_only,
20	limit,
21	cursor,
22	order_by,
23	order_direction,
24	include_count,
UNCOV 25	} = payload;	×
NEW 26	const address_id = context?.address?.id;	×
27
NEW 28	const replacements = {	×
29	address_id,
30	community_id:
31	community_id && community_id !== ALL_COMMUNITIES	×
32	? community_id
33	: undefined,
34	search_term,
35	limit,
36	offset: limit * (cursor - 1),
37	};
38
39	const sql = `	×
40	${withGates(address_id)}
41	SELECT
42	'thread' as type,
43	T.community_id,
44	T.id,
45	T.title,
46	${thread_title_only ? `''` : `T.body`} as body,	×
47	T.kind,
48	T.created_at,
49	T.address_id,
50	JSONB_BUILD_OBJECT(
51	'id', A.id,
52	'user_id', A.user_id,
53	'address', A.address
54	) as Address,
55	A.last_active as address_last_active,
56	U.id as user_id,
57	U.tier as user_tier,
58	U.profile->>'avatar_url' as avatar_url,
59	U.profile->>'name' as profile_name,
60	COUNT(*) OVER()::INTEGER AS total_count,
61	ts_rank_cd(T.search, tsquery) as rank
62	FROM
63	"Threads" T
64	JOIN "Addresses" A ON T.address_id = A.id
65	JOIN "Users" U ON A.user_id = U.id
66	${joinGates(address_id)}
67	, websearch_to_tsquery('english', :search_term) as tsquery
68	WHERE
69	T.deleted_at IS NULL
70	AND T.marked_as_spam_at IS NULL
71	${replacements.community_id ? 'AND T.community_id = :community_id' : ''}	×
72	${filterGates(address_id)}
73	AND (T.title ILIKE '%' \|\| :search_term \|\| '%' ${!thread_title_only ? 'OR tsquery @@ T.search' : ''})	×
74	ORDER BY
75	${order_by === 'created_at' ? `T.${order_by} ${order_direction \|\| 'DESC'}` : `rank, T.created_at DESC`}	×
76	LIMIT :limit OFFSET :offset
77	`;
78
79	const results = await models.sequelize.query<	×
80	z.infer<typeof schemas.ThreadView> & { total_count: number }
81	>(sql, {
82	type: QueryTypes.SELECT,
83	replacements,
84	raw: true,
85	logging: true,
86	});
87
NEW 88	const totalResults = include_count	×
89	? results.length > 0	×
90	? results[0].total_count
91	: 0
92	: results.length;
93
NEW 94	return schemas.buildPaginatedResponse(results, totalResults, {	×
95	cursor,
96	limit,
97	});
98	},
99	};
100	}

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