8453146637

Committed 27 Mar 2024 02:08PM CUT coverage: 82.388% (+60.6%) from 21.837%

Build # 8453146637

Build Type

push

github

Committed by

web-flow

Commit Message

fix: date field grouping collapse (#505)

* fix: date field grouping collapse

* fix: remove redundant record menu items

* fix: long text field editor style

Run Details

3850 of 4032 branches covered (95.49%)

1 of 2 new or added lines in 1 file covered. (50.0%)

25944 of 31490 relevant lines covered (82.39%)

1211.99 hits per line

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

76.42

/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;
  }): Promise<IRawAggregationValue> {
    const { tableId, withFieldIds, withView } = 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,
      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,
      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;
    statisticFields?: IAggregationField[];
    withUserId?: string;
  }) {
    const { dbTableName, fieldInstanceMap, filter, 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();
        }
      })
      .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'];
    withUserId?: string;
  }) {
    const { tableId, dbTableName, fieldInstanceMap, filter, filterLinkCellCandidate, withUserId } =
      params;

    const queryBuilder = this.knex(dbTableName);

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

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

teableio / teable / 8453146637

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