Skip to content

📈 Open source, privacy-first web analytics — a cookie-less Google Analytics alternative with built-in error tracking and performance monitoring. Self-host it or use our cloud. Get started for free!

2.7 KB
Raw Blame
import { UnprocessableEntityException } from '@nestjs/common'

import { getCaptchaColumnExpression } from '../../analytics.service'
import {
  captchaExtraWhere,
  V2DataType,
  V2DimensionDef,
  V2MetricCtx,
  V2MetricDef,
} from '../registry'

export interface BreakdownSort {
  field: string
  direction: 'asc' | 'desc'
}

export interface BreakdownQueryOptions {
  dataType: V2DataType
  dimension: V2DimensionDef
  metrics: V2MetricDef[]
  subQuery: string
  ctx: V2MetricCtx
  sort: BreakdownSort
}

export const parseSortParam = (
  sort: string | undefined,
  dimension: V2DimensionDef,
  metrics: V2MetricDef[],
): BreakdownSort => {
  if (!sort) {
    return { field: metrics[0].api, direction: 'desc' }
  }

  const [field, direction = 'desc'] = sort.split(':')

  if (direction !== 'asc' && direction !== 'desc') {
    throw new UnprocessableEntityException(
      `Invalid sort direction '${direction}'; use 'asc' or 'desc'`,
    )
  }

  const sortable = ['value', ...metrics.map((metric) => metric.api)]

  if (!sortable.includes(field)) {
    throw new UnprocessableEntityException(
      `Cannot sort '${dimension.api}' breakdown by '${field}'. Sortable fields: ${sortable.join(', ')}`,
    )
  }

  return { field, direction }
}

export const buildBreakdownQuery = (opts: BreakdownQueryOptions): string => {
  const { dataType, dimension, metrics, subQuery, ctx, sort } = opts

  const dimensionSql =
    dataType === 'captcha'
      ? getCaptchaColumnExpression(dimension.column)
      : dimension.column

  const dimensionCols = [
    `${dimensionSql} AS value`,
    ...(dimension.extraFields ?? []).map(
      (field) => `${field.column} AS ${field.api}`,
    ),
  ]

  const metricCols = metrics.map((metric) => {
    const expr = metric.sqlExpr(ctx)

    if (!expr) {
      throw new UnprocessableEntityException(
        `The '${metric.api}' metric is not supported on breakdowns. Request it on the timeseries or summary endpoint instead`,
      )
    }

    return `${expr} AS ${metric.api}`
  })

  const groupBy = ['value', ...(dimension.extraFields ?? []).map((f) => f.api)]

  const whereParts: string[] = []

  if (dimension.excludeNull) {
    whereParts.push(`AND ${dimension.column} IS NOT NULL`)
  }

  if (dataType === 'captcha') {
    const guard = captchaExtraWhere(dimension)
    if (guard) {
      whereParts.push(guard)
    }
  }

  return `
    SELECT
      ${[...dimensionCols, ...metricCols].join(',\n      ')},
      count() OVER () AS __total
    ${subQuery}
    ${whereParts.join(' ')}
    GROUP BY ${groupBy.join(', ')}
    ORDER BY ${sort.field} ${sort.direction}${dimension.api === 'outbound_link' && sort.field !== 'value' ? ', value ASC' : ''}
    LIMIT {v2_limit:UInt32} OFFSET {v2_offset:UInt32}
  `
}