11851743864

Committed 15 Nov 2024 06:58AM UTC coverage: 84.314%. First build

Build # 11851743864

Build Type

Pull #1089

github

Committed by

web-flow

Commit Message

Merge f8d5060be into 501f35cd4

Pull Request Pull Request #1089: feat: suport search cursor highlight

Run Details

5983 of 6285 branches covered (95.19%)

251 of 275 new or added lines in 5 files covered. (91.27%)

39513 of 46864 relevant lines covered (84.31%)

1698.02 hits per line

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

96.44

/apps/nestjs-backend/src/db-provider/search-query/abstract.ts

import { CellValueType } from '@teable/core';
import type { Knex } from 'knex';
import type { IFieldInstance } from '../../features/field/model/factory';

export abstract class SearchQueryAbstract {
  static factory(
    // eslint-disable-next-line @typescript-eslint/naming-convention
    SearchQuery: new (
      originQueryBuilder: Knex.QueryBuilder,
      field: IFieldInstance,
      searchValue: string
    ) => SearchQueryAbstract,
    originQueryBuilder: Knex.QueryBuilder,
    fieldMap?: { [fieldId: string]: IFieldInstance },
    search?: [string, string?, boolean?]
  ) {
    if (!search || !fieldMap) {
      return originQueryBuilder;
    }

    let searchArr = [];

    if (!search?.[1]) {
      searchArr = Object.values(fieldMap).map((f) => f.id);
    } else {
      searchArr = search[1]?.split(',');
    }

    const searchValue = search[0];

    searchArr.forEach((item) => {
      const field = fieldMap?.[item];

      if (!field) {
        return;
      }

      if (field.cellValueType === CellValueType.Boolean) {
        return;
      }

      const searchQueryBuilder = new SearchQuery(originQueryBuilder, field, searchValue);

      if (field.isMultipleCellValue) {
        switch (field.cellValueType) {
          case CellValueType.DateTime:
            searchQueryBuilder.multipleDate();
            break;
          case CellValueType.Number:
            searchQueryBuilder.multipleNumber();
            break;
          case CellValueType.String:
            if (field.isStructuredCellValue) {
              searchQueryBuilder.multipleJson();
            } else {
              searchQueryBuilder.multipleText();
            }
            break;
        }
        return;
      }

      switch (field.cellValueType) {
        case CellValueType.DateTime:
          searchQueryBuilder.date();
          break;
        case CellValueType.Number:
          searchQueryBuilder.number();
          break;
        case CellValueType.String:
          if (field.isStructuredCellValue) {
            searchQueryBuilder.json();
          } else {
            searchQueryBuilder.text();
          }
          break;
      }
    });

    return originQueryBuilder;
  }

  static buildSearchIndexQuery(
    // eslint-disable-next-line @typescript-eslint/naming-convention
    SearchQuery: new (
      originQueryBuilder: Knex.QueryBuilder,
      field: IFieldInstance,
      searchValue: string
    ) => SearchQueryAbstract,
    queryBuilder: Knex.QueryBuilder,
    searchField: IFieldInstance[],
    searchValue: string,
    dbTableName: string
  ) {
    const knexInstance = queryBuilder.client;
    const searchQuery = searchField
      .map((field) => {
        const searchQueryBuilder = new SearchQuery(queryBuilder, field, searchValue);
        if (field.isMultipleCellValue) {
          switch (field.cellValueType) {
            case CellValueType.DateTime:
              return searchQueryBuilder.getMultipleDateSqlQuery();
            case CellValueType.Number:
              return searchQueryBuilder.getMultipleNumberSqlQuery();
            case CellValueType.String:
              if (field.isStructuredCellValue) {
                return searchQueryBuilder.getMultipleJsonSqlQuery();
              } else {
                return searchQueryBuilder.getMultipleTextSqlQuery();
              }
          }
        }

        switch (field.cellValueType) {
          case CellValueType.DateTime:
            return searchQueryBuilder.getDateSqlQuery();
          case CellValueType.Number:
            return searchQueryBuilder.getNumberSqlQuery();
          case CellValueType.String:
            if (field.isStructuredCellValue) {
              return searchQueryBuilder.getJsonSqlQuery();
            } else {
              return searchQueryBuilder.getTextSqlQuery();
            }
        }
      })
      .filter((sql) => !!sql);

    queryBuilder.with('search_field_union_table', (qb) => {
      searchField.forEach((field, index) => {
        if (index === 0) {
          qb.select('*', knexInstance.raw(`? as matched_column`, [field.dbFieldName]))
            .whereRaw(`${searchQuery[index]}`)
            .from(dbTableName);
        } else {
          qb.unionAll(function () {
            this.select('*', knexInstance.raw(`? as matched_column`, [field.dbFieldName]))
              .whereRaw(`${searchQuery[index]}`)
              .from(dbTableName);
          });
        }
      });
    });

    queryBuilder
      .select('__id', '__auto_number', 'matched_column')
      .select(
        knexInstance.raw(
          `CASE
            ${searchField.map((field) => `WHEN matched_column = '${field.dbFieldName}' THEN '${field.id}'`).join(' ')}
          END AS "fieldId"`
        )
      )
      .from('search_field_union_table');
    return queryBuilder;
  }

  static buildSearchCountQuery(
    // eslint-disable-next-line @typescript-eslint/naming-convention
    SearchQuery: new (
      originQueryBuilder: Knex.QueryBuilder,
      field: IFieldInstance,
      searchValue: string
    ) => SearchQueryAbstract,
    queryBuilder: Knex.QueryBuilder,
    searchField: IFieldInstance[],
    searchValue: string
  ) {
    const searchQuery = searchField
      .map((field) => {
        const searchQueryBuilder = new SearchQuery(queryBuilder, field, searchValue);

        if (field.isMultipleCellValue) {
          switch (field.cellValueType) {
            case CellValueType.DateTime:
              return searchQueryBuilder.getMultipleDateSqlQuery();
            case CellValueType.Number:
              return searchQueryBuilder.getMultipleNumberSqlQuery();
            case CellValueType.String:
              if (field.isStructuredCellValue) {
                return searchQueryBuilder.getMultipleJsonSqlQuery();
              } else {
                return searchQueryBuilder.getMultipleTextSqlQuery();
              }
          }
        }

        switch (field.cellValueType) {
          case CellValueType.DateTime:
            return searchQueryBuilder.getDateSqlQuery();
          case CellValueType.Number:
            return searchQueryBuilder.getNumberSqlQuery();
          case CellValueType.String:
            if (field.isStructuredCellValue) {
              return searchQueryBuilder.getJsonSqlQuery();
            } else {
              return searchQueryBuilder.getTextSqlQuery();
            }
        }
      })
      .filter((sql) => !!sql);

    const knexInstance = queryBuilder.client;

    queryBuilder.select(
      knexInstance.raw(`
        COALESCE(SUM(
          ${searchQuery.map((sql) => `(CASE WHEN (${sql}) THEN 1 ELSE 0 END)`).join(' + ')}
        ), 0) as count
      `)
    );

    return queryBuilder;
  }

  constructor(
    protected readonly originQueryBuilder: Knex.QueryBuilder,
    protected readonly field: IFieldInstance,
    protected readonly searchValue: string
  ) {}

  abstract multipleNumber(): Knex.QueryBuilder;

  abstract multipleDate(): Knex.QueryBuilder;

  abstract multipleText(): Knex.QueryBuilder;

  abstract multipleJson(): Knex.QueryBuilder;

  abstract json(): Knex.QueryBuilder;

  abstract text(): Knex.QueryBuilder;

  abstract date(): Knex.QueryBuilder;

  abstract number(): Knex.QueryBuilder;

  abstract getNumberSqlQuery(): string;

  abstract getDateSqlQuery(): string;

  abstract getTextSqlQuery(): string;

  abstract getJsonSqlQuery(): string;

  abstract getMultipleNumberSqlQuery(): string;

  abstract getMultipleDateSqlQuery(): string;

  abstract getMultipleTextSqlQuery(): string;

  abstract getMultipleJsonSqlQuery(): string;
}

1	import { CellValueType } from '@teable/core';	4✔
2	import type { Knex } from 'knex';	4✔
3	import type { IFieldInstance } from '../../features/field/model/factory';	4✔
4		4✔
5	export abstract class SearchQueryAbstract {	4✔
6	static factory(	226✔
7	// eslint-disable-next-line @typescript-eslint/naming-convention	126✔
8	SearchQuery: new (	126✔
9	originQueryBuilder: Knex.QueryBuilder,	126✔
10	field: IFieldInstance,	126✔
11	searchValue: string	126✔
12	) => SearchQueryAbstract,	126✔
13	originQueryBuilder: Knex.QueryBuilder,	126✔
14	fieldMap?: { [fieldId: string]: IFieldInstance },	126✔
15	search?: [string, string?, boolean?]	126✔
16	) {	126✔
17	if (!search \|\| !fieldMap) {	126!
18	return originQueryBuilder;	×
19	}	×
20		126✔
21	let searchArr = [];	126✔
22		126✔
23	if (!search?.[1]) {	126!
24	searchArr = Object.values(fieldMap).map((f) => f.id);	×
25	} else {	126✔
26	searchArr = search[1]?.split(',');	126✔
27	}	126✔
28		126✔
29	const searchValue = search[0];	126✔
30		126✔
31	searchArr.forEach((item) => {	126✔
32	const field = fieldMap?.[item];	126✔
33		126✔
34	if (!field) {	126!
35	return;	×
36	}	×
37		126✔
38	if (field.cellValueType === CellValueType.Boolean) {	126✔
39	return;	6✔
40	}	6✔
41		120✔
42	const searchQueryBuilder = new SearchQuery(originQueryBuilder, field, searchValue);	120✔
43		120✔
44	if (field.isMultipleCellValue) {	126✔
45	switch (field.cellValueType) {	66✔
46	case CellValueType.DateTime:	66✔
47	searchQueryBuilder.multipleDate();	6✔
48	break;	6✔
49	case CellValueType.Number:	66✔
50	searchQueryBuilder.multipleNumber();	6✔
51	break;	6✔
52	case CellValueType.String:	66✔
53	if (field.isStructuredCellValue) {	54✔
54	searchQueryBuilder.multipleJson();	36✔
55	} else {	54✔
56	searchQueryBuilder.multipleText();	18✔
57	}	18✔
58	break;	54✔
59	}	66✔
60	return;	66✔
61	}	66✔
62		54✔
63	switch (field.cellValueType) {	54✔
64	case CellValueType.DateTime:	126✔
65	searchQueryBuilder.date();	12✔
66	break;	12✔
67	case CellValueType.Number:	126✔
68	searchQueryBuilder.number();	24✔
69	break;	24✔
70	case CellValueType.String:	126✔
71	if (field.isStructuredCellValue) {	18✔
72	searchQueryBuilder.json();	6✔
73	} else {	18✔
74	searchQueryBuilder.text();	12✔
75	}	12✔
76	break;	18✔
77	}	126✔
78	});	126✔
79		126✔
80	return originQueryBuilder;	126✔
81	}	126✔
82		226✔
83	static buildSearchIndexQuery(	226✔
84	// eslint-disable-next-line @typescript-eslint/naming-convention	4✔
85	SearchQuery: new (	4✔
86	originQueryBuilder: Knex.QueryBuilder,	4✔
87	field: IFieldInstance,	4✔
88	searchValue: string	4✔
89	) => SearchQueryAbstract,	4✔
90	queryBuilder: Knex.QueryBuilder,	4✔
91	searchField: IFieldInstance[],	4✔
92	searchValue: string,	4✔
93	dbTableName: string	4✔
94	) {	4✔
95	const knexInstance = queryBuilder.client;	4✔
96	const searchQuery = searchField	4✔
97	.map((field) => {	4✔
98	const searchQueryBuilder = new SearchQuery(queryBuilder, field, searchValue);	36✔
99	if (field.isMultipleCellValue) {	36✔
100	switch (field.cellValueType) {	12✔
101	case CellValueType.DateTime:	12!
NEW 102	return searchQueryBuilder.getMultipleDateSqlQuery();	×
103	case CellValueType.Number:	12!
NEW 104	return searchQueryBuilder.getMultipleNumberSqlQuery();	×
105	case CellValueType.String:	12✔
106	if (field.isStructuredCellValue) {	12✔
107	return searchQueryBuilder.getMultipleJsonSqlQuery();	8✔
108	} else {	12✔
109	return searchQueryBuilder.getMultipleTextSqlQuery();	4✔
110	}	4✔
111	}	12✔
112	}	12✔
113		24✔
114	switch (field.cellValueType) {	24✔
115	case CellValueType.DateTime:	36✔
116	return searchQueryBuilder.getDateSqlQuery();	4✔
117	case CellValueType.Number:	36✔
118	return searchQueryBuilder.getNumberSqlQuery();	8✔
119	case CellValueType.String:	36✔
120	if (field.isStructuredCellValue) {	12✔
121	return searchQueryBuilder.getJsonSqlQuery();	4✔
122	} else {	12✔
123	return searchQueryBuilder.getTextSqlQuery();	8✔
124	}	8✔
125	}	36✔
126	})	36✔
127	.filter((sql) => !!sql);	4✔
128		4✔
129	queryBuilder.with('search_field_union_table', (qb) => {	4✔
130	searchField.forEach((field, index) => {	4✔
131	if (index === 0) {	36✔
132	qb.select('*', knexInstance.raw(`? as matched_column`, [field.dbFieldName]))	4✔
133	.whereRaw(`${searchQuery[index]}`)	4✔
134	.from(dbTableName);	4✔
135	} else {	36✔
136	qb.unionAll(function () {	32✔
137	this.select('*', knexInstance.raw(`? as matched_column`, [field.dbFieldName]))	32✔
138	.whereRaw(`${searchQuery[index]}`)	32✔
139	.from(dbTableName);	32✔
140	});	32✔
141	}	32✔
142	});	36✔
143	});	4✔
144		4✔
145	queryBuilder	4✔
146	.select('__id', '__auto_number', 'matched_column')	4✔
147	.select(	4✔
148	knexInstance.raw(	4✔
149	`CASE	4✔
150	${searchField.map((field) => `WHEN matched_column = '${field.dbFieldName}' THEN '${field.id}'`).join(' ')}	4✔
151	END AS "fieldId"`	4✔
152	)	4✔
153	)	4✔
154	.from('search_field_union_table');	4✔
155	return queryBuilder;	4✔
156	}	4✔
157		226✔
158	static buildSearchCountQuery(	226✔
159	// eslint-disable-next-line @typescript-eslint/naming-convention	8✔
160	SearchQuery: new (	8✔
161	originQueryBuilder: Knex.QueryBuilder,	8✔
162	field: IFieldInstance,	8✔
163	searchValue: string	8✔
164	) => SearchQueryAbstract,	8✔
165	queryBuilder: Knex.QueryBuilder,	8✔
166	searchField: IFieldInstance[],	8✔
167	searchValue: string	8✔
168	) {	8✔
169	const searchQuery = searchField	8✔
170	.map((field) => {	8✔
171	const searchQueryBuilder = new SearchQuery(queryBuilder, field, searchValue);	70✔
172		70✔
173	if (field.isMultipleCellValue) {	70✔
174	switch (field.cellValueType) {	24✔
175	case CellValueType.DateTime:	24!
NEW 176	return searchQueryBuilder.getMultipleDateSqlQuery();	×
177	case CellValueType.Number:	24!
NEW 178	return searchQueryBuilder.getMultipleNumberSqlQuery();	×
179	case CellValueType.String:	24✔
180	if (field.isStructuredCellValue) {	24✔
181	return searchQueryBuilder.getMultipleJsonSqlQuery();	16✔
182	} else {	24✔
183	return searchQueryBuilder.getMultipleTextSqlQuery();	8✔
184	}	8✔
185	}	24✔
186	}	24✔
187		46✔
188	switch (field.cellValueType) {	46✔
189	case CellValueType.DateTime:	70✔
190	return searchQueryBuilder.getDateSqlQuery();	8✔
191	case CellValueType.Number:	70✔
192	return searchQueryBuilder.getNumberSqlQuery();	14✔
193	case CellValueType.String:	70✔
194	if (field.isStructuredCellValue) {	24✔
195	return searchQueryBuilder.getJsonSqlQuery();	8✔
196	} else {	24✔
197	return searchQueryBuilder.getTextSqlQuery();	16✔
198	}	16✔
199	}	70✔
200	})	70✔
201	.filter((sql) => !!sql);	8✔
202		8✔
203	const knexInstance = queryBuilder.client;	8✔
204		8✔
205	queryBuilder.select(	8✔
206	knexInstance.raw(`	8✔
207	COALESCE(SUM(	8✔
208	${searchQuery.map((sql) => `(CASE WHEN (${sql}) THEN 1 ELSE 0 END)`).join(' + ')}	8✔
209	), 0) as count	8✔
210	`)	8✔
211	);	8✔
212		8✔
213	return queryBuilder;	8✔
214	}	8✔
215		226✔
216	constructor(	226✔
217	protected readonly originQueryBuilder: Knex.QueryBuilder,	226✔
218	protected readonly field: IFieldInstance,	226✔
219	protected readonly searchValue: string	226✔
220	) {}	226✔
221		226✔
222	abstract multipleNumber(): Knex.QueryBuilder;	226✔
223		226✔
224	abstract multipleDate(): Knex.QueryBuilder;	226✔
225		226✔
226	abstract multipleText(): Knex.QueryBuilder;	226✔
227		226✔
228	abstract multipleJson(): Knex.QueryBuilder;	226✔
229		226✔
230	abstract json(): Knex.QueryBuilder;	226✔
231		226✔
232	abstract text(): Knex.QueryBuilder;	226✔
233		226✔
234	abstract date(): Knex.QueryBuilder;	226✔
235		226✔
236	abstract number(): Knex.QueryBuilder;	226✔
237		226✔
238	abstract getNumberSqlQuery(): string;	226✔
239		226✔
240	abstract getDateSqlQuery(): string;	226✔
241		226✔
242	abstract getTextSqlQuery(): string;	226✔
243		226✔
244	abstract getJsonSqlQuery(): string;	226✔
245		226✔
246	abstract getMultipleNumberSqlQuery(): string;	226✔
247		226✔
248	abstract getMultipleDateSqlQuery(): string;	226✔
249		226✔
250	abstract getMultipleTextSqlQuery(): string;	226✔
251		226✔
252	abstract getMultipleJsonSqlQuery(): string;	226✔
253	}	226✔

teableio / teable / 11851743864

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