wenbao-website/server/db/QueryGenerator.ts

import { CommonModel, CommonPageResult, NewCommonModel, ValidCommonModel } from "./CommonModel";
import { DateUtils, ObjectUtils } from "@imengyu/imengyu-utils";
import { query, queryAndReturnInsertId } from "./Query";

export interface QueryData {
  [index: string]: any;
}
export interface QueryWhereArg {
  field : string;
  valueOrOperator : string | QueryOperator;
  valueSecond ?: string;
}
export interface QueryResult {
  [index: string]: any;
}

const logger = {
  log : (msg : string, data?: any) => console.log(msg, data),
  error : (msg : string, data?: any) => console.error(msg, data),
}

/**
 * 查询比较运算符
 */
export type QueryOperator = ''|'='|'!='|'<'|'>'|'<>'|'<='|'>='|'is null'|'not null'|'like'|'between'|'in';
/**
 * 查询条件
 */
export type QueryCondition = 'and'|'or';

export type QuerySubWhereCallback = (query : QueryGenerator) => void;

type QueryAcceptValue = string|number|boolean|Date;

interface QueryWhere {
  condition : QueryCondition;
  not: boolean;
  operator : QueryOperator;
  field: string;
  value: any;
  valueIsField?: boolean;
  valueIsRawSql?: boolean;
  valueChildren ?: QueryWhere[];
}
interface QueryOrderBy {
  field : string;
  direction : 'asc'|'desc';
  isRand: boolean;
}
class QuerySelectField {
  isRaw = false;
  field = '';

  static wrapKey(str: string) {
    const strs = str.replace(/\`/g, '').split('.');
    return strs.map(k => k == '*' ? k : `\`${k}\``).join('.');
  }

  public constructor(field : string, isRaw = false) {
    this.field = field?.toString() ?? '';
    this.isRaw = isRaw;
  }
  public toString() {
    return this.isRaw ? this.field : QuerySelectField.wrapKey(this.field);
  }
}

/**
 * 查询构造器。
 * 用于构建SQL查询语句的构造器。
 * 
 * 可以从 DB 类快速构建查询语句：
 * ```ts
 * // 构建查询语句
 * DB.table('users')
 *   .select('*')
 *   .where('username', 'imengyu')
 *   .orderBy('id', 'desc')
 *   .limit(10)
 *   .offset(0)
 *   .get();
 * ```
 * 
 * @example
 * // 基本查询示例
 * const users = await DB.table('users')
 *   .select('id', 'username', 'email')
 *   .get<UserModel>(UserModel);
 * 
 * @example
 * // 条件查询示例
 * const user = await DB.table('users')
 *   .where('id', '=', 1)
 *   .orWhere('username', 'imengyu')
 *   .where('status', 'active')
 *   .first<UserModel>(UserModel);
 * 
 * @example
 * // 复杂条件查询示例
 * const users = await DB.table('users')
 *   .where('age', '>', 18)
 *   .where('created_at', '>=', new Date('2023-01-01'))
 *   .where('status', 'in', [1, 2, 3])
 *   .where('name', 'like', '%张%')
 *   .get<UserModel>(UserModel);
 * 
 * @example
 * // 排序和分页示例
 * const users = await DB.table('users')
 *   .select('*')
 *   .orderBy('created_at', 'desc')
 *   .orderBy('id', 'asc')
 *   .limit(10)
 *   .offset(20)
 *   .get<UserModel>(UserModel);
 * 
 * @example
 * // 分页查询示例
 * const pageResult = await DB.table('users')
 *   .select('*')
 *   .where('status', 1)
 *   .orderBy('id', 'desc')
 *   .paginate<UserModel>(2, 10, UserModel);
 * 
 * @example
 * // 插入数据示例
 * await DB.table('users')
 *   .insert({
 *     username: 'imengyu',
 *     email: 'imengyu@example.com',
 *     created_at: new Date()
 *   });
 * 
 * // 插入并获取ID
 * const userId = await DB.table('users')
 *   .insertGetId({
 *     username: 'imengyu',
 *     email: 'imengyu@example.com'
 *   });
 * 
 * @example
 * // 更新数据示例
 * await DB.table('users')
 *   .where('id', 1)
 *   .update({
 *     username: 'new_username',
 *     updated_at: new Date()
 *   });
 * 
 * @example
 * // 删除数据示例
 * await DB.table('users')
 *   .where('id', 1)
 *   .delete();
 * 
 * @example
 * // 聚合函数示例
 * const count = await DB.table('users').count();
 * const maxAge = await DB.table('users').max('age');
 * const minAge = await DB.table('users').min('age');
 * const avgAge = await DB.table('users').avg('age');
 * const sumAge = await DB.table('users').sum('age');
 * 
 * @example
 * // 分组查询示例
 * const userCounts = await DB.table('users')
 *   .select('status', 'COUNT(*) as count')
 *   .groupBy('status')
 *   .get();
 * 
 * @example
 * // 存在性检查示例
 * const exists = await DB.table('users')
 *   .where('email', 'imengyu@example.com')
 *   .exists();
 * 
 * const notExists = await DB.table('users')
 *   .where('email', 'nonexistent@example.com')
 *   .doesntExist();
 * 
 * @example
 * // 原生SQL示例
 * const users = await DB.table('users')
 *   .selectRaw('id, username, email')
 *   .whereRaw('created_at >= ? AND status = ?', [new Date('2023-01-01'), 1])
 *   .get<UserModel>(UserModel);
 */
export class QueryGenerator<O extends CommonModel = any> {

  /**
   * 当前表名
   */
  public tableName = '';
  public enableLogSQL = false; 
  public dateFormat = 'yyyy-MM-dd HH:mm:ss';

  private tableSetCommonModel: NewCommonModel<O>|undefined;

  public enableLog() {
    this.enableLogSQL = true;
    return this;
  }

  public constructor(tableName : string|NewCommonModel<O>) {
    if (tableName instanceof Function) {
      this.tableSetCommonModel = tableName;
      this.tableName = new tableName()._tableName;
    } else if (typeof tableName === 'string')
      this.tableName = tableName;
  }

  //#region 工具方法

  /**
   * 清空所有已生成的SQL
   */
  public emptyAll() {
    this.whereConditions = [];
    this.orderByConditions = [];
    this.groupByConditions = [];
    this.limitCondition = -1;
    this.offsetCondition = -1;
    this.lastLimitCondition = '';
    this.lastWhereCondition = '';
    this.addDistinct = false;

    
    return this;
  }

  private solveQueryArrayData(arr : any[]) {
    let sqlValues = new Array<string>();
    for(let i = 0; i < arr.length; i++) {
      if(typeof arr[i] !== 'undefined')
        sqlValues.push(this.solveQueryVar(arr[i]))
    }
    return sqlValues
  }
  private solveQueryData(param : QueryData, acceptFields : string[], noId = false) {
    if (param instanceof CommonModel)
      param = param.toServerSide();
    const keys = Object.keys(param);
    const sqlKeys = new Array<string>();
    const sqlValues = new Array<string>();
    let key = '', emptyAccepts = acceptFields.length === 0, val : any = null;
    for(let i = 0; i < keys.length; i++) {
      key = keys[i], val = param[key];
      if (noId && key === 'id')
        continue;
      if((ObjectUtils.isDefined(val) && key !== 'id') && (emptyAccepts || acceptFields.includes(key))) {
        sqlKeys.push(key);
        sqlValues.push(this.solveQueryVar(val))
      }
    }
    return { sqlKeys, sqlValues }
  }
  private replaceForSQlite(v : string) {
    return v.replace(/\'/g, "\\'");
  }
  private solveQueryVar(v : any) {
    if(typeof v === 'undefined' || v === null)
      return 'null';
    else if(typeof v === 'string')
      v =  `'${this.replaceForSQlite(v).replace(/\`/g,'\\\`')}'`;
    else if(typeof v === 'number' || typeof v === 'bigint')
      v = v.toString();
    else if(typeof v === 'object') {
      if (v instanceof Date)
        v = `'${DateUtils.formatDate(v, this.dateFormat)}'`;
      else if(typeof v === 'object' && v instanceof CommonModel)
        v =  `'${this.replaceForSQlite(JSON.stringify(v.toServerSide()))}'`;
      else
        v = `'${this.replaceForSQlite(JSON.stringify(v))}'`;
    }
    return ('' + v)
  }
  private solveFields(arr : string[]) {
    let sqlValues = '';
    for(let i = 0; i < arr.length; i++) {
      if(i > 0) sqlValues += ',';
      sqlValues += QuerySelectField.wrapKey(arr[i]);
    }
    return sqlValues
  }

  //#endregion

  //#region insert 子句

  /**
   * 插入操作
   * @param param 插入的参数
   * @returns Promise 不返回数据
   */
  public async insert(param : O|QueryData | (O|QueryData)[], acceptFields : string[] = []) {
    if (param instanceof Array)
      await Promise.all(param.map(v => () => this.doInsert(v, false, acceptFields)));
    else 
      await this.doInsert(param, false, acceptFields);
  }
  /**
   * 执行插入操作并返回新插入记录的ID
   * @param param 插入的参数
   * @returns Promise 返回新插入记录的ID
   */
  public insertGetId(param : O|QueryData, acceptFields : string[] = []) { 
    return this.doInsert(param, true, acceptFields); 
  }

  //插入
  private async doInsert(param : O|QueryData, returnId : boolean, acceptFields : string[]) {
    let { sqlKeys, sqlValues } = this.solveQueryData(param, acceptFields, true);
    let sql = `INSERT INTO \`${this.tableName}\` (${sqlKeys.map(k => QuerySelectField.wrapKey(k)).join(',')}) VALUES (${sqlValues.join(',')})`;

    if(this.enableLogSQL) logger.log('Query', sql);

    try {
      return returnId ? await queryAndReturnInsertId(sql) : null;
    } catch (err) {
      logger.log('Query', sql);
      throw err;
    }
  }

  //#endregion

  //#region update 子句

  private keyValueToUpdateSql(keys : string[], sqlValues : string[]) {
    let str = '', v = '';
    for(let i = 0, c = keys.length; i < c; i++) {
      v = sqlValues[i];
      if(i > 0) str += ',';
      str += `${QuerySelectField.wrapKey(keys[i])}=${v}`;
    }
    return str;
  }

  /**
   * 更新查询器指定字段的记录
   * @param param 要更新的字段和值
   */
  public async update(param : O|QueryData, acceptFields : string[] = []) {
    let whereCondition = this.makeWhereCondition();
    let limitCondition = this.makeLimitCondition();
    let { sqlKeys, sqlValues } = this.solveQueryData(param, acceptFields);
    let sql = `UPDATE \`${this.tableName}\` SET ${this.keyValueToUpdateSql(sqlKeys, sqlValues)}`;

    if(whereCondition !== '') sql += ' ' + whereCondition;
    if(limitCondition !== '') sql += ' ' + limitCondition;
    
    if(this.enableLogSQL) logger.log('Query', sql);

    try {
      return await query(sql);
    } catch(err) {
      logger.log('Query', sql);
      throw err;
    }
  }

  //#endregion

  //#region delete 子句

  /**
   * 删除当前查询器指定条件的记录
   */
  public async delete() {
    let whereCondition = this.makeWhereCondition();
    let sql = `DELETE FROM \`${this.tableName}\` ${whereCondition}`;
    
    if(this.enableLogSQL) logger.log('Query', sql);
    try {
      await query(sql);
    } catch(err) {
      logger.log('Query', sql);
      throw err;
    }
  }

  //#endregion

  //#region get 子句

  private makeCondition() {
    let whereCondition = this.makeWhereCondition();
    let orderByCondition = this.makeOrderByCondition();
    let groupByCondition = this.makeGroupByCondition();
    let limitCondition = this.makeLimitCondition();

    return { whereCondition, orderByCondition, groupByCondition, limitCondition }
  }
  private makeSelectSql(sql: string, whereCondition: string, orderByCondition: string, groupByCondition: string, limitCondition: string, limitOne = false) {
    if(this.leftJoinTable) sql +=  ' ' + this.makeLeftJoinConditions();
    if(whereCondition !== '') sql += ' ' + whereCondition;  
    if(groupByCondition !== '') sql += ' ' + groupByCondition;
    if(orderByCondition !== '') sql += ' ' + orderByCondition;
    if(limitCondition !== '') sql += ' ' + limitCondition;
    else if(limitOne) sql += ' LIMIT 1';
    return sql;
  }

  private addDistinct = false;

  /**
   * 强制 SELECT 加上 DISTINCT
   */
  public distinct() {
    this.addDistinct = true;
    this.lastSelectCondition = '';
  }

  private toModel(model: NewCommonModel<any>|undefined, p: any) {
    return model && p ? new model().fromServerSide(p) : p;
  }

  /**
   * 进行查询获取数据
   * @returns Promise 成功则返回数据数组
   */
  public async get<T = O>(model: ValidCommonModel<T> = this.tableSetCommonModel as any) {
    let { whereCondition, orderByCondition, groupByCondition, limitCondition } = this.makeCondition();
    let selectCondition = this.makeSelectCondition();
    let sql = this.makeSelectSql(
      this.disableSelectCondition ? selectCondition : `SELECT ${selectCondition} FROM \`${this.tableName}\``, 
      whereCondition, orderByCondition, groupByCondition, limitCondition);
    
    if(this.enableLogSQL) logger.log('Query', sql);
    try {
      const results = await query(sql);
      return results.map(p => this.toModel(model, p)) as T[];
    } catch(err) {
      logger.log('Query', sql);
      throw err;
    }
  }
  /**
   * 从数据表中获取单行数据
   * @returns Promise 成功则返回第一条数据
   */
  public async first<T = O>(model: ValidCommonModel<T> = this.tableSetCommonModel as any) {
    let { whereCondition, orderByCondition, groupByCondition, limitCondition } = this.makeCondition();
    let selectCondition = this.makeSelectCondition(); 
    let sql = this.makeSelectSql(
      this.disableSelectCondition ? selectCondition : `SELECT ${selectCondition} FROM \`${this.tableName}\``,  
      whereCondition, orderByCondition, groupByCondition, limitCondition, true);
    
    if(this.enableLogSQL) logger.log('Query', sql);
    try {
      const result = (await query(sql))?.[0];
      return this.toModel(model, result) as T;
    } catch(err) {
      logger.log('Query', sql);
      throw err;
    }
  }
  /**
   * 从记录中获取单个字段的值。
   * @param field 字段
   */
  public async value<T extends QueryResult>(field : string) {
    let { whereCondition, orderByCondition, groupByCondition, limitCondition } = this.makeCondition();
    let sql = this.makeSelectSql(`SELECT \`${field}\` FROM \`${this.tableName}\``, 
      whereCondition, orderByCondition, groupByCondition, limitCondition);
    
    if(this.enableLogSQL) logger.log('Query', sql);
    try {
      const result = (await query(sql))?.[0];
      return result ? result[field] as T : null;
    } catch(err) {
      logger.log('Query', sql);
      throw err;
    }
  }
  /**
   * 如果你想获取单列数据的集合。
   * @param fields 字段
   */
  public async pluck(...fields : string[]) {
    let { whereCondition, orderByCondition, groupByCondition, limitCondition } = this.makeCondition();
    let sql = this.makeSelectSql(`SELECT \`${this.solveFields(fields)}\` FROM \`${this.tableName}\``, 
      whereCondition, orderByCondition, groupByCondition, limitCondition);

    if(this.enableLogSQL) logger.log('Query', sql);   
    try {
      const results = await query(sql);
      if(fields.length === 1) {
        let field = fields[0];
        let result : any[] = [];
        for(let i = 0; i < results.length; i++)
          result.push(results[i][field]);
        return result;
      } else {
        let result : {[index: string]:any[]} = {};
        fields.forEach((field) => {
          let arr : any[] = [];
          for(let i = 0; i < results.length; i++)
            arr.push(results[i][field]);
          result[field] = arr;
        })
        return result;
      }
    } catch(err) {
      logger.log('Query', sql);
      throw err;
    }
  }

  /**
   * 禁用自动Select
   * @returns 
   */
  public disableAutoSelectCondition() {
    this.disableSelectCondition = true;
    return this;
  }
  /**
   * 启用自动Select
   * @returns 
   */
  public enableAutoSelectCondition() {
    this.disableSelectCondition = false;
    return this;
  }

  /**
   * 进行分页查询数据
   * @param pageNumber 页数，从1开始
   * @param pageSize 页大小
   */
  public async paginate<T = O>(pageNumber : number, pageSize : number, model: ValidCommonModel<T> = this.tableSetCommonModel as any) {
    let { whereCondition, orderByCondition, groupByCondition } = this.makeCondition();
    let selectCondition = this.makeSelectCondition();
    let sql = this.makeSelectSql(this.disableSelectCondition ? selectCondition : `SELECT ${selectCondition} FROM \`${this.tableName}\``, 
      whereCondition, orderByCondition, groupByCondition, `LIMIT ${(pageNumber-1)*pageSize},${pageSize}`);
    
    if(this.enableLogSQL) logger.log('Query', sql);
    try {
      const results = await query(sql);
      const countSql = `SELECT COUNT(1) AS count FROM \`${this.tableName}\` ${whereCondition}`;
      const v = (await query(countSql))?.[0];
      return new CommonPageResult<T>(model, results, pageNumber, pageSize, v.count);
    } catch(err) {
      logger.log('Query', sql);
      throw err;
    }
  }

  //#endregion

  //#region 聚合子句

  private async commonAggregate(field : string, fun : string, distinct : boolean) {
    let { whereCondition, orderByCondition, groupByCondition, limitCondition } = this.makeCondition();
    let sql = this.makeSelectSql(`SELECT ${fun}(${distinct?'DISTINCT ':''}${field}) AS val FROM \`${this.tableName}\``, 
      whereCondition, orderByCondition, groupByCondition, limitCondition);
    
    if(this.enableLogSQL) logger.log('Query', sql); 
    try {
      const results = await query(sql);
      return results.length > 0 ? results[0].val : null;
    } catch(err) {
      logger.log('Query', sql);
      throw err;
    }
  }

  /**
   * 统计当前查询记录数
   */
  public count(field = '1', distinct = false)  { return this.commonAggregate(field, 'COUNT', distinct); }
  public max(field : string, distinct = false)  { return this.commonAggregate(field, 'MAX', distinct); }
  public min(field : string, distinct = false) { return this.commonAggregate(field, 'MIN', distinct); }
  public avg(field : string, distinct = false) { return this.commonAggregate(field, 'AVG', distinct); }
  public sum(field : string, distinct = false) { return this.commonAggregate(field, 'SUM', distinct); }

  /**
   * 查询当前的构造器是否存在指定记录
   * @returns 
   */
  public async exists() {
    let { whereCondition, orderByCondition, groupByCondition, limitCondition } = this.makeCondition();
    let sql = this.makeSelectSql(`SELECT COUNT(1) AS val FROM \`${this.tableName}\``, 
      whereCondition, orderByCondition, groupByCondition, limitCondition);
    
    if(this.enableLogSQL) logger.log('Query', sql); 
    try {
      const results = await query(sql);
      return results.length > 0 ? (results[0].val > 0) : false;
    } catch(err) {
      logger.log('Query', sql);
      throw err;
    }
  }
  /**
   * 查询当前的构造器是否不存在指定记录
   * @returns 
   */
  public async doesntExist() {
    let { whereCondition, orderByCondition, groupByCondition, limitCondition } = this.makeCondition();
    let sql = this.makeSelectSql(`SELECT COUNT(1) AS val FROM \`${this.tableName}\``, 
      whereCondition, orderByCondition, groupByCondition, limitCondition);
    
    if(this.enableLogSQL) logger.log('Query', sql); 
    try {
      const results = await query(sql);
      return results.length > 0 ? (results[0].val == 0) : false;
    } catch(err) {
      logger.log('Query', sql);
      throw err;
    }
  }

  //#endregion

  //#region select 子句

  private selectFields = new Array<QuerySelectField>();
  private lastSelectCondition = '';
  private disableSelectCondition = false;

  private makeSelectCondition() {
    if(this.lastSelectCondition === '') {
      let anyNonRawCondition = false;
      for(let i = 0; i < this.selectFields.length; i++) {
        if(!this.selectFields[i].isRaw) anyNonRawCondition = true;
      }
      if(this.selectFields.length === 0) return `*`;

      let ols = this.selectFields.join(',');
      this.lastSelectCondition = 
        (this.addDistinct ? 'DISTINCT ' : '') + 
        ((this.disableSelectCondition || anyNonRawCondition) ? '' : `*${(ols !== '' ? ',' : '')}`) + 
        ols;
    }
    return this.lastSelectCondition;
  }

  /**
   * 清空当前查询器上的select条件
   */
  public selectClear() {
    this.selectFields = [];
    this.lastSelectCondition = '';
  }
  /**
   * 添加select字段
   * @param args 字段
   */
  public select(...args : any[]) { args.forEach((a) => this.selectFields.push(new QuerySelectField(a))); return this; }
  /**
   * 添加原生select语句
   * @param args 语句
   */
  public selectRaw(...args : any[])  { args.forEach((a) => this.selectFields.push(new QuerySelectField(a, true))); return this; }

  //#endregion

  //#region where 子句

  private whereConditions = new Array<QueryWhere>();
  private lastWhereCondition = '';

  private clearWhereCondition() {
    if(this.lastWhereCondition !== '') 
      this.lastWhereCondition = '';
  }
  private QueryWhereToSQL(params: QueryWhere)  {
    if(params.valueIsRawSql)
      return ''+params.value;
    else {
      switch(params.operator) {
        case '<':
        case '<=':
        case '=':
        case '!=':
        case '<>':
        case '>':
        case '>=':
          return `${QuerySelectField.wrapKey(params.field)}${params.not?' NOT ':''}${params.operator}` + (params.valueIsField ? `${QuerySelectField.wrapKey(params.value)}` : `${this.solveQueryVar(params.value)}`);
        case 'like':
          return `${QuerySelectField.wrapKey(params.field)} ${params.not?'NOT ':''}LIKE ${this.solveQueryVar(params.value)}`;
        case 'between':
          return `${QuerySelectField.wrapKey(params.field)} ${params.not?'NOT ':''}BETWEEN ${this.solveQueryVar(params.value[0])} AND ${this.solveQueryVar(params.value[1])}`;
        case 'in':
          return `${QuerySelectField.wrapKey(params.field)} ${params.not?'NOT ':''}IN (${this.solveQueryArrayData(params.value).join(',')})`;
        case 'is null':
          return `${QuerySelectField.wrapKey(params.field)} IS NULL`;
        case 'not null':
          return `${QuerySelectField.wrapKey(params.field)} IS NOT NULL`;
      }
    }
    return '';
  };
  private makeWhereCondition() {
    if(this.lastWhereCondition !== '') 
      return this.lastWhereCondition;
    if(this.whereConditions.length === 0)
      return '';

    let loopForConditions = (whereConditions : QueryWhere[]) => {
      let index = 0;
      let base = '';

      whereConditions.forEach((condition) => {

        let sql : string;
        if(condition.valueChildren) 
          sql = `(${loopForConditions(condition.valueChildren)}`;
        else 
          sql = this.QueryWhereToSQL(condition);

        if(index == 0) base += ` ${sql}`;
        else base += ` ${condition.condition} ${sql}`;

        index++;
      });

      return base;
    }
    this.lastWhereCondition = 'WHERE' + loopForConditions(this.whereConditions);
    return this.lastWhereCondition;
  }

  /**
   * 清空当前查询器上的where条件
   */
  public whereClear() {
    this.whereConditions = [];
    this.lastWhereCondition = '';
  }

  /**
   * where条件语句
   * @param field 对应字段
   * @param valueOrOperator 可以是值；或当第三个参数填写，则改参数作为比较运算符
   * @param valueSecond 字段的值
   */
  public where(field : string | QueryWhereArg[] | QuerySubWhereCallback, valueOrOperator ?: QueryOperator | QueryAcceptValue, valueSecond ?: QueryAcceptValue, condition : QueryCondition = 'and', isCol = false) {
    if(typeof field === 'string') {
      this.whereConditions.push({
        condition: condition,
        operator: valueSecond ? valueOrOperator as QueryOperator : '=',
        value: valueSecond ? valueSecond : valueOrOperator,
        valueIsField: isCol,
        field: field,
        not: false,
      });
    } else if(typeof field === 'function') {
      let old = this.whereConditions;
      let valueChildren = [] as QueryWhere[];
      this.whereConditions.push({
        condition: condition,
        operator: '',
        value: null,
        valueIsField: isCol,
        valueChildren: valueChildren,
        field: '',
        not: false,
      });
      this.whereConditions = valueChildren;//换成指定子的数组
      field(this);//调用回调
      this.whereConditions = old;//换回来
    } else {
      field.forEach((v) => {
        let valueSecond = v.valueSecond;
        let valueOrOperator = v.valueOrOperator;
        this.whereConditions.push({
          condition: condition,
          operator: valueSecond ? valueOrOperator as QueryOperator : '=',
          value: valueSecond ? valueSecond : valueOrOperator,
          field: v.field,
          valueIsField: isCol,
          not: false,
        });
      })
    }
    this.clearWhereCondition();
    return this;
  }
  /**
   * where条件语句但是使用or与前方的where进行拼接
   * @param field 对应字段
   * @param valueOrOperator 可以是值；或当第三个参数填写，则改参数作为比较运算符
   * @param valueSecond 字段的值
   */
  public orWhere(field : string | QueryWhereArg[] | QuerySubWhereCallback, valueOrOperator ?: QueryOperator | QueryAcceptValue, valueSecond ?: QueryAcceptValue) {
    return this.where(field, valueOrOperator, valueSecond, 'or');
  }

  /**
   * where条件语句,对比两个字段
   * @param field 对应字段
   * @param field2OrOperator 可以是值；或当第三个参数填写，则改参数作为比较运算符
   * @param field2 字段的值
   */
  public whereColumn(field : string | QueryWhereArg[], field2OrOperator ?: string | QueryOperator, field2 ?: string, condition : QueryCondition = 'and') {
    return this.where(field, field2OrOperator, field2, condition, true);
  }

  /**
   * 注入原生 where 子句
   * @param sql SQL子句。不需要加or或者and
   */
  public whereRaw(sql : string, condition : QueryCondition = 'and') {
    this.whereConditions.push({
      condition: condition,
      operator: '',
      value: sql,
      valueIsRawSql: true,
      field: '',
      not: false,
    });
    this.clearWhereCondition();
    return this;
  }
  /**
   * 注入原生 where 子句（OR连接）
   * @param sql SQL子句。不需要加or或者and
   */
  public orWhereRaw(sql : string) {
    return this.whereRaw(sql, 'or');
  }

  /**
   * 添加某字段值是否包含在一个数组中的条件
   * @param field 字段
   * @param values 可能的值
   */
  public whereIn(field : string, values : any[], condition : QueryCondition = 'and', not = false) {
    if(!Array.isArray(values) || values.length === 0)
      return this;
    this.whereConditions.push({
      condition: condition,
      operator: 'in',
      value: values,
      field: field,
      not: not,
    });
    this.clearWhereCondition();
    return this;
  }
  /**
   * 添加某字段值是否不包含在一个数组中的条件
   * @param field 字段
   * @param values 可能的值
   */
  public whereNotIn(field : string, values : any[], condition : QueryCondition = 'and') {
    return this.whereIn(field, values, condition, true);
  }

  /**
   * 字段为空条件
   * @param field 字段
   */
  public whereNull(field : string, condition : QueryCondition = 'and') {
    this.whereConditions.push({
      condition: condition,
      operator: 'is null',
      value: null,
      field: field,
      not: false,
    });
    this.clearWhereCondition();
    return this;
  }
  /**
   * 字段非空条件
   * @param field 字段
   */
  public whereNotNull(field : string, condition : QueryCondition = 'and') {
    this.whereConditions.push({
      condition: condition,
      operator: 'not null',
      value: null,
      field: field,
      not: false,
    });
    return this;
  }

  /**
   * 字段值在某个区间
   * @param field 字段
   * @param valueBetween 某个区间 [x,y]
   */
  public whereBetween(field : string, valueBetween : any[], condition : QueryCondition = 'and', not = false) {
    this.whereConditions.push({
      condition: condition,
      operator: 'between',
      value: valueBetween,
      field: field,
      not: not,
    });
    this.clearWhereCondition();
    return this;
  }
  /**
   * 字段值不在某个区间
   * @param field 字段
   * @param valueBetween 某个区间
   */
  public whereNotBetween(field : string, valueBetween : any[], condition : QueryCondition = 'and') {
    return this.whereBetween(field, valueBetween, condition, true);
  }
   
  //#endregion

  //#region when

  /**
   * 在给定值在请求中存在的情况下才执行某些查询。
   * @param condition 给定值
   * @param trueCallback 给定值为真时执行回调
   * @param falseCallback 给定值为假时执行回调
   */
  public when(condition : any, trueCallback : QuerySubWhereCallback, falseCallback ?: QuerySubWhereCallback) {
    if(condition) trueCallback(this);
    else if(typeof falseCallback === 'function') falseCallback(this);
  }

  //#endregion

  //#region limit 语句

  private limitCondition = -1;
  private offsetCondition = -1;
  private lastLimitCondition = '';

  private clearLimitCondition() {
    if(this.lastLimitCondition !== '') 
      this.lastLimitCondition = '';
  }
  private makeLimitCondition() {
    if(this.lastLimitCondition === '') {
      if(this.offsetCondition >= 0) this.lastLimitCondition = `LIMIT ${this.offsetCondition},${this.limitCondition}`;
      else if(this.limitCondition >= 0) this.lastLimitCondition = `LIMIT ${this.limitCondition}`;
      else this.lastLimitCondition = '';
    }
    return this.lastLimitCondition;
  }

  /**
   * limit 条件语句
   * @param sizeOrOffset 大小。如果第二个参数填写，则当前参数作为offset。
   * @param size 大小
   */
  public limit(sizeOrOffset : number, size ?: number) {
    if(typeof size === 'number') {
      this.offsetCondition = sizeOrOffset;
      this.limitCondition = size;
    } else this.limitCondition = sizeOrOffset;
    this.clearLimitCondition();
    return this;
  }
  /**
   * offset 条件语句
   * @param size 大小
   */
  public offset(size : number) {
    this.offsetCondition = size;
    this.clearLimitCondition();
    
    return this;
  }

  //#endregion

  //#region orderBy、groupBy 语句

  private orderByConditions = new Array<QueryOrderBy>();
  private groupByConditions = new Array<string>();

  private lastOrderByCondition = '';
  private lastGroupByCondition = '';

  private clearGroupByCondition() { if(this.lastGroupByCondition !== '') this.lastGroupByCondition = ''; }
  private makeGroupByCondition() {
    if(this.lastGroupByCondition !== '') return this.lastGroupByCondition;
    if(this.groupByConditions.length === 0) return '';

    let base = 'GROUP BY';
    for(let i = 0, c = this.orderByConditions.length; i < c; i++) 
      base += `${i > 0?',':''}${QuerySelectField.wrapKey(this.groupByConditions[i])}`;

    this.lastGroupByCondition = base;
    return this.lastGroupByCondition;
  }
  private clearOrderByCondition() { if(this.lastOrderByCondition !== '') this.lastOrderByCondition = ''; }
  private makeOrderByCondition() {
    if(this.lastOrderByCondition !== '') return this.lastOrderByCondition;
    if(this.orderByConditions.length === 0) return '';

    let base = 'ORDER BY ';

    for(let i = 0, c = this.orderByConditions.length; i < c; i++) {
      let condition = this.orderByConditions[i];
      if(condition.isRand) {
        base += 'RAND()';
        break;
      }else {
        if(i > 0) base += ',';
        base += `${QuerySelectField.wrapKey(condition.field)} ${condition.direction.toUpperCase()}`;
      }
    }

    this.lastOrderByCondition = base;
    return this.lastOrderByCondition;
  }

  /**
   * orderBy语句
   * @param field 字段
   * @param direction 排序方式
   */
  public orderBy(field : string, direction : 'asc'|'desc' = 'asc') {
    this.orderByConditions.push({ field: field, direction: direction, isRand: false });
    this.clearOrderByCondition();
    return this;
  }
  /**
   * groupBy rand() 语句，用于随机排序
   */
  public inRandomOrder() {
    this.orderByConditions = [];
    this.orderByConditions.push({ field: '', direction: 'asc', isRand: true });
    this.clearOrderByCondition();
    return this;
  }
  /**
   * groupBy 语句
   * @param field 字段
   */
  public groupBy(field : string) {
    this.groupByConditions.push(field);
    this.clearGroupByCondition();
    return this;
  }

  //#endregion

  //#region join子句

  private leftJoinTable = '';
  private leftJoinConditions : Record<string, string> = {};

  private makeLeftJoinConditions() {
    if (this.leftJoinTable) {

      let leftJoinConstr = ''; 
      for (const key in this.leftJoinConditions) {
        if (Object.prototype.hasOwnProperty.call(this.leftJoinConditions, key)) {
          leftJoinConstr += `${QuerySelectField.wrapKey(key)} = ${QuerySelectField.wrapKey(this.leftJoinConditions[key])}`
        }
      }
      return `LEFT JOIN \`${this.leftJoinTable}\`` + (leftJoinConstr ? (' ON ' + leftJoinConstr) : '');
    }
    return '';
  }

  public leftJoin(tableName : string, conditions: Record<string, string>) {
    this.leftJoinTable = tableName;
    this.leftJoinConditions = conditions;
    return this;
  }

  //#endregion

}