Example dinamic querys like loopback with typeorm

custom.ts

import "reflect-metadata";
import { createConnection, getManager } from "typeorm";
import { resolve } from 'path';

var document: any;

const fs = require('fs-extra') // this is no longer necessary
const hbs = require('handlebars');
const path = require('path');

module.exports = function (Custom) {

  // Ficheros comunes    
  var app = require('../../server/server');

  function sqlDinamica(manager, include, tabla) {

    console.log("Pasamos");

    if (include) {
      var arrayInlclude = (!Array.isArray(include)) ? Array(include) : include
      arrayInlclude.forEach(element => {


        if (element.tipo && element.tipo === "left") {
          manager.leftJoin(tabla.concat("." + element.relation), element.relation)
        } else {
          manager.innerJoinAndSelect(tabla.concat("." + element.relation), element.relation)
        }


        // where
        var arrayWhere = Array()
        if (element.where) {
          arrayWhere = !Array.isArray(element.where) ? Array(element.where) : element.where
        }
        arrayWhere.forEach(where => {

          var key = Object.keys(where)[0]
          if (key) {
            if (where[key] && where[key].regexp) {
              var nodo = {}
              nodo[key] = where[key].regexp
              manager.andWhere(element.relation + "." + key + " REGEXP " + ":" + key, nodo)
            } else {
              var clause = where.clausesql;
              delete where.clausesql;
              manager.andWhere(element.relation + "." + key + " " + clause + " " + ":" + key, where)
            }
          }

        });

        // or
        var arrayOr = Array()
        if (element.or) {
          arrayOr = !Array.isArray(element.or) ? Array(element.or) : element.or
        }
        arrayOr.forEach(or => {
          var key = Object.keys(or)[0]
          manager.orWhere(element.relation + "." + key + " = " + ":" + key, or)
        });

        // order
        if (element.order) {
          var arrayOrder = Array()
          arrayOrder = (!Array.isArray(element.order)) ? Array(element.order) : element.order
          arrayOrder.forEach(e => {
            var order = e.split(" ")
            if (e.indexOf(".") > -1) {
              manager.orderBy(order[0].trim(), order[1].trim())
            } else {
              order = e.split(" ")
              manager.orderBy(tabla + "." + order[0].trim(), order[1].trim())
            }
          });
        }


        // Recursividad
        if (element.include) {
          sqlDinamica(manager, element.include, element.relation);
        }

      });
    }

  }


  Custom.custom = function (options, tabla, filter, cb) {
    try {
      console.log("filter");
      console.log(filter);
      var rawSql = false;
      let manager = getManager().getRepository(tabla).createQueryBuilder(tabla)

      if (filter) {
        if (filter.fields) {

          var select = [];

          if (filter.fields.length === 1 && filter.fields[0].count) { // Solo tenemos count sacamos los datos de la tabla padre
            filter.fields.forEach(field => {
              if (field['count']) {
                select.push("COUNT(" + field.count.table + "." + field.count.field + ") AS " + field.count.table + "_count")
                //select.push("COUNT(" + field.table + "." + field.field + ") AS " + field.table + "_count")
                rawSql = true;
              }
              select.push(tabla + ".*")
            });

          } else {
            filter.fields.forEach(field => {
              if (field['count']) {
                select.push("COUNT(" + field.count.table + "." + field.count.field + ") AS " + field.count.table + "_count")
                rawSql = true;
              } else {
                select.push(field.field.table + "." + field.field.field + " AS " + field.field.field)
              }
            });
          }
          manager.select(select)
        }
        if (filter.groupby) {
          var groups = !Array.isArray(filter.groupby) ? Array(filter.groupby) : filter.groupby;
          groups.forEach(group => {
            manager.addGroupBy(group.table + "." + group.field)
          })
        }

        // Base para el where
        manager.where("1 = 1")

        // Dinamica 
        sqlDinamica(manager, filter.include, tabla);


        // wheres de la tabla base
        if (filter.where) {
          var arrayWhere = Array()
          if (filter.where) {
            arrayWhere = !Array.isArray(filter.where) ? Array(filter.where) : filter.where
          }
          arrayWhere.forEach(where => {

            var key = Object.keys(where)[0]
            if (key) {
              if (where[key] && where[key].regexp) {
                var nodo = {}
                nodo[key] = where[key].regexp
                manager.andWhere(tabla + "." + key + " REGEXP " + ":" + key, nodo)
              } else {
                manager.andWhere(tabla + "." + key + " = " + ":" + key, where)
              }
            }

          });
        }

        if (filter.or) {
          var arrayOr = Array()
          if (filter.or) {
            arrayOr = (!Array.isArray(filter.or)) ? Array(filter.or) : filter.or
          }
          arrayOr.forEach(or => {
            var key = Object.keys(or)[0]
            manager.orWhere(tabla + "." + key + " = " + ":" + key, or)
          });
        }

        // order
        if (filter.order) {
          var arrayOrder = Array()
          arrayOrder = (!Array.isArray(filter.order)) ? Array(filter.order) : filter.order
          arrayOrder.forEach(e => {
            var order = e.split(" ")
            if (e.indexOf(".") > -1) { // sin especificar la tabla 
              if ("table_count") { // para ordenar por el propio count 
                manager.orderBy(order[0].replace("table_count.", "").trim(), order[1].trim())
              } else {
                manager.orderBy(order[0].trim(), order[1].trim())
              }

            } else { // especificando la tabla
              order = e.split(" ")
              manager.orderBy(tabla + "." + order[0].trim(), order[1].trim())
            }
          });
        }

        // skip
        if (filter.skip) {

          manager.skip(filter.skip)
        }
        // limit
        if (filter.limit) {
          manager.limit(filter.limit)
        }
      }

      //  devuelve los datos  y un object  del total de registros

      if (rawSql) {
        manager.getRawMany().then(data => {
          cb(null, data);
        }).catch(error => {
          console.log(error);
          cb(null, error);
        })
      } else if (filter && filter.get && filter.get === "datacount") {
        manager.getManyAndCount().then(data => {
          cb(null, data);
        }).catch(error => {
          console.log(error);
          cb(null, error);
        })
      } else if (filter && filter.get && filter.get === "count") {
        manager.getCount().then(data => {
          cb(null, data);
        }).catch(error => {
          console.log(error);
          cb(null, error);
        })
      } else {
        manager.getMany().then(data => {
          cb(null, data);
        }).catch(error => {
          console.log(error);
          cb(null, error);
        })
      }

    } catch (error) {
      console.log(error);
      cb(error);
    }

  };

  Custom.pdf = function (options, req, res, cb) {

    const puppeteer = require('puppeteer');
    req.headers['Content-Disposition'] = 'inline; filename=nombre_fichero.pdf';


    hbs.registerHelper('formatDate', function (options) {
      if (this) {
        if (options) {
          var d = new Date(options);
          let month = String(d.getMonth() + 1);
          let day = String(d.getDate());
          const year = String(d.getFullYear());
          if (month.length < 2) month = '0' + month;
          if (day.length < 2) day = '0' + day;
          return `${day}/${month}/${year}`;
        } else {
          return null;
        }

      }
    });
  }


  Custom.remoteMethod(
    'custom', {
      accepts: [
        {
          "arg": "options",
          "type": "object",
          "http": "optionsFromRequest",

        },
        { arg: 'tabla', type: 'string', required: true },
        { arg: 'filter', type: 'object' }
      ],
      http: {
        path: '/custom/:tabla',
        verb: 'get'
      },
      returns: {
        arg: 'custom',
        type: 'array', root: true
      }
    }
  );

};