Optional query with a lot of parameter

I must have build a query from my store items. my store items have 10 field . I just let customer search in my item’s optional.
for example first one maybe want to filter on field1 , second one maybe want to filter on field1 and field2,third one maybe want to filter on field6 and field8 and filde9,… .

how can i make a query more short more efficient for this ?

note 1: I don’t want to use Raw method because of its vulnerability.

note 2: i see some answers in link 1 and link 2 but I think first one can not be use for
condition like : where('field1','>=','somevalue') or where('field2','like','%somevalue%') or any sort of condition with some complexity and second one has more “if” chaining and i want to have shorter than this if it’s possible

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can do this in several ways depending on the syntax you’d like. One possible way is to use a separation symbol to pass multiple arguments:

/api/user?where[]=user_id|>=|5
/api/user?where[]=user_id|2
/api/user?where[]=user_id|2&where[]=status|activated

This simply allows you to pass multiple where options where the pipe | operator separates the arguments. Note that this may cause issues if you want the pipe character to be available as search argument for instance.

Then you could simply parse this url into your query like so:

foreach($request->get('where') as $i => $values) {
    $values = explode('|', $values);
    // Allow the third argument to be exchanged with the second one,
    // similar to how the Laravel `where()` method works.
    $query->where($values[0], $values[1], $values[2] ?? null);
}

Optionally, you can add a validation method so that the syntax will be properly checked beforehand. You can add this snippet to some boot() method of a service provider:

IlluminateSupportFacadesValidator::extend('query_where', function($attribute, $value) {
    // Require minimum length of 2 parameters.
    return count(explode('|', $value)) >= 2;
});

Next, your validation in your controller action would look like this:

$this->validate($request, [
    'where' => 'nullable|array',
    'where.*' => 'query_where',
]);

The possibilities are endless.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0