Pagination stops working, when using custom setFilteringFunc()

[TUNER88]

I try to create like filter on 2 fields:

        $grid = new Grid(
            (new GridConfig)
                ->setName('file_grid')
                ->setPageSize(10)
                ->setTemplate('nayjest.grids.default')
                ->setDataProvider(new EloquentDataProvider($query))
                ->setColumns([
                    (new FieldConfig)
                        ->setName('name')
                        ->setLabel(Lang::get('models/file.name'))
                        ->setSortable(true)
                        ->addFilter(
                            (new FilterConfig)
                                ->setName('name')
                                ->setFilteringFunc(function ($val, EloquentDataProvider $dp) {
                                    $builder = $dp->getBuilder();
                                    $builder->where('name', 'LIKE', '%'.$val.'%')
                                        ->orWhere('description', 'LIKE', '%'.$val.'%');
                                })
                        )
                ])
                ->setComponents([
                    (new THead)
                        ->setComponents([
                            (new ColumnHeadersRow),
                            (new FiltersRow)
                        ])
                    ,
                    (new TFoot)
                        ->setComponents([
                            (new OneCellRow)
                                ->setComponents([
                                    new Pager,
                                    (new HtmlTag)
                                        ->setAttributes(['class' => 'pull-right'])
                                        ->addComponent(new ShowingRecords)
                                    ,
                                ]),
                        ])
                ])
        );

First result page displays valid results, second page lose my search string and return results without filter. Any ideas?

[Nayjest]

Hi! Starnge, in demo (without setFilteringFunc()) it works fine: http://grids-demo.herokuapp.com/demo/example4?example_grid4%5Bfilters%5D%5Bemail-like%5D=.com&example_grid4%5Bpage%5D=2

Code also looks fine and I use this functionality same way too.

• Is GET-parameter file_grid[name] preserved when you navigating to next page? If it's not preserved, problem is somewhere in grids code.
• Check your SQL query modified by grid filters: (var_dump(\DB::getQueryLog()) & die() after $grid->render() or use barryvdh/laravel-debugbar package to always see DB queries in debug panel). If you use some kind of complex queries (grouping or sub-queries), there is possibility that "where" conditions will be added not to proper place.
• Check that you use proper pagination component: \Nayjest\Grids\Components\Laravel5\Pager for L5 and \Nayjest\Grids\Components\Pager for L4

[TUNER88]

First result page is fine, page number and item count looks also fine. Pagination links on first result page contain search string as GET parameter. http://host.tld/categories?file_grid[filters][name-=]=as&file_grid[page]=2

When I navigate to the next page, the filter field does not contain my search string anymore. I get unfiltered results from second page, page count is equal to unfiltered page count. But the next-page links still contains my search string: http://host.tld/categories/?file_grid[filters]=]=as&file_grid[page]=3

As soon as i change the filter from:

(new FilterConfig)
   ->setName('name')
   ->setFilteringFunc(function ($val, EloquentDataProvider $dp) {
    $builder = $dp->getBuilder();
    $builder->where('name', 'LIKE', '%'.$val.'%')
      ->orWhere('description', 'LIKE', '%'.$val.'%');
   })

to

(new FilterConfig)
   ->setName('name')
   ->setOperator(FilterConfig::OPERATOR_LIKE)

everything works fine.

DB Queries

Query on the first page looks fine:

select * from `files` where `category_id` in ('11', '166') and `name` LIKE '%as%' or `description` LIKE '%as%' order by `updated_at` desc limit 10 offset 0

Query on the second page does not contain my search string anymore:

select * from `files` where `category_id` in ('11', '166') order by `updated_at` desc limit 10 offset 10

Pager class use Nayjest\Grids\Components\Laravel5\Pager; should also be fine for Laravel 5.1

URLs

Urls seems to be broken?

First page (on Filter POST request): http://host.tld/categories?file_grid%5Bfilters%5D%5Bname-%3D%5D=as Page 2: http://host.tld/categories?file_grid[filters][name-=]=as&file_grid[page]=2 Page 1 link on Page 2: http://host.tld/categories?file_grid[filters]=]=as&file_grid[page]=1

[Nayjest]

Looks like problem is somewhere in filter name. Operator name used in name of get-parameter, so try to specify both filteringFunc and operator (filteringFunc always has higher priority when modifying query).

(new FilterConfig)
   ->setName('name')
   ->setOperator(FilterConfig::OPERATOR_LIKE)
   ->setFilteringFunc(function ($val, EloquentDataProvider $dp) {
            $builder = $dp->getBuilder();
            $builder->where('name', 'LIKE', '%'.$val.'%')
                ->orWhere('description', 'LIKE', '%'.$val.'%');
   })

Of course it's a dirty hack, but I'm not sure that I can solve this problem soon (currently I even have no time to reproduce it), so better to try something like that.

Please give me know if specifying both filteringFunc and operator helped.

[TUNER88]

@Nayjest thx for your suggestion, works like a charm for this filter!

seems to be generic problem, now i have same problem when I add other filters, but this trick does not work anymore:

->addFilter(
    (new SelectFilterConfig)
        ->setName('type')
        ->setOperator(FilterConfig::OPERATOR_EQ)
        ->setSubmittedOnChange(true)
        ->setOptions(array_combine(FileType::keys(), FileType::values())))

As soon I add this filter, all filters loosing their values when I navigating to next page.

[Nayjest]

Hi! Currently I still have no time to researh deeper. But I have another suggestion: try to use custom filter names ('type_filter') that's not equeal to column names (It's not used when you use custom filtering function, but looks like bug is related to filter name) + custom filtering function.

Also, please, check this bug in different browsers. I had problems with multidimensional arrays in HTTP GET & specific GET parameter names in Google Chrome before.

[AndreiGapejev]

Hi! Had similar issue and after removing all filters except 'LIKE' pagination works. Again after adding OPERATOR_EQ or OPERATOR_GTE / OPERATOR_LSE pagination stops working. Looks like problem with pager href values being not urlencoded as grid expecting. Checked in Chrome/Firefox.

[wdog]

i've the same problem using operator OPERATOR_EQ this works

pa[park_id-%3D]=34

on the next page the filter is not encoded and this will not work

pa[park_id-=]=34&pa[page]=2

workaround as Nayjest proposed works

  (new SelectFilterConfig)
                ->setOptions($parks)
                /* to be fixed  encode = in %3D*/
                ->setOperator(FilterConfig::OPERATOR_LIKE)
                ->setFilteringFunc(function ($val, EloquentDataProvider $dp) {
                  $builder = $dp->getBuilder();
                  $builder->where('parks.id', '=', $val);
                })

[Nayjest]

Looks like problem is in special characters in GET parameter names (?) GET parameter name = $filter_name . $operator. But in this case it's strange -- why it doesn't reproduces in demo app: http://grids-demo.herokuapp.com/demo/example4

[wdog]

all filters in demo are

FilterConfig::OPERATOR_LIKE

try to change one in

FilterConfig::OPERATOR_EQ 

the issue is raised by all but LIKE i think

[asaf050]

Hey, Looks like the equal sign (=) mess up with URL parameters.

Is there any solution for that?

Thanks

[Nayjest]

Hi!

1. You can rename constants in FilterConfig (FilterConfig::OPERATOR_EQ = 'eq' instead of '=', etc.) & make pull request, I will accept it and community will say you many thanx.
2. If i'm not mistaken, setting filter name via setName() method changes URL parameter name too, check it

[asaf050]

Hey, I've renamed constants in FilterConfig but now those operators doesn't work. You can see changes in my commit https://github.com/asaf050/Grids/commit/91af85577584d3b6b5488ba13c1d21ff62b70dd7

[Nayjest]

This constants is used inside DataProviders to build SQL, so you need to fix that in filter() methods of DataProvider classes.

StyleCI rejects isn't very important. There is too strict rules so just don't pay attention.

[asaf050]

Hey @Nayjest I've created a new pull request https://github.com/Nayjest/Grids/pull/90

[Nayjest]

Thanks to @asaf050 for contribution! Released in v0.9.8

I will appreciate any feedback regarding this issue fix becouse unfortunately I had no time to add tests. @TUNER88, @AndreiGapejev, @wdog please give me know if v0.9.8 works fine.

[Nayjest]

Upd: some my reports was broken after this fix, trying to locate the problem now.

[Nayjest]

Upd: custom operations functionality was broken by this fix in v0.9.8, fixed in v 0.9.10

[mohsinabbas]

Hi @Nayjest, We have implemented Nayjest grid, having issue on pagination URL it throw params in query string ?shop_forms_listing%255Bpage%255D=3 but it should be: ?shop_forms_listing%5Bpage%5D=2 let me know what is the issue with the pagination urls