Enable raw arguments in query

[ngaspari]

Option to handle request parameters as raw arguments.

For example: api/search/{model}

{ "search": { "remote_relations.remote_model_type": "=contact", "remote_relations.remote_model_id": "=...." }, "group_by": ["year", "month"], "order_by": ["year", "month"], "returns": ["DATE_PART('Year', created_at) AS year", "DATE_PART('Month', created_at) AS month", "COUNT(*) AS total"], "use_raw_arguments": 1 }

[toni4848]

@ngaspari @izibrat Je li nam pametno stavljati raw SQL upit u request body zbog sigurnosnog rizika, težeg održavanja, vežemo se direktno na bazu - neko limitiranje abstrakcije, a možda i smanjena validacija?

Kod je ok.

@ngaspari možeš li ažurirati dokumentaciju (README) da nam se ne izgubi dorada.

[ngaspari]

@ngaspari @izibrat Je li nam pametno stavljati raw SQL upit u request body zbog sigurnosnog rizika, težeg održavanja, vežemo se direktno na bazu - neko limitiranje abstrakcije, a možda i smanjena validacija?

Kod je ok.

@ngaspari možeš li ažurirati dokumentaciju (README) da nam se ne izgubi dorada.

Istina, ovime smo podložni SQL injection-u, te moramo dati funkciju koju baza podržava. Nisam našao neki out-of-the box način da ovo drugačije riješi sa Laravel/Eloquent. Moguće je to malo apstraktnije složiti, naravno (opet moramo pokrivati različite DB opcije onda). npr: "returns": [ ['func' => 'YEAR', 'field' => 'created_at', 'as' => 'year'], ['func' => 'MONTH', 'field' => 'created_at', 'as' => 'month'], ['func' => 'COUNT', 'field' => '*', 'as' => 'total'], ],

Ovo je više bio kao POC - da vidim hoće li raditi, bez mnogo kompliciranja

[nealarec]

@ngaspari Have found a more interesting whom to do this, I added some code to the returns' preprocessor, will public a pr in a couple of days

[nealarec]

You can override the Returns Argument with a custom class implementing this trait

use Asseco\JsonQueryBuilder\Exceptions\JsonQueryBuilderException;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class PGSQLAggregations {
    public static function year($raw) {
        return "EXTRACT(YEAR FROM $raw)";
    }

    public static function month($raw) {
        return "EXTRACT(MONTH FROM $raw)";
    }

    public static function day($raw) {
        return "EXTRACT(DAY FROM $raw)";
    }
}

trait AggregationTrait {
    public Builder $builder;

    protected array $providers = [
        "pgsql" => PGSQLAggregations::class,
    ];

    protected const DB_FUNCTIONS = [
        "avg",
        "count",
        "max",
        "min",
        "sum",
        "distinct",

        "year",
        "month",
        "day",
    ];

    protected function areArgumentsValid(): void {
        foreach ($this->arguments as $argument) {
            $split = explode(":", $argument);
            if (count($split) == 1) {
                continue;
            }
            $column = array_pop($split);
            if (!preg_match("/^[a-zA-Z_][a-zA-Z0-9_]*|\*$/", $column)) {
                throw new JsonQueryBuilderException(
                    "Invalid column name: {$column}."
                );
            }
            if ($invalidFns = array_diff($split, self::DB_FUNCTIONS)) {
                throw new JsonQueryBuilderException(
                    "Invalid function: " . join(",", $invalidFns) . "."
                );
            }
        }
    }

    private function applyAggrs(array $params): string {
        $column = array_pop($params);
        $provider =
            $this->builder->getModel()->connection ??
            config("database.default");

        return array_reduce($params, function ($query, $param) use (
            $column,
            $provider
        ) {
            $stat = $query ?? $column;
            if (
                $this->providers[$provider] &&
                method_exists($this->providers[$provider], $param)
            ) {
                return $this->providers[$provider]::$param($stat);
            }

            return "$param($stat)";
        });
    }

    protected function appendQuery(): void {
        $this->arguments = array_map(function ($argument) {
            if (strpos($argument, ":") === false) {
                return $argument;
            }
            $split = explode(":", $argument);
            $apply = $this->applyAggrs($split);
            $alias = join("_", $split);

            return DB::raw("{$apply} as {$alias}");
        }, $this->arguments);
        parent::appendQuery();
    }
}

[nealarec]

Example of working query

{
  "search": { "close_date": "<>2024-01-01;2024-12-31" },
  "returns": [
    "year:close_date",
    "month:close_date",
    "sum:value",
    "avg:value",
    "count:value",
    "max:year:close_date",
    "min:year:close_date",
    "max:month:close_date",
    "min:month:close_date"
  ],
  "group_by": ["year_close_date", "month_close_date"]
}

Result

[
  {
    "year_close_date": "2024",
    "month_close_date": "3",
    "sum_value": "280990",
    "avg_value": "93663.33333333333",
    "count_value": 3,
    "max_year_close_date": "2024",
    "min_year_close_date": "2024",
    "max_month_close_date": "3",
    "min_month_close_date": "3"
  },
  {
    "year_close_date": "2024",
    "month_close_date": "4",
    "sum_value": "500000",
    "avg_value": "500000",
    "count_value": 1,
    "max_year_close_date": "2024",
    "min_year_close_date": "2024",
    "max_month_close_date": "4",
    "min_month_close_date": "4"
  }
]

[nealarec]

Have added a new PR #55 for to solve this use cases. 🥳