Transforming API query string to eloquent where query

Hello guys/girls. I solved one problem but not fully satisfied with it. I would love to make it a one liner but I cannot achieve this alone.

From browser i'm receiving this ?foo[eq]=bar&department[eq]=06&date[eq]=2021-13-12

Illuminate\Http\Request $request->query() will transform it to ['foo' => [ 'eq' => 'bar' ], 'department' => [ 'eq' => '06 ], 'date' => ['eq' => '2021-13-12'] ]

what I want to send directly to eloquent is [[ 'departement_id', '=', '06' ], ['created_at', '=', '2021-13-12']]

here is the code.

class WhatEver {
    protected $allowedParams = [
        'department' => ['eq'],
        'date' => ['eq'],
    ];

	protected $columnMap = [
        'department' => 'department_id',
        'date' => 'created_at',
    ];
/**
     * This will filter query string parameters and transform them to a where query for eloquent
     * in browser        ?foo[eq]=bar&department[eq]=06&date[eq]=2021-13-12
     * request->query()  ['foo' => [ 'eq' => 'bar' ], 'department' => [ 'eq' => '06 ], 'date' => ['eq' => '2021-13-12'] ]
     * eloquent          [[ 'departement_id', '=', '06' ], ['created_at', '=', '2021-13-12']].
     *
     * @todo would love to make it a one liner
     * return collect(...)->toArray(); but I fail to achieve this.
     */
    public function transform(): array
    {
        $result = collect(); // <==== first "bad" step

        collect($this->queryParams)
            ->only(array_keys($this->allowedParams)) // keeping only allowed params
            ->each(
                fn ($queryParam, $column) => $result->push( // <==== second "bad" step
                    collect($queryParam)
                        ->only($this->allowedParams[$column]) // keeping only allowed operators
                        ->mapWithKeys(
                            fn ($value, $operator) => [
                                $this->columnMap[$column],
                                QueryOperators::from($operator)->value(),
                                $value,
                            ]
                        )
                )
            )
        ;

        return $result->toArray();
    }
}

If you can help I will sing your name to the moon.

LaryAI

3 years ago

Level 58

Here's a one-liner solution to transform the query parameters to an Eloquent where query:

$result = collect($request->query())
    ->only(array_keys($allowedParams))
    ->flatMap(function ($queryParam, $column) use ($columnMap) {
        return collect($queryParam)
            ->only($allowedParams[$column])
            ->map(function ($value, $operator) use ($columnMap, $column) {
                return [$columnMap[$column], QueryOperators::from($operator)->value(), $value];
            });
    })
    ->values()
    ->toArray();

This code does the following:

Collects the query parameters from the request and keeps only the allowed ones.
Maps each query parameter to an array of Eloquent where clauses.
Flattens the resulting array of arrays into a single array.
Removes any empty arrays from the result.
Returns the result as an array.

Note that this code assumes that the QueryOperators class is defined and that it provides a from method that returns an instance of the appropriate query operator based on the given operator string (e.g. "eq" => "=").

tyteck

3 years ago

Level 8

modified answer of Lary is (unfortunately) only producing

[
  [
    0 => "created_at"
    1 => "="
    2 => "2021-08-06"
  ]
]

losing the department part of the query.

        return collect($this->queryParams)
            ->only(array_keys($this->allowedParams))
            ->flatMap(function ($queryParam, $column) {
                return collect($queryParam)
                    ->only($this->allowedParams[$column])
                    ->map(function ($value, $operator) use ($column) {
                        return [$this->columnMap[$column], QueryOperators::from($operator)->value(), $value];
                    })
                ;
            })
            ->values()
            ->toArray()
        ;

webrobert

3 years ago

Level 51

Not sure less is more here.

If you just transform and filter the data there isn't any feedback for what isn't working.

It seems like you'd be better to do some kind of pipeline...

check that the parameters are valid
check that the operators are valid
check that the value is valid

so you can give feedback when something isn't correct along the way.

1 like

tyteck

3 years ago

Level 8

You probably right, it is a little over engineered and not that readable. At last it's more a personal challenge now.

Please or to participate in this conversation.