Build SQL Query to Execute from Where/Filter in Loopback4

[sohaibahsan007]

Hello,
I have a situation where I need to get a sum of some columns. I can only do that using a custom execution of SQL query. It will be very helpful if, I can use existing where and filter Params to builder SQL query, so when I query results from the front-end, I can get the sum of columns, count, and result using a single where/filter.

Something that knex.js is trying to do.

For Example, I have tried to do it using knex.js with Postgre.

private getSumByGroupBy(where?: Where<T>, options?: Options): Promise<(T & Relations)[] | null> {
    if(!options?.columns || !options?.groupBy){
      throw new HttpErrors.Forbidden('Please define option with columns and groupBy params');
     }

   try {
      // get schema with table name 
    const schemWithTable = this.getSchemaWithTableName();
    const {sql, bindings} = knex.withSchema(schemWithTable?.schema).from(schemWithTable?.table)
      .column(this.convertColumnsIntoSumAs(options?.columns))
      .select()
      .where(whereFilter(where))
      .groupBy(options?.groupBy?.toString())
      .first()
      .toSQL().toNative();
    // prepare SQL Command
    const sqlCommand = sql?.replace(/"/g, "")?.replace(schemWithTable?.schema, `"${schemWithTable?.schema}"`);
    // execute SQL Command
    return this.execute(sqlCommand, bindings) as Promise<(T & Relations)[]>;
   } catch (error) {
     throw new HttpErrors.InternalServerError(error);
   }
  }