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

11.11

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

import { type Query } from '@hicommonwealth/core';
import * as schemas from '@hicommonwealth/schemas';
import { QueryTypes } from 'sequelize';
import { z } from 'zod';
import { models } from '../database';

const sql = `
WITH 
referrer AS (
  SELECT DISTINCT address FROM "Addresses"
  WHERE user_id = :user_id AND address LIKE '0x%'),
referee AS (
  SELECT DISTINCT A.address, A.user_id, U.referred_by_address
  FROM "Users" U
    JOIN referrer ON U.referred_by_address = referrer.address
    JOIN "Addresses" A ON A.user_id = U.id
)
SELECT 
  referee.referred_by_address as referrer_address,
  referee.address as referee_address,
  referee.user_id as referee_user_id,
  U.profile as referee_profile,
  -- when referee creates a community
  C.id as community_id,
  C.name as community_name,
  C.icon_url as community_icon_url,
  referrals.namespace_address,
  COALESCE(referrals.referrer_received_eth_amount, '0') as referrer_received_eth_amount
FROM referee 
  JOIN "Users" U ON U.id = referee.user_id
  LEFT JOIN "Referrals" referrals
    ON referrals.referrer_address = referee.referred_by_address
    AND referrals.referee_address = referee.address
  LEFT JOIN "Communities" C ON C.namespace_address = referrals.namespace_address
  ;
`;

export function GetUserReferrals(): Query<typeof schemas.GetUserReferrals> {
  return {
    ...schemas.GetUserReferrals,
    auth: [],
    secure: true,
    body: async ({ actor, payload }) => {
      // only super admin can get referrals for all users
      const id =
        actor.user.isAdmin && payload.user_id ? payload.user_id : actor.user.id;
      const result = await models.sequelize.query(sql, {
        type: QueryTypes.SELECT,
        raw: true,
        replacements: { user_id: id },
      });
      return result as Array<z.infer<typeof schemas.ReferralView>>;
    },
  };
}

1	import { type 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
7	const sql = `	30✔
8	WITH
9	referrer AS (
10	SELECT DISTINCT address FROM "Addresses"
11	WHERE user_id = :user_id AND address LIKE '0x%'),
12	referee AS (
13	SELECT DISTINCT A.address, A.user_id, U.referred_by_address
14	FROM "Users" U
15	JOIN referrer ON U.referred_by_address = referrer.address
16	JOIN "Addresses" A ON A.user_id = U.id
17	)
18	SELECT
19	referee.referred_by_address as referrer_address,
20	referee.address as referee_address,
21	referee.user_id as referee_user_id,
22	U.profile as referee_profile,
23	-- when referee creates a community
24	C.id as community_id,
25	C.name as community_name,
26	C.icon_url as community_icon_url,
27	referrals.namespace_address,
28	COALESCE(referrals.referrer_received_eth_amount, '0') as referrer_received_eth_amount
29	FROM referee
30	JOIN "Users" U ON U.id = referee.user_id
31	LEFT JOIN "Referrals" referrals
32	ON referrals.referrer_address = referee.referred_by_address
33	AND referrals.referee_address = referee.address
34	LEFT JOIN "Communities" C ON C.namespace_address = referrals.namespace_address
35	;
36	`;
37
38	export function GetUserReferrals(): Query<typeof schemas.GetUserReferrals> {
UNCOV 39	return {	×
40	...schemas.GetUserReferrals,
41	auth: [],
42	secure: true,
43	body: async ({ actor, payload }) => {
44	// only super admin can get referrals for all users
45	const id =
46	actor.user.isAdmin && payload.user_id ? payload.user_id : actor.user.id;	×
UNCOV 47	const result = await models.sequelize.query(sql, {	×
48	type: QueryTypes.SELECT,
49	raw: true,
50	replacements: { user_id: id },
51	});
UNCOV 52	return result as Array<z.infer<typeof schemas.ReferralView>>;	×
53	},
54	};
55	}

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