8478979078

Committed 29 Mar 2024 08:32AM CUT coverage: 82.643% (+61.0%) from 21.636%

Build # 8478979078

Build Type

push

github

Committed by

web-flow

Commit Message

feat: search api (#507)

* feat: search api

* test: add advanced test case

* feat: search responsive ui

* feat: realtime search

Run Details

3942 of 4135 branches covered (95.33%)

454 of 493 new or added lines in 29 files covered. (92.09%)

26536 of 32109 relevant lines covered (82.64%)

1222.65 hits per line

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

76.55

/apps/nestjs-backend/src/features/aggregation/aggregation.service.ts

import { HttpException, HttpStatus, Injectable, Logger } from '@nestjs/common';
import type { IGridColumnMeta, IFilter } from '@teable/core';
import {
  DbFieldType,
  mergeWithDefaultFilter,
  nullsToUndefined,
  parseGroup,
  StatisticsFunc,
  ViewType,
} from '@teable/core';
import type { Prisma } from '@teable/db-main-prisma';
import { PrismaService } from '@teable/db-main-prisma';
import { GroupPointType } from '@teable/openapi';
import type {
  IAggregationField,
  IGetRecordsRo,
  IQueryBaseRo,
  IRawAggregations,
  IRawAggregationValue,
  IRawRowCountValue,
  IGroupPoint,
  IGroupPointsRo,
} from '@teable/openapi';
import dayjs from 'dayjs';
import { Knex } from 'knex';
import { groupBy, isDate, isEmpty } from 'lodash';
import { InjectModel } from 'nest-knexjs';
import { ClsService } from 'nestjs-cls';
import { IThresholdConfig, ThresholdConfig } from '../../configs/threshold.config';
import { InjectDbProvider } from '../../db-provider/db.provider';
import { IDbProvider } from '../../db-provider/db.provider.interface';
import type { IClsStore } from '../../types/cls';
import { string2Hash } from '../../utils';
import { Timing } from '../../utils/timing';
import type { IFieldInstance } from '../field/model/factory';
import { createFieldInstanceByRaw } from '../field/model/factory';
import { RecordService } from '../record/record.service';

export type IWithView = {
  viewId?: string;
  customFilter?: IFilter;
  customFieldStats?: ICustomFieldStats[];
};

type ICustomFieldStats = {
  fieldId: string;
  statisticFunc?: StatisticsFunc;
};

type IStatisticsData = {
  viewId?: string;
  filter?: IFilter;
  statisticFields?: IAggregationField[];
};

@Injectable()
export class AggregationService {
  private logger = new Logger(AggregationService.name);

  constructor(
    private readonly recordService: RecordService,
    private readonly prisma: PrismaService,
    @InjectModel('CUSTOM_KNEX') private readonly knex: Knex,
    @InjectDbProvider() private readonly dbProvider: IDbProvider,
    private readonly cls: ClsService<IClsStore>,
    @ThresholdConfig() private readonly thresholdConfig: IThresholdConfig
  ) {}

  async performAggregation(params: {
    tableId: string;
    withFieldIds?: string[];
    withView?: IWithView;
    search?: [string, string];
  }): Promise<IRawAggregationValue> {
    const { tableId, withFieldIds, withView, search } = params;
    // Retrieve the current user's ID to build user-related query conditions
    const currentUserId = this.cls.get('user.id');

    const { statisticsData, fieldInstanceMap } = await this.fetchStatisticsParams({
      tableId,
      withView,
      withFieldIds,
    });

    const dbTableName = await this.getDbTableName(this.prisma, tableId);

    const { filter, statisticFields } = statisticsData;

    const rawAggregationData = await this.handleAggregation({
      dbTableName,
      fieldInstanceMap,
      filter,
      search,
      statisticFields,
      withUserId: currentUserId,
    });

    const aggregationResult = rawAggregationData && rawAggregationData[0];

    const aggregations: IRawAggregations = [];
    if (aggregationResult) {
      for (const [key, value] of Object.entries(aggregationResult)) {
        const [fieldId, aggFunc] = key.split('_') as [string, StatisticsFunc | undefined];

        const convertValue = this.formatConvertValue(value, aggFunc);

        if (fieldId) {
          aggregations.push({
            fieldId,
            total: aggFunc ? { value: convertValue, aggFunc: aggFunc } : null,
          });
        }
      }
    }
    return { aggregations };
  }

  async performRowCount(tableId: string, queryRo: IQueryBaseRo): Promise<IRawRowCountValue> {
    const { filterLinkCellCandidate, filterLinkCellSelected } = queryRo;
    // Retrieve the current user's ID to build user-related query conditions
    const currentUserId = this.cls.get('user.id');

    const { statisticsData, fieldInstanceMap } = await this.fetchStatisticsParams({
      tableId,
      withView: {
        viewId: queryRo.viewId,
        customFilter: queryRo.filter,
      },
    });

    const dbTableName = await this.getDbTableName(this.prisma, tableId);

    const { filter } = statisticsData;

    if (filterLinkCellSelected) {
      // TODO: use a new method to retrieve only count
      const { ids } = await this.recordService.getLinkSelectedRecordIds(filterLinkCellSelected);
      return { rowCount: ids.length };
    }

    const rawRowCountData = await this.handleRowCount({
      tableId,
      dbTableName,
      fieldInstanceMap,
      filter,
      filterLinkCellCandidate,
      search: queryRo.search,
      withUserId: currentUserId,
    });

    return {
      rowCount: Number(rawRowCountData[0]?.count ?? 0),
    };
  }

  private async fetchStatisticsParams(params: {
    tableId: string;
    withView?: IWithView;
    withFieldIds?: string[];
  }): Promise<{
    statisticsData: IStatisticsData;
    fieldInstanceMap: Record<string, IFieldInstance>;
  }> {
    const { tableId, withView, withFieldIds } = params;

    const viewRaw = await this.findView(tableId, withView);

    const { fieldInstances, fieldInstanceMap } = await this.getFieldsData(tableId);
    const filteredFieldInstances = this.filterFieldInstances(
      fieldInstances,
      withView,
      withFieldIds
    );

    const statisticsData = this.buildStatisticsData(filteredFieldInstances, viewRaw, withView);
    return { statisticsData, fieldInstanceMap };
  }

  private async findView(tableId: string, withView?: IWithView) {
    if (!withView?.viewId) {
      return undefined;
    }

    return nullsToUndefined(
      await this.prisma.view.findFirst({
        select: { id: true, columnMeta: true, filter: true, group: true },
        where: {
          tableId,
          ...(withView?.viewId ? { id: withView.viewId } : {}),
          type: { in: [ViewType.Grid, ViewType.Gantt] },
          deletedTime: null,
        },
      })
    );
  }

  private filterFieldInstances(
    fieldInstances: IFieldInstance[],
    withView?: IWithView,
    withFieldIds?: string[]
  ) {
    const targetFieldIds =
      withView?.customFieldStats?.map((field) => field.fieldId) ?? withFieldIds;

    return targetFieldIds?.length
      ? fieldInstances.filter((instance) => targetFieldIds.includes(instance.id))
      : fieldInstances;
  }

  private buildStatisticsData(
    filteredFieldInstances: IFieldInstance[],
    viewRaw:
      | {
          id: string | undefined;
          columnMeta: string | undefined;
          filter: string | undefined;
          group: string | undefined;
        }
      | undefined,
    withView?: IWithView
  ) {
    let statisticsData: IStatisticsData = {
      viewId: viewRaw?.id,
    };

    if (viewRaw?.filter || withView?.customFilter) {
      const filter = mergeWithDefaultFilter(viewRaw?.filter, withView?.customFilter);
      statisticsData = { ...statisticsData, filter };
    }

    if (viewRaw?.id || withView?.customFieldStats) {
      const statisticFields = this.getStatisticFields(
        filteredFieldInstances,
        viewRaw?.columnMeta && JSON.parse(viewRaw.columnMeta),
        withView?.customFieldStats
      );
      statisticsData = { ...statisticsData, statisticFields };
    }
    return statisticsData;
  }

  async getFieldsData(tableId: string, fieldIds?: string[]) {
    const fieldsRaw = await this.prisma.field.findMany({
      where: { tableId, ...(fieldIds ? { id: { in: fieldIds } } : {}), deletedTime: null },
    });

    const fieldInstances = fieldsRaw.map((field) => createFieldInstanceByRaw(field));
    const fieldInstanceMap = fieldInstances.reduce(
      (map, field) => {
        map[field.id] = field;
        map[field.name] = field;
        return map;
      },
      {} as Record<string, IFieldInstance>
    );
    return { fieldInstances, fieldInstanceMap };
  }

  private getStatisticFields(
    fieldInstances: IFieldInstance[],
    columnMeta?: IGridColumnMeta,
    customFieldStats?: ICustomFieldStats[]
  ) {
    let calculatedStatisticFields: IAggregationField[] | undefined;
    const customFieldStatsGrouped = groupBy(customFieldStats, 'fieldId');

    fieldInstances.forEach((fieldInstance) => {
      const { id: fieldId } = fieldInstance;
      const viewColumnMeta = columnMeta ? columnMeta[fieldId] : undefined;
      const customFieldStats = customFieldStatsGrouped[fieldId];

      if (viewColumnMeta || customFieldStats) {
        const { hidden, statisticFunc } = viewColumnMeta || {};
        const statisticFuncList = customFieldStats
          ?.filter((item) => item.statisticFunc)
          ?.map((item) => item.statisticFunc) as StatisticsFunc[];

        const funcList = !isEmpty(statisticFuncList)
          ? statisticFuncList
          : statisticFunc && [statisticFunc];

        if (hidden !== true && funcList && funcList.length) {
          const statisticFieldList = funcList.map((item) => {
            return {
              fieldId,
              statisticFunc: item,
            };
          });
          (calculatedStatisticFields = calculatedStatisticFields ?? []).push(...statisticFieldList);
        }
      }
    });
    return calculatedStatisticFields;
  }

  private handleAggregation(params: {
    dbTableName: string;
    fieldInstanceMap: Record<string, IFieldInstance>;
    filter?: IFilter;
    search?: [string, string];
    statisticFields?: IAggregationField[];
    withUserId?: string;
  }) {
    const { dbTableName, fieldInstanceMap, filter, search, statisticFields, withUserId } = params;
    if (!statisticFields?.length) {
      return;
    }

    const tableAlias = 'main_table';
    const queryBuilder = this.knex
      .with(tableAlias, (qb) => {
        qb.select('*').from(dbTableName);
        if (filter) {
          this.dbProvider
            .filterQuery(qb, fieldInstanceMap, filter, { withUserId })
            .appendQueryBuilder();
        }
        if (search) {
          this.dbProvider.searchQuery(qb, fieldInstanceMap, search);
        }
      })
      .from(tableAlias);

    const aggSql = this.dbProvider
      .aggregationQuery(queryBuilder, tableAlias, fieldInstanceMap, statisticFields)
      .toQuerySql();
    return this.prisma.$queryRawUnsafe<{ [field: string]: unknown }[]>(aggSql);
  }

  private async handleRowCount(params: {
    tableId: string;
    dbTableName: string;
    fieldInstanceMap: Record<string, IFieldInstance>;
    filter?: IFilter;
    filterLinkCellCandidate?: IGetRecordsRo['filterLinkCellCandidate'];
    search?: [string, string];
    withUserId?: string;
  }) {
    const {
      tableId,
      dbTableName,
      fieldInstanceMap,
      filter,
      filterLinkCellCandidate,
      search,
      withUserId,
    } = params;

    const queryBuilder = this.knex(dbTableName);

    if (filter) {
      this.dbProvider
        .filterQuery(queryBuilder, fieldInstanceMap, filter, { withUserId })
        .appendQueryBuilder();
    }

    if (search) {
      this.dbProvider.searchQuery(queryBuilder, fieldInstanceMap, search);
    }

    if (filterLinkCellCandidate) {
      await this.recordService.buildLinkCandidateQuery(
        queryBuilder,
        tableId,
        filterLinkCellCandidate
      );
    }

    return this.getRowCount(this.prisma, queryBuilder);
  }

  private convertValueToNumberOrString(currentValue: unknown): number | string | null {
    if (typeof currentValue === 'bigint' || typeof currentValue === 'number') {
      return Number(currentValue);
    }
    if (isDate(currentValue)) {
      return currentValue.toISOString();
    }
    return currentValue?.toString() ?? null;
  }

  private calculateDateRangeOfMonths(currentValue: string): number {
    const [maxTime, minTime] = currentValue.split(',');
    return maxTime && minTime ? dayjs(maxTime).diff(minTime, 'month') : 0;
  }

  private formatConvertValue = (currentValue: unknown, aggFunc?: StatisticsFunc) => {
    let convertValue = this.convertValueToNumberOrString(currentValue);

    if (!aggFunc) {
      return convertValue;
    }

    if (aggFunc === StatisticsFunc.DateRangeOfMonths && typeof currentValue === 'string') {
      convertValue = this.calculateDateRangeOfMonths(currentValue);
    }

    const defaultToZero = [
      StatisticsFunc.PercentEmpty,
      StatisticsFunc.PercentFilled,
      StatisticsFunc.PercentUnique,
      StatisticsFunc.PercentChecked,
      StatisticsFunc.PercentUnChecked,
    ];

    if (defaultToZero.includes(aggFunc)) {
      convertValue = convertValue ?? 0;
    }
    return convertValue;
  };

  private async getDbTableName(prisma: Prisma.TransactionClient, tableId: string) {
    const tableMeta = await prisma.tableMeta.findUniqueOrThrow({
      where: { id: tableId },
      select: { dbTableName: true },
    });
    return tableMeta.dbTableName;
  }

  private async getRowCount(prisma: Prisma.TransactionClient, queryBuilder: Knex.QueryBuilder) {
    queryBuilder
      .clearSelect()
      .clearCounters()
      .clearGroup()
      .clearHaving()
      .clearOrder()
      .clear('limit')
      .clear('offset');
    const rowCountSql = queryBuilder.count({ count: '*' });

    return prisma.$queryRawUnsafe<{ count?: number }[]>(rowCountSql.toQuery());
  }

  @Timing()
  private groupDbCollection2GroupPoints(
    groupResult: { [key: string]: unknown; __c: number }[],
    groupFields: IFieldInstance[]
  ) {
    const groupPoints: IGroupPoint[] = [];
    let fieldValues: unknown[] = [Symbol(), Symbol(), Symbol()];

    groupResult.forEach((item) => {
      const { __c: count } = item;

      groupFields.forEach((field, index) => {
        const { id, dbFieldName } = field;
        const fieldValue = this.convertValueToNumberOrString(item[dbFieldName]);

        if (fieldValues[index] === fieldValue) return;

        fieldValues[index] = fieldValue;
        fieldValues = fieldValues.map((value, idx) => (idx > index ? Symbol() : value));

        const flagString = `${id}_${fieldValues.slice(0, index + 1).join('_')}`;

        groupPoints.push({
          id: String(string2Hash(flagString)),
          type: GroupPointType.Header,
          depth: index,
          value: field.convertDBValue2CellValue(fieldValue),
        });
      });

      groupPoints.push({ type: GroupPointType.Row, count: Number(count) });
    });
    return groupPoints;
  }

  private async checkGroupingOverLimit(dbFieldNames: string[], queryBuilder: Knex.QueryBuilder) {
    queryBuilder.countDistinct(dbFieldNames);

    const distinctResult = await this.prisma.$queryRawUnsafe<{ count: number }[]>(
      queryBuilder.toQuery()
    );
    const distinctCount = Number(distinctResult[0].count);

    return distinctCount > this.thresholdConfig.maxGroupPoints;
  }

  public async getGroupPoints(tableId: string, query?: IGroupPointsRo) {
    const { viewId, groupBy: extraGroupBy, filter } = query || {};

    if (!viewId) return null;

    const groupBy = parseGroup(extraGroupBy);

    if (!groupBy?.length) return null;

    const viewRaw = await this.findView(tableId, { viewId });
    const { fieldInstanceMap } = await this.getFieldsData(tableId);
    const dbTableName = await this.getDbTableName(this.prisma, tableId);

    const filterStr = viewRaw?.filter;
    const mergedFilter = mergeWithDefaultFilter(filterStr, filter);
    const groupFieldIds = groupBy.map((item) => item.fieldId);

    const queryBuilder = this.knex(dbTableName);
    const distinctQueryBuilder = this.knex(dbTableName);

    if (mergedFilter) {
      const withUserId = this.cls.get('user.id');
      this.dbProvider
        .filterQuery(queryBuilder, fieldInstanceMap, mergedFilter, { withUserId })
        .appendQueryBuilder();
      this.dbProvider
        .filterQuery(distinctQueryBuilder, fieldInstanceMap, mergedFilter, { withUserId })
        .appendQueryBuilder();
    }

    const dbFieldNames = groupFieldIds.map((fieldId) => fieldInstanceMap[fieldId].dbFieldName);

    const isGroupingOverLimit = await this.checkGroupingOverLimit(
      dbFieldNames,
      distinctQueryBuilder
    );
    if (isGroupingOverLimit) {
      throw new HttpException(
        'Grouping results exceed limit, please adjust grouping conditions to reduce the number of groups.',
        HttpStatus.PAYLOAD_TOO_LARGE
      );
    }

    this.dbProvider.sortQuery(queryBuilder, fieldInstanceMap, groupBy).appendSortBuilder();

    queryBuilder.count({ __c: '*' });

    groupFieldIds.forEach((fieldId) => {
      const field = fieldInstanceMap[fieldId];

      if (!field) return;

      const { dbFieldType, dbFieldName } = field;
      const column =
        dbFieldType === DbFieldType.Json
          ? this.knex.raw(`CAST(?? as text)`, [dbFieldName]).toQuery()
          : this.knex.ref(dbFieldName).toQuery();

      queryBuilder.select(this.knex.raw(`${column}`)).groupBy(dbFieldName);
    });

    const groupSql = queryBuilder.toQuery();

    const result =
      await this.prisma.$queryRawUnsafe<{ [key: string]: unknown; __c: number }[]>(groupSql);

    const groupFields = groupFieldIds.map((fieldId) => fieldInstanceMap[fieldId]);

    return this.groupDbCollection2GroupPoints(result, groupFields);
  }
}

1	import { HttpException, HttpStatus, Injectable, Logger } from '@nestjs/common';	4✔
2	import type { IGridColumnMeta, IFilter } from '@teable/core';	4✔
3	import {	4✔
4	DbFieldType,	4✔
5	mergeWithDefaultFilter,	4✔
6	nullsToUndefined,	4✔
7	parseGroup,	4✔
8	StatisticsFunc,	4✔
9	ViewType,	4✔
10	} from '@teable/core';	4✔
11	import type { Prisma } from '@teable/db-main-prisma';	4✔
12	import { PrismaService } from '@teable/db-main-prisma';	4✔
13	import { GroupPointType } from '@teable/openapi';	4✔
14	import type {	4✔
15	IAggregationField,	4✔
16	IGetRecordsRo,	4✔
17	IQueryBaseRo,	4✔
18	IRawAggregations,	4✔
19	IRawAggregationValue,	4✔
20	IRawRowCountValue,	4✔
21	IGroupPoint,	4✔
22	IGroupPointsRo,	4✔
23	} from '@teable/openapi';	4✔
24	import dayjs from 'dayjs';	4✔
25	import { Knex } from 'knex';	4✔
26	import { groupBy, isDate, isEmpty } from 'lodash';	4✔
27	import { InjectModel } from 'nest-knexjs';	4✔
28	import { ClsService } from 'nestjs-cls';	4✔
29	import { IThresholdConfig, ThresholdConfig } from '../../configs/threshold.config';	4✔
30	import { InjectDbProvider } from '../../db-provider/db.provider';	4✔
31	import { IDbProvider } from '../../db-provider/db.provider.interface';	4✔
32	import type { IClsStore } from '../../types/cls';	4✔
33	import { string2Hash } from '../../utils';	4✔
34	import { Timing } from '../../utils/timing';	4✔
35	import type { IFieldInstance } from '../field/model/factory';	4✔
36	import { createFieldInstanceByRaw } from '../field/model/factory';	4✔
37	import { RecordService } from '../record/record.service';	4✔
38		4✔
39	export type IWithView = {	4✔
40	viewId?: string;	4✔
41	customFilter?: IFilter;	4✔
42	customFieldStats?: ICustomFieldStats[];	4✔
43	};	4✔
44		4✔
45	type ICustomFieldStats = {	4✔
46	fieldId: string;	4✔
47	statisticFunc?: StatisticsFunc;	4✔
48	};	4✔
49		4✔
50	type IStatisticsData = {	4✔
51	viewId?: string;	4✔
52	filter?: IFilter;	4✔
53	statisticFields?: IAggregationField[];	4✔
54	};	4✔
55		4✔
56	@Injectable()	4✔
57	export class AggregationService {	4✔
58	private logger = new Logger(AggregationService.name);	128✔
59		128✔
60	constructor(	128✔
61	private readonly recordService: RecordService,	128✔
62	private readonly prisma: PrismaService,	128✔
63	@InjectModel('CUSTOM_KNEX') private readonly knex: Knex,	128✔
64	@InjectDbProvider() private readonly dbProvider: IDbProvider,	128✔
65	private readonly cls: ClsService<IClsStore>,	128✔
66	@ThresholdConfig() private readonly thresholdConfig: IThresholdConfig	128✔
67	) {}	128✔
68		128✔
69	async performAggregation(params: {	128✔
70	tableId: string;	96✔
71	withFieldIds?: string[];	96✔
72	withView?: IWithView;	96✔
73	search?: [string, string];	96✔
74	}): Promise<IRawAggregationValue> {	96✔
75	const { tableId, withFieldIds, withView, search } = params;	96✔
76	// Retrieve the current user's ID to build user-related query conditions	96✔
77	const currentUserId = this.cls.get('user.id');	96✔
78		96✔
79	const { statisticsData, fieldInstanceMap } = await this.fetchStatisticsParams({	96✔
80	tableId,	96✔
81	withView,	96✔
82	withFieldIds,	96✔
83	});	96✔
84		96✔
85	const dbTableName = await this.getDbTableName(this.prisma, tableId);	96✔
86		96✔
87	const { filter, statisticFields } = statisticsData;	96✔
88		96✔
89	const rawAggregationData = await this.handleAggregation({	96✔
90	dbTableName,	96✔
91	fieldInstanceMap,	96✔
92	filter,	96✔
93	search,	96✔
94	statisticFields,	96✔
95	withUserId: currentUserId,	96✔
96	});	96✔
97		96✔
98	const aggregationResult = rawAggregationData && rawAggregationData[0];	96✔
99		96✔
100	const aggregations: IRawAggregations = [];	96✔
101	if (aggregationResult) {	96✔
102	for (const [key, value] of Object.entries(aggregationResult)) {	96✔
103	const [fieldId, aggFunc] = key.split('_') as [string, StatisticsFunc \| undefined];	96✔
104		96✔
105	const convertValue = this.formatConvertValue(value, aggFunc);	96✔
106		96✔
107	if (fieldId) {	96✔
108	aggregations.push({	96✔
109	fieldId,	96✔
110	total: aggFunc ? { value: convertValue, aggFunc: aggFunc } : null,	96✔
111	});	96✔
112	}	96✔
113	}	96✔
114	}	96✔
115	return { aggregations };	96✔
116	}	96✔
117		128✔
118	async performRowCount(tableId: string, queryRo: IQueryBaseRo): Promise<IRawRowCountValue> {	128✔
119	const { filterLinkCellCandidate, filterLinkCellSelected } = queryRo;	56✔
120	// Retrieve the current user's ID to build user-related query conditions	56✔
121	const currentUserId = this.cls.get('user.id');	56✔
122		56✔
123	const { statisticsData, fieldInstanceMap } = await this.fetchStatisticsParams({	56✔
124	tableId,	56✔
125	withView: {	56✔
126	viewId: queryRo.viewId,	56✔
127	customFilter: queryRo.filter,	56✔
128	},	56✔
129	});	56✔
130		56✔
131	const dbTableName = await this.getDbTableName(this.prisma, tableId);	56✔
132		56✔
133	const { filter } = statisticsData;	56✔
134		56✔
135	if (filterLinkCellSelected) {	56✔
136	// TODO: use a new method to retrieve only count	24✔
137	const { ids } = await this.recordService.getLinkSelectedRecordIds(filterLinkCellSelected);	24✔
138	return { rowCount: ids.length };	24✔
139	}	24✔
140		32✔
141	const rawRowCountData = await this.handleRowCount({	32✔
142	tableId,	32✔
143	dbTableName,	32✔
144	fieldInstanceMap,	32✔
145	filter,	32✔
146	filterLinkCellCandidate,	32✔
147	search: queryRo.search,	32✔
148	withUserId: currentUserId,	32✔
149	});	32✔
150		32✔
151	return {	32✔
152	rowCount: Number(rawRowCountData[0]?.count ?? 0),	56✔
153	};	56✔
154	}	56✔
155		128✔
156	private async fetchStatisticsParams(params: {	128✔
157	tableId: string;	152✔
158	withView?: IWithView;	152✔
159	withFieldIds?: string[];	152✔
160	}): Promise<{	152✔
161	statisticsData: IStatisticsData;	152✔
162	fieldInstanceMap: Record<string, IFieldInstance>;	152✔
163	}> {	152✔
164	const { tableId, withView, withFieldIds } = params;	152✔
165		152✔
166	const viewRaw = await this.findView(tableId, withView);	152✔
167		152✔
168	const { fieldInstances, fieldInstanceMap } = await this.getFieldsData(tableId);	152✔
169	const filteredFieldInstances = this.filterFieldInstances(	152✔
170	fieldInstances,	152✔
171	withView,	152✔
172	withFieldIds	152✔
173	);	152✔
174		152✔
175	const statisticsData = this.buildStatisticsData(filteredFieldInstances, viewRaw, withView);	152✔
176	return { statisticsData, fieldInstanceMap };	152✔
177	}	152✔
178		128✔
179	private async findView(tableId: string, withView?: IWithView) {	128✔
180	if (!withView?.viewId) {	152✔
181	return undefined;	48✔
182	}	48✔
183		104✔
184	return nullsToUndefined(	104✔
185	await this.prisma.view.findFirst({	104✔
186	select: { id: true, columnMeta: true, filter: true, group: true },	104✔
187	where: {	104✔
188	tableId,	104✔
189	...(withView?.viewId ? { id: withView.viewId } : {}),	152✔
190	type: { in: [ViewType.Grid, ViewType.Gantt] },	152✔
191	deletedTime: null,	152✔
192	},	152✔
193	})	152✔
194	);	104✔
195	}	104✔
196		128✔
197	private filterFieldInstances(	128✔
198	fieldInstances: IFieldInstance[],	152✔
199	withView?: IWithView,	152✔
200	withFieldIds?: string[]	152✔
201	) {	152✔
202	const targetFieldIds =	152✔
203	withView?.customFieldStats?.map((field) => field.fieldId) ?? withFieldIds;	152✔
204		152✔
205	return targetFieldIds?.length	152✔
206	? fieldInstances.filter((instance) => targetFieldIds.includes(instance.id))	96✔
207	: fieldInstances;	56✔
208	}	152✔
209		128✔
210	private buildStatisticsData(	128✔
211	filteredFieldInstances: IFieldInstance[],	152✔
212	viewRaw:	152✔
213	\| {	152✔
214	id: string \| undefined;	152✔
215	columnMeta: string \| undefined;	152✔
216	filter: string \| undefined;	152✔
217	group: string \| undefined;	152✔
218	}	152✔
219	\| undefined,	152✔
220	withView?: IWithView	152✔
221	) {	152✔
222	let statisticsData: IStatisticsData = {	152✔
223	viewId: viewRaw?.id,	152✔
224	};	152✔
225		152✔
226	if (viewRaw?.filter \|\| withView?.customFilter) {	152✔
227	const filter = mergeWithDefaultFilter(viewRaw?.filter, withView?.customFilter);	×
228	statisticsData = { ...statisticsData, filter };	×
229	}	×
230		152✔
231	if (viewRaw?.id \|\| withView?.customFieldStats) {	152✔
232	const statisticFields = this.getStatisticFields(	104✔
233	filteredFieldInstances,	104✔
234	viewRaw?.columnMeta && JSON.parse(viewRaw.columnMeta),	104✔
235	withView?.customFieldStats	104✔
236	);	104✔
237	statisticsData = { ...statisticsData, statisticFields };	104✔
238	}	104✔
239	return statisticsData;	152✔
240	}	152✔
241		128✔
242	async getFieldsData(tableId: string, fieldIds?: string[]) {	128✔
243	const fieldsRaw = await this.prisma.field.findMany({	248✔
244	where: { tableId, ...(fieldIds ? { id: { in: fieldIds } } : {}), deletedTime: null },	248✔
245	});	248✔
246		248✔
247	const fieldInstances = fieldsRaw.map((field) => createFieldInstanceByRaw(field));	248✔
248	const fieldInstanceMap = fieldInstances.reduce(	248✔
249	(map, field) => {	248✔
250	map[field.id] = field;	1,142✔
251	map[field.name] = field;	1,142✔
252	return map;	1,142✔
253	},	1,142✔
254	{} as Record<string, IFieldInstance>	248✔
255	);	248✔
256	return { fieldInstances, fieldInstanceMap };	248✔
257	}	248✔
258		128✔
259	private getStatisticFields(	128✔
260	fieldInstances: IFieldInstance[],	104✔
261	columnMeta?: IGridColumnMeta,	104✔
262	customFieldStats?: ICustomFieldStats[]	104✔
263	) {	104✔
264	let calculatedStatisticFields: IAggregationField[] \| undefined;	104✔
265	const customFieldStatsGrouped = groupBy(customFieldStats, 'fieldId');	104✔
266		104✔
267	fieldInstances.forEach((fieldInstance) => {	104✔
268	const { id: fieldId } = fieldInstance;	134✔
269	const viewColumnMeta = columnMeta ? columnMeta[fieldId] : undefined;	134✔
270	const customFieldStats = customFieldStatsGrouped[fieldId];	134✔
271		134✔
272	if (viewColumnMeta \|\| customFieldStats) {	134✔
273	const { hidden, statisticFunc } = viewColumnMeta \|\| {};	134✔
274	const statisticFuncList = customFieldStats	134✔
275	?.filter((item) => item.statisticFunc)	134✔
276	?.map((item) => item.statisticFunc) as StatisticsFunc[];	134✔
277		134✔
278	const funcList = !isEmpty(statisticFuncList)	134✔
279	? statisticFuncList	96✔
280	: statisticFunc && [statisticFunc];	38✔
281		134✔
282	if (hidden !== true && funcList && funcList.length) {	134✔
283	const statisticFieldList = funcList.map((item) => {	96✔
284	return {	96✔
285	fieldId,	96✔
286	statisticFunc: item,	96✔
287	};	96✔
288	});	96✔
289	(calculatedStatisticFields = calculatedStatisticFields ?? []).push(...statisticFieldList);	96✔
290	}	96✔
291	}	134✔
292	});	134✔
293	return calculatedStatisticFields;	104✔
294	}	104✔
295		128✔
296	private handleAggregation(params: {	128✔
297	dbTableName: string;	96✔
298	fieldInstanceMap: Record<string, IFieldInstance>;	96✔
299	filter?: IFilter;	96✔
300	search?: [string, string];	96✔
301	statisticFields?: IAggregationField[];	96✔
302	withUserId?: string;	96✔
303	}) {	96✔
304	const { dbTableName, fieldInstanceMap, filter, search, statisticFields, withUserId } = params;	96✔
305	if (!statisticFields?.length) {	96✔
306	return;	×
307	}	×
308		96✔
309	const tableAlias = 'main_table';	96✔
310	const queryBuilder = this.knex	96✔
311	.with(tableAlias, (qb) => {	96✔
312	qb.select('*').from(dbTableName);	96✔
313	if (filter) {	96✔
314	this.dbProvider	×
315	.filterQuery(qb, fieldInstanceMap, filter, { withUserId })	×
316	.appendQueryBuilder();	×
317	}	×
318	if (search) {	96✔
NEW 319	this.dbProvider.searchQuery(qb, fieldInstanceMap, search);	×
NEW 320	}	×
321	})	96✔
322	.from(tableAlias);	96✔
323		96✔
324	const aggSql = this.dbProvider	96✔
325	.aggregationQuery(queryBuilder, tableAlias, fieldInstanceMap, statisticFields)	96✔
326	.toQuerySql();	96✔
327	return this.prisma.$queryRawUnsafe<{ [field: string]: unknown }[]>(aggSql);	96✔
328	}	96✔
329		128✔
330	private async handleRowCount(params: {	128✔
331	tableId: string;	32✔
332	dbTableName: string;	32✔
333	fieldInstanceMap: Record<string, IFieldInstance>;	32✔
334	filter?: IFilter;	32✔
335	filterLinkCellCandidate?: IGetRecordsRo['filterLinkCellCandidate'];	32✔
336	search?: [string, string];	32✔
337	withUserId?: string;	32✔
338	}) {	32✔
339	const {	32✔
340	tableId,	32✔
341	dbTableName,	32✔
342	fieldInstanceMap,	32✔
343	filter,	32✔
344	filterLinkCellCandidate,	32✔
345	search,	32✔
346	withUserId,	32✔
347	} = params;	32✔
348		32✔
349	const queryBuilder = this.knex(dbTableName);	32✔
350		32✔
351	if (filter) {	32✔
352	this.dbProvider	×
353	.filterQuery(queryBuilder, fieldInstanceMap, filter, { withUserId })	×
354	.appendQueryBuilder();	×
355	}	×
356		32✔
357	if (search) {	32✔
NEW 358	this.dbProvider.searchQuery(queryBuilder, fieldInstanceMap, search);	×
NEW 359	}	×
360		32✔
361	if (filterLinkCellCandidate) {	32✔
362	await this.recordService.buildLinkCandidateQuery(	24✔
363	queryBuilder,	24✔
364	tableId,	24✔
365	filterLinkCellCandidate	24✔
366	);	24✔
367	}	24✔
368		32✔
369	return this.getRowCount(this.prisma, queryBuilder);	32✔
370	}	32✔
371		128✔
372	private convertValueToNumberOrString(currentValue: unknown): number \| string \| null {	128✔
373	if (typeof currentValue === 'bigint' \|\| typeof currentValue === 'number') {	96✔
374	return Number(currentValue);	70✔
375	}	70✔
376	if (isDate(currentValue)) {	96✔
377	return currentValue.toISOString();	2✔
378	}	2✔
379	return currentValue?.toString() ?? null;	96✔
380	}	96✔
381		128✔
382	private calculateDateRangeOfMonths(currentValue: string): number {	128✔
383	const [maxTime, minTime] = currentValue.split(',');	2✔
384	return maxTime && minTime ? dayjs(maxTime).diff(minTime, 'month') : 0;	2✔
385	}	2✔
386		128✔
387	private formatConvertValue = (currentValue: unknown, aggFunc?: StatisticsFunc) => {	128✔
388	let convertValue = this.convertValueToNumberOrString(currentValue);	96✔
389		96✔
390	if (!aggFunc) {	96✔
391	return convertValue;	×
392	}	×
393		96✔
394	if (aggFunc === StatisticsFunc.DateRangeOfMonths && typeof currentValue === 'string') {	96✔
395	convertValue = this.calculateDateRangeOfMonths(currentValue);	2✔
396	}	2✔
397		96✔
398	const defaultToZero = [	96✔
399	StatisticsFunc.PercentEmpty,	96✔
400	StatisticsFunc.PercentFilled,	96✔
401	StatisticsFunc.PercentUnique,	96✔
402	StatisticsFunc.PercentChecked,	96✔
403	StatisticsFunc.PercentUnChecked,	96✔
404	];	96✔
405		96✔
406	if (defaultToZero.includes(aggFunc)) {	96✔
407	convertValue = convertValue ?? 0;	40✔
408	}	40✔
409	return convertValue;	96✔
410	};	96✔
411		128✔
412	private async getDbTableName(prisma: Prisma.TransactionClient, tableId: string) {	128✔
413	const tableMeta = await prisma.tableMeta.findUniqueOrThrow({	152✔
414	where: { id: tableId },	152✔
415	select: { dbTableName: true },	152✔
416	});	152✔
417	return tableMeta.dbTableName;	152✔
418	}	152✔
419		128✔
420	private async getRowCount(prisma: Prisma.TransactionClient, queryBuilder: Knex.QueryBuilder) {	128✔
421	queryBuilder	32✔
422	.clearSelect()	32✔
423	.clearCounters()	32✔
424	.clearGroup()	32✔
425	.clearHaving()	32✔
426	.clearOrder()	32✔
427	.clear('limit')	32✔
428	.clear('offset');	32✔
429	const rowCountSql = queryBuilder.count({ count: '*' });	32✔
430		32✔
431	return prisma.$queryRawUnsafe<{ count?: number }[]>(rowCountSql.toQuery());	32✔
432	}	32✔
433		128✔
434	@Timing()	128✔
435	private groupDbCollection2GroupPoints(	128✔
436	groupResult: { [key: string]: unknown; __c: number }[],	×
437	groupFields: IFieldInstance[]	×
438	) {	×
439	const groupPoints: IGroupPoint[] = [];	×
440	let fieldValues: unknown[] = [Symbol(), Symbol(), Symbol()];	×
441		×
442	groupResult.forEach((item) => {	×
443	const { __c: count } = item;	×
444		×
445	groupFields.forEach((field, index) => {	×
446	const { id, dbFieldName } = field;	×
447	const fieldValue = this.convertValueToNumberOrString(item[dbFieldName]);	×
448		×
449	if (fieldValues[index] === fieldValue) return;	×
450		×
451	fieldValues[index] = fieldValue;	×
452	fieldValues = fieldValues.map((value, idx) => (idx > index ? Symbol() : value));	×
453		×
454	const flagString = `${id}_${fieldValues.slice(0, index + 1).join('_')}`;	×
455		×
456	groupPoints.push({	×
457	id: String(string2Hash(flagString)),	×
458	type: GroupPointType.Header,	×
459	depth: index,	×
460	value: field.convertDBValue2CellValue(fieldValue),	×
461	});	×
462	});	×
463		×
464	groupPoints.push({ type: GroupPointType.Row, count: Number(count) });	×
465	});	×
466	return groupPoints;	×
467	}	×
468		128✔
469	private async checkGroupingOverLimit(dbFieldNames: string[], queryBuilder: Knex.QueryBuilder) {	128✔
470	queryBuilder.countDistinct(dbFieldNames);	×
471		×
472	const distinctResult = await this.prisma.$queryRawUnsafe<{ count: number }[]>(	×
473	queryBuilder.toQuery()	×
474	);	×
475	const distinctCount = Number(distinctResult[0].count);	×
476		×
477	return distinctCount > this.thresholdConfig.maxGroupPoints;	×
478	}	×
479		128✔
480	public async getGroupPoints(tableId: string, query?: IGroupPointsRo) {	128✔
481	const { viewId, groupBy: extraGroupBy, filter } = query \|\| {};	×
482		×
483	if (!viewId) return null;	×
484		×
485	const groupBy = parseGroup(extraGroupBy);	×
486		×
487	if (!groupBy?.length) return null;	×
488		×
489	const viewRaw = await this.findView(tableId, { viewId });	×
490	const { fieldInstanceMap } = await this.getFieldsData(tableId);	×
491	const dbTableName = await this.getDbTableName(this.prisma, tableId);	×
492		×
493	const filterStr = viewRaw?.filter;	×
494	const mergedFilter = mergeWithDefaultFilter(filterStr, filter);	×
495	const groupFieldIds = groupBy.map((item) => item.fieldId);	×
496		×
497	const queryBuilder = this.knex(dbTableName);	×
498	const distinctQueryBuilder = this.knex(dbTableName);	×
499		×
500	if (mergedFilter) {	×
501	const withUserId = this.cls.get('user.id');	×
502	this.dbProvider	×
503	.filterQuery(queryBuilder, fieldInstanceMap, mergedFilter, { withUserId })	×
504	.appendQueryBuilder();	×
505	this.dbProvider	×
506	.filterQuery(distinctQueryBuilder, fieldInstanceMap, mergedFilter, { withUserId })	×
507	.appendQueryBuilder();	×
508	}	×
509		×
510	const dbFieldNames = groupFieldIds.map((fieldId) => fieldInstanceMap[fieldId].dbFieldName);	×
511		×
512	const isGroupingOverLimit = await this.checkGroupingOverLimit(	×
513	dbFieldNames,	×
514	distinctQueryBuilder	×
515	);	×
516	if (isGroupingOverLimit) {	×
517	throw new HttpException(	×
518	'Grouping results exceed limit, please adjust grouping conditions to reduce the number of groups.',	×
519	HttpStatus.PAYLOAD_TOO_LARGE	×
520	);	×
521	}	×
522		×
523	this.dbProvider.sortQuery(queryBuilder, fieldInstanceMap, groupBy).appendSortBuilder();	×
524		×
525	queryBuilder.count({ __c: '*' });	×
526		×
527	groupFieldIds.forEach((fieldId) => {	×
528	const field = fieldInstanceMap[fieldId];	×
529		×
530	if (!field) return;	×
531		×
532	const { dbFieldType, dbFieldName } = field;	×
533	const column =	×
534	dbFieldType === DbFieldType.Json	×
535	? this.knex.raw(`CAST(?? as text)`, [dbFieldName]).toQuery()	×
536	: this.knex.ref(dbFieldName).toQuery();	×
537		×
538	queryBuilder.select(this.knex.raw(`${column}`)).groupBy(dbFieldName);	×
539	});	×
540		×
541	const groupSql = queryBuilder.toQuery();	×
542		×
543	const result =	×
544	await this.prisma.$queryRawUnsafe<{ [key: string]: unknown; __c: number }[]>(groupSql);	×
545		×
546	const groupFields = groupFieldIds.map((fieldId) => fieldInstanceMap[fieldId]);	×
547		×
548	return this.groupDbCollection2GroupPoints(result, groupFields);	×
549	}	×
550	}	128✔

teableio / teable / 8478979078

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