13665553594

Committed 05 Mar 2025 12:16AM UTC coverage: 45.613% (-0.1%) from 45.755%

Build # 13665553594

Build Type

Pull #11244

github

Committed by

web-flow

Commit Message

Merge 7e1eb8f80 into f0ab4fbf1

Pull Request Pull Request #11244: Query optimizations

Run Details

1310 of 3187 branches covered (41.1%)

Branch coverage included in aggregate %.

4 of 18 new or added lines in 3 files covered. (22.22%)

3 existing lines in 1 file now uncovered.

2506 of 5179 relevant lines covered (48.39%)

38.05 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
              ${communityFilter || nameFilter ? '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	${communityFilter \|\| nameFilter ? 'WHERE' : ''}	×
63	${communityFilter} ${communityFilter && nameFilter ? ' AND ' : ''}	×
64	${nameFilter}
65	GROUP BY U.id
66	ORDER BY ${order_col} ${direction}
67	LIMIT :limit OFFSET :offset
68	`;
69
70	const profiles = await models.sequelize.query<	×
71	z.infer<typeof schemas.SearchUserProfilesView> & { total: number }
72	>(sql, {
73	replacements: {
74	searchTerm: exact_match ? search : `${search}%`,	×
75	community_id,
76	limit,
77	offset,
78	},
79	type: QueryTypes.SELECT,
80	});
81
82	return schemas.buildPaginatedResponse(	×
83	profiles,
84	profiles.at(0)?.total ?? 0,	×
85	{ limit, offset },
86	);
87	},
88	};
89	}

hicommonwealth / commonwealth / 13665553594

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