13661652742

Committed 04 Mar 2025 07:44PM UTC coverage: 45.679% (-0.08%) from 45.755%

Build # 13661652742

Build Type

Pull #11244

github

Committed by

web-flow

Commit Message

Merge afd3fee3d into f0ae57e07

Pull Request Pull Request #11244: Query optimizations

Coverage Stats

1310 of 3179 branches covered (41.21%)

Branch coverage included in aggregate %.

4 of 7 new or added lines in 2 files covered. (57.14%)

9 existing lines in 2 files now uncovered.

2506 of 5175 relevant lines covered (48.43%)

38.08 hits per line

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

0.0

/libs/model/src/user/SearchUserProfiles.query.ts

import { type 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';

export function SearchUserProfiles(): Query<typeof schemas.SearchUserProfiles> {
  return {
    ...schemas.SearchUserProfiles,
    auth: [],
    secure: true,
    body: async ({ payload }) => {
      const {
        community_id,
        search,
        limit,
        cursor,
        order_by,
        order_direction,
        exact_match,
      } = payload;

      const communityFilter =
        community_id && community_id !== ALL_COMMUNITIES
          ? `A.community_id = :community_id`
          : '';
      const nameFilter =
        search.length > 0
          ? exact_match
            ? `U.profile->>'name' = :searchTerm`
            : `(U.profile->>'name' ILIKE :searchTerm)`
          : '';

      // pagination configuration
      const direction = order_direction || 'DESC';
      const order_col =
        order_by === 'created_at'
          ? 'U.created_at'
          : order_by === 'profile_name'
            ? `U.profile->>'name'`
            : 'last_active';
      const offset = limit! * (cursor! - 1);

      const sql = `
          SELECT U.id                         AS user_id,
                 U.profile ->> 'name'         AS profile_name,
                 U.profile ->> 'avatar_url'   AS avatar_url,
                 U.created_at,
                 MAX(A.last_active)           as last_active,
                 array_agg(
                         json_build_object(
                                 'id', A.id,
                                 'community_id', A.community_id,
                                 'address', A.address,
                                 'role', A.role
                         )
                 )                            as addresses,
                 COUNT(U.id) OVER ()::integer as total
          FROM "Users" U
                   JOIN "Addresses" A ON U.id = A.user_id
          WHERE ${communityFilter} ${communityFilter && nameFilter ? ' AND ' : ''}
              ${nameFilter}
          GROUP BY U.id
          ORDER BY ${order_col} ${direction}
          LIMIT :limit OFFSET :offset
      `;

      const profiles = await models.sequelize.query<
        z.infer<typeof schemas.SearchUserProfilesView> & { total: number }
      >(sql, {
        replacements: {
          searchTerm: exact_match ? search : `${search}%`,
          community_id,
          limit,
          offset,
        },
        type: QueryTypes.SELECT,
      });

      return schemas.buildPaginatedResponse(
        profiles,
        profiles.at(0)?.total ?? 0,
        { limit, offset },
      );
    },
  };
}

1	import { type 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
8	export function SearchUserProfiles(): Query<typeof schemas.SearchUserProfiles> {
9	return {	×
10	...schemas.SearchUserProfiles,
11	auth: [],
12	secure: true,
13	body: async ({ payload }) => {
14	const {
15	community_id,
16	search,
17	limit,
18	cursor,
19	order_by,
20	order_direction,
21	exact_match,
NEW 22	} = payload;	×
23
24	const communityFilter =
NEW 25	community_id && community_id !== ALL_COMMUNITIES	×
26	? `A.community_id = :community_id`
27	: '';
28	const nameFilter =
NEW 29	search.length > 0	×
30	? exact_match	×
31	? `U.profile->>'name' = :searchTerm`
32	: `(U.profile->>'name' ILIKE :searchTerm)`
33	: '';
34
35	// pagination configuration
36	const direction = order_direction \|\| 'DESC';	×
37	const order_col =
38	order_by === 'created_at'	×
39	? 'U.created_at'
40	: order_by === 'profile_name'	×
41	? `U.profile->>'name'`
42	: 'last_active';
43	const offset = limit! * (cursor! - 1);	×
44
45	const sql = `	×
46	SELECT U.id AS user_id,
47	U.profile ->> 'name' AS profile_name,
48	U.profile ->> 'avatar_url' AS avatar_url,
49	U.created_at,
50	MAX(A.last_active) as last_active,
51	array_agg(
52	json_build_object(
53	'id', A.id,
54	'community_id', A.community_id,
55	'address', A.address,
56	'role', A.role
57	)
58	) as addresses,
59	COUNT(U.id) OVER ()::integer as total
60	FROM "Users" U
61	JOIN "Addresses" A ON U.id = A.user_id
62	WHERE ${communityFilter} ${communityFilter && nameFilter ? ' AND ' : ''}	×
63	${nameFilter}
64	GROUP BY U.id
65	ORDER BY ${order_col} ${direction}
66	LIMIT :limit OFFSET :offset
67	`;
68
UNCOV 69	const profiles = await models.sequelize.query<	×
70	z.infer<typeof schemas.SearchUserProfilesView> & { total: number }
71	>(sql, {
72	replacements: {
73	searchTerm: exact_match ? search : `${search}%`,	×
74	community_id,
75	limit,
76	offset,
77	},
78	type: QueryTypes.SELECT,
79	});
80
UNCOV 81	return schemas.buildPaginatedResponse(	×
82	profiles,
83	profiles.at(0)?.total ?? 0,	×
84	{ limit, offset },
85	);
86	},
87	};
88	}

hicommonwealth / commonwealth / 13661652742

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