How to do dynamic Querys

posted 10 years ago

Hey artisans :)

I would like to have a dynamic query-builder but don't no how to do it :/

I have a database with products and i am using paginator. I wanna do 2 filters for the products (brand and price). When i wanna show all products i just do

Article::->paginate(20);

I would like to have a function that sees if the filters are actice, so if the brand is set it automaticly adds a where clause

Article::->where('brand', '=', 'abc')->paginate(20);

i could make a function that prooves which filters are active (maybe a switch case) but when i think about having like 5 filters this would be a lot of code and i am sure there is a better way.

i would like to have something like

function($filter1, filter2){ Article:: . $filter1 . -> . $filter2 . ->paginate(20); }

but of course it doesn't work ^^. Does anyone know a good way for this issue?? Thank you guys :)

Last updated 2 years ago.

torchsk

replied 10 years ago

Well that is a good question, and I want to hear some ideas too. Right now I am doing it this way. The code is heavily modified to serve only this thread so there may be errors.

I am doing it by ajax, and the filters in the input are json-encoded

public function search()
{
$filters=json_decode(Input::get('filters'),true);

return Article::where(function($query) use ($filters){
  foreach ((array)$filters as $key=>$value){
    if ($value){
      $query->where($key, '=', $value);
    }
   }
 })->paginate(20);
}

Clarification:

The (array) in

(array)$filters

is there for the case when there is no filter available

The

if ($value)

is there for the case when the filter is there but has no values

Last updated 2 years ago.

epik38

replied 10 years ago

I'll try to explain what I did in my project. There is certainely a better solution but it can give you a starting point.

First, I get all input from the $_GET

$input = Input::all();

Nothing too hard here. Then I build a new QueryBuilder.

$articles = Article::where( function($subquery) use($input) { /* Here goes your filter logic */ } )->paginate(20);

Note that you have to use your $input to access it in the anonymous function.

Now, you have to fill up the anonymous function to set (Or not) your filters. The following code goes into the anonymous function. It replaces the comment

if( isset($input['filter1']) )
{
  $subquery->where('filter1', $input['filter1']);
}

if( isset($input['filter2']) )
{
  $subquery->where('filter2', $input['filter2']);
}

...

Note that you don't need to call ->get() on the subquery, Eloquent does it for you.

###Important point !

I didnt try this code, there is a possibility you get an error if there is no filter at all.

I hope it helps you ;)

Last updated 2 years ago.

Moderators