Function findProfilesCore

packages/db/src/services/profile.service.ts:390–471

(
  input: FindProfilesInput
)

}

export function findProfilesCore(
  input: FindProfilesInput
): Promise<IClickhouseProfile[]> {
  const pid = sqlstring.escape(input.projectId);
  const conditions: string[] = [`project_id = ${pid}`];

  if (input.email) {
    conditions.push(`email ILIKE ${sqlstring.escape(`%${input.email}%`)}`);
  }
  if (input.name) {
    const nameClause = profileSearchSql(input.name);
    if (nameClause) conditions.push(nameClause);
  }
  if (input.country) {
    conditions.push(
      `properties['country'] = ${sqlstring.escape(input.country)}`
    );
  }
  if (input.city) {
    conditions.push(`properties['city'] = ${sqlstring.escape(input.city)}`);
  }
  if (input.device) {
    conditions.push(`properties['device'] = ${sqlstring.escape(input.device)}`);
  }
  if (input.browser) {
    conditions.push(
      `properties['browser'] = ${sqlstring.escape(input.browser)}`
    );
  }

  if (input.inactiveDays !== undefined) {
    const days = Math.floor(input.inactiveDays);
    conditions.push(`id NOT IN (
      SELECT DISTINCT profile_id FROM ${TABLE_NAMES.events}
      WHERE project_id = ${pid}
        AND profile_id != ''
        AND created_at >= now() - INTERVAL ${days} DAY
    )`);
  }

  if (input.minSessions !== undefined) {
    const min = Math.floor(input.minSessions);
    conditions.push(`id IN (
      SELECT profile_id FROM ${TABLE_NAMES.sessions}
      WHERE project_id = ${pid}
        AND sign = 1
        AND profile_id != ''
      GROUP BY profile_id
      HAVING count() >= ${min}
    )`);
  }

  if (input.performedEvent) {
    conditions.push(`id IN (
      SELECT DISTINCT profile_id FROM ${TABLE_NAMES.events}
      WHERE project_id = ${pid}
        AND name = ${sqlstring.escape(input.performedEvent)}
    )`);