Undefined offset in conjunction with yajra/laravel-datatables

[rappasoft]

Issue

This issue prevents searching on the laravel datatables plugin due to some type of issue with the laravel datatables usage of whereRaw.

Possibly related to: #111

Possible clue:

DataTables::of(User::query()) -> Error
DataTables::of(\DB::table('users')) -> Works

Environment

Laravel Version: 5.6.24
Laravel Model Caching Package Version: 0.2.62
PHP Version: 7.2
Homestead Version: 6
Operating System & Version: MacOS 10.13.5

Stack Trace

[2018-06-03 19:34:22] local.ERROR: ErrorException: Undefined offset: 3 in /PROJECT_PATH/vendor/genealabs/laravel-model-caching/src/CacheKey.php:235
Stack trace:
#0 /PROJECT_PATH/vendor/genealabs/laravel-model-caching/src/CacheKey.php(235): Illuminate\Foundation\Bootstrap\HandleExceptions->handleError(8, 'Undefined offse...', '/home/vagrant/C...', 235, Array)
#1 /PROJECT_PATH/vendor/genealabs/laravel-model-caching/src/CacheKey.php(152): GeneaLabs\LaravelModelCaching\CacheKey->getRawClauses(Array)
#2 [internal function]: GeneaLabs\LaravelModelCaching\CacheKey->GeneaLabs\LaravelModelCaching\{closure}('-_or_LOWER(`pra...', Array)
#3 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Support/Collection.php(1313): array_reduce(Array, Object(Closure), NULL)
#4 /PROJECT_PATH/vendor/genealabs/laravel-model-caching/src/CacheKey.php(158): Illuminate\Support\Collection->reduce(Object(Closure))
#5 /PROJECT_PATH/vendor/genealabs/laravel-model-caching/src/CacheKey.php(169): GeneaLabs\LaravelModelCaching\CacheKey->getWhereClauses(Array)
#6 /PROJECT_PATH/vendor/genealabs/laravel-model-caching/src/CacheKey.php(150): GeneaLabs\LaravelModelCaching\CacheKey->getNestedClauses(Array)
#7 [internal function]: GeneaLabs\LaravelModelCaching\CacheKey->GeneaLabs\LaravelModelCaching\{closure}(NULL, Array)
#8 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Support/Collection.php(1313): array_reduce(Array, Object(Closure), NULL)
#9 /PROJECT_PATH/vendor/genealabs/laravel-model-caching/src/CacheKey.php(158): Illuminate\Support\Collection->reduce(Object(Closure))
#10 /PROJECT_PATH/vendor/genealabs/laravel-model-caching/src/CacheKey.php(36): GeneaLabs\LaravelModelCaching\CacheKey->getWhereClauses()
#11 /PROJECT_PATH/vendor/genealabs/laravel-model-caching/src/Traits/Caching.php(76): GeneaLabs\LaravelModelCaching\CacheKey->make(Array, NULL, '')
#12 /PROJECT_PATH/vendor/genealabs/laravel-model-caching/src/CachedBuilder.php(78): GeneaLabs\LaravelModelCaching\CachedBuilder->makeCacheKey(Array)
#13 /PROJECT_PATH/vendor/yajra/laravel-datatables-oracle/src/QueryDataTable.php(186): GeneaLabs\LaravelModelCaching\CachedBuilder->get()
#14 /PROJECT_PATH/vendor/yajra/laravel-datatables-oracle/src/QueryDataTable.php(86): Yajra\DataTables\QueryDataTable->results()
#15 /PROJECT_PATH/app/Http/Controllers/Backend/Organization/Practice/PracticeController.php(65): Yajra\DataTables\QueryDataTable->make(true)
#16 [internal function]: App\Http\Controllers\Backend\Organization\Practice\PracticeController->get(Object(App\Http\Requests\Backend\Organization\Practice\ManagePracticeRequest))
#17 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(54): call_user_func_array(Array, Array)
#18 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(45): Illuminate\Routing\Controller->callAction('get', Array)
#19 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Route.php(212): Illuminate\Routing\ControllerDispatcher->dispatch(Object(Illuminate\Routing\Route), Object(App\Http\Controllers\Backend\Organization\Practice\PracticeController), 'get')
#20 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Route.php(169): Illuminate\Routing\Route->runController()
#21 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Router.php(665): Illuminate\Routing\Route->run()
#22 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(30): Illuminate\Routing\Router->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#23 /PROJECT_PATH/app/Http/Middleware/RouteNeedsRole.php(35): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#24 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): App\Http\Middleware\RouteNeedsRole->handle(Object(Illuminate\Http\Request), Object(Closure), '1')
#25 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#26 /PROJECT_PATH/app/Http/Middleware/OrganizationMiddleware.php(31): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#27 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): App\Http\Middleware\OrganizationMiddleware->handle(Object(Illuminate\Http\Request), Object(Closure))
#28 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#29 /PROJECT_PATH/app/Http/Middleware/PasswordExpires.php(34): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#30 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): App\Http\Middleware\PasswordExpires->handle(Object(Illuminate\Http\Request), Object(Closure))
#31 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#32 /PROJECT_PATH/app/Http/Middleware/NeedsPracticeCreation.php(34): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#33 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): App\Http\Middleware\NeedsPracticeCreation->handle(Object(Illuminate\Http\Request), Object(Closure))
#34 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#35 /PROJECT_PATH/app/Http/Middleware/NeedsPasswordChange.php(34): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#36 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): App\Http\Middleware\NeedsPasswordChange->handle(Object(Illuminate\Http\Request), Object(Closure))
#37 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#38 /PROJECT_PATH/app/Http/Middleware/PortalMiddleware.php(71): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#39 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): App\Http\Middleware\PortalMiddleware->handle(Object(Illuminate\Http\Request), Object(Closure))
#40 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#41 /PROJECT_PATH/app/Http/Middleware/IsUserMiddleware.php(25): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#42 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): App\Http\Middleware\IsUserMiddleware->handle(Object(Illuminate\Http\Request), Object(Closure))
#43 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#44 /PROJECT_PATH/app/Http/Middleware/RefreshSessionObjects.php(37): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#45 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): App\Http\Middleware\RefreshSessionObjects->handle(Object(Illuminate\Http\Request), Object(Closure))
#46 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#47 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php(41): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#48 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Routing\Middleware\SubstituteBindings->handle(Object(Illuminate\Http\Request), Object(Closure))
#49 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#50 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Auth/Middleware/Authenticate.php(43): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#51 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Auth\Middleware\Authenticate->handle(Object(Illuminate\Http\Request), Object(Closure))
#52 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#53 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php(67): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#54 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Foundation\Http\Middleware\VerifyCsrfToken->handle(Object(Illuminate\Http\Request), Object(Closure))
#55 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#56 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php(49): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#57 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\View\Middleware\ShareErrorsFromSession->handle(Object(Illuminate\Http\Request), Object(Closure))
#58 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#59 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php(37): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#60 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse->handle(Object(Illuminate\Http\Request), Object(Closure))
#61 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#62 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php(59): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#63 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Cookie\Middleware\EncryptCookies->handle(Object(Illuminate\Http\Request), Object(Closure))
#64 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#65 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(104): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#66 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Router.php(667): Illuminate\Pipeline\Pipeline->then(Object(Closure))
#67 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Router.php(642): Illuminate\Routing\Router->runRouteWithinStack(Object(Illuminate\Routing\Route), Object(Illuminate\Http\Request))
#68 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Router.php(608): Illuminate\Routing\Router->runRoute(Object(Illuminate\Http\Request), Object(Illuminate\Routing\Route))
#69 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Router.php(597): Illuminate\Routing\Router->dispatchToRoute(Object(Illuminate\Http\Request))
#70 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(176): Illuminate\Routing\Router->dispatch(Object(Illuminate\Http\Request))
#71 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(30): Illuminate\Foundation\Http\Kernel->Illuminate\Foundation\Http\{closure}(Object(Illuminate\Http\Request))
#72 /PROJECT_PATH/vendor/barryvdh/laravel-debugbar/src/Middleware/InjectDebugbar.php(58): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#73 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Barryvdh\Debugbar\Middleware\InjectDebugbar->handle(Object(Illuminate\Http\Request), Object(Closure))
#74 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#75 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(63): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#76 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Session\Middleware\StartSession->handle(Object(Illuminate\Http\Request), Object(Closure))
#77 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#78 /PROJECT_PATH/vendor/fideloper/proxy/src/TrustProxies.php(57): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#79 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Fideloper\Proxy\TrustProxies->handle(Object(Illuminate\Http\Request), Object(Closure))
#80 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#81 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(31): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#82 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Foundation\Http\Middleware\TransformsRequest->handle(Object(Illuminate\Http\Request), Object(Closure))
#83 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#84 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(31): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#85 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Foundation\Http\Middleware\TransformsRequest->handle(Object(Illuminate\Http\Request), Object(Closure))
#86 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#87 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#88 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Foundation\Http\Middleware\ValidatePostSize->handle(Object(Illuminate\Http\Request), Object(Closure))
#89 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#90 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php(51): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#91 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode->handle(Object(Illuminate\Http\Request), Object(Closure))
#92 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#93 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(104): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))
#94 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(151): Illuminate\Pipeline\Pipeline->then(Object(Closure))
#95 /PROJECT_PATH/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(116): Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter(Object(Illuminate\Http\Request))
#96 /PROJECT_PATH/public/index.php(55): Illuminate\Foundation\Http\Kernel->handle(Object(Illuminate\Http\Request))
#97 {main}  

[mikebronner]

If you install Laravel Debugbar, can you see which query caused it? I am completely unfamiliar with the data tables package.

[eelco2k]

have you also tried writing the activerecord pattern like this
->where(DB::raw(""))
I know whereRaw and whereHaving in combination with yajra datatables is not always working properly.

[eelco2k]

I also noticed that using "?" in the whereRaw it was not working for me as well so i did this:

->whereRaw("name LIKE '{$search}%' ")
hope it helps.

and i see you're still using the old method: "->make(true)", the new one = ->toJson()

update:
if you do want to use ? in your whereRaw you should add:

SomeModel::whereRaw('somecolumn = ?')->setBindings([$somecolumn])->get();

or

 ->whereRaw("name = ?")
->setBindings([
                $name
            ]);

[rappasoft]

@eelco2k I will give these a shot. @mikebronner I don't see the query in debugbar because it's AJAX, i'm not sure if theres an option to watch. Nonetheless I can set up a middleware to just log every query and i'll try to find it for you. Thanks.

P.S. The current one i'm testing with does not have any extra whereRaw's added by me so I can't even test with those. So i'm pretty sure its not the ones i'm adding its the ones being generated by the plugin.

I just dumped line 234 and currentBindings in CacheKey.php and this is the stack:

[2018-06-03 20:31:00] local.INFO: _or_LOWER(`users`.`name`) LIKE   
[2018-06-03 20:31:00] local.INFO: 1  
[2018-06-03 20:31:00] local.INFO: _or_LOWER(`users`.`phone`) LIKE   
[2018-06-03 20:31:00] local.INFO: 2  
[2018-06-03 20:31:00] local.INFO: _or_LOWER(`users`.`address`) LIKE   
[2018-06-03 20:31:00] local.INFO: 3  
[2018-06-03 20:31:00] local.ERROR: ErrorException: Undefined offset: 3.....

[eelco2k]

@rappasoft you can see datatables queries and bindings when you set DEBUG=true in your .env file.

When you open Network in developer toolbar of chrome you should see a Ajax response with the queries and bindings which have been executed. Hope that helps debugging.

[rappasoft]

I get a query on the initial page load which is such (this works, the initial table loads data):

select count(*) as aggregate from (select '1' as row_countfromuserswhereusers.deleted_at is null) count_row_table

The search query fires but the queries array is not populated I assume because it errors before. But this is what it shows:

{draw: 3, recordsTotal: 2, recordsFiltered: 0, data: [],…}
data:[]
draw:3
error:"Exception Message:↵↵Undefined offset: 3"
recordsFiltered:0
recordsTotal:2

I have a HAR file exported if it helps at all, though i don't think it will but let me know and i'll upload it.

[eelco2k]

@rappasoft how have you configured the backend part for the search. Do you mean the global search of the dataTables or is it a column search? I can provide you an example of my global search method:

 return Datatables::of($model_or_query)
                ->filter(function ($query) use ($request) {
                    if ($request->has('search') && trim($request->get('search')['value']) != '') {
                        $value = $request->get('search')['value'];
                        
                       $query->whereRaw($this->regex_filter('id', $value) . ' OR ' . $this->regex_filter('complaintid', $value))
                            ->orWhereRaw($this->regex_filter('name', $value) . ' OR ' . $this->regex_filter('second_name', $value))
                        }
                })

The $this->regex_filter is a custom private function but returns an raw sql query part.

for a specific column filter i've defined as followed:

->filterColumn('telephone', function ($query, $keyword) {
                    $query->whereRaw($this->regex_filter('telephone', $keyword));
                })

[rappasoft]

@eelco2k I have most of my tables using the default search without me overriding anything. I'm testing with a table that has no relationships or joins which is not working, and I think is the simplest use case.

I have other tables with relationships I'm trying to figure out the searching and whatnot with them.

How do you set up your queries for that? Do you join and select the rows before? Or do you use eloquent to display them and then use the filterColumn method? In that case can you use joins or are you limited to where clauses?

[eelco2k]

@rappasoft i'm using very complex joins and selections and filter activeRecord patterns.

$query = DB::table('some_table')
->leftJoin('related_table', 'some_table.related_table_id', 'related_table.id')
->select("*")  // using a complext select with some db::raw selectors
->groupBy('id')
->whereNull('some_table.deleted_at')
->havingRaw($having_in_a_set)
->;

$dt = DataTables::of($query)

//column filter
->filterColumn('column_name', function($query, $keyword) {
                    $query->whereRaw($this->regex_filter('column_name', $keyword));
                })
//global filter
->filter(function ($query) use ($request) {
                    if ($request->has('search') && trim($request->get('search')['value']) != '') {
                        $value = $request->get('search')['value'];
                        //your filtercolumn names
                       $query->whereRaw($this->regex_filter('id', $value) . ' OR ' . $this->regex_filter('complaintid', $value))
                            ->orWhereRaw($this->regex_filter('name', $value) . ' OR ' . $this->regex_filter('second_name', $value))
                        }
                })
->toJson();

return $dt;

[rappasoft]

@eelco2k It's just weird because everything works well with caching turned off, global search, column search, sorting. But as soon as I turn it on, all the tables will load the data initially, but as soon as I go to search is when I get errors.

[eelco2k]

@rappasoft Are you using DB::table or a Eloquent model? because if you use eloquent model your already defined use Cachable in your model. but when using DB::table() there is no caching involved i think.. you need to define it in for example your controller / class. And i'm not sure if that works because CachedBuilder class extends EloquentBuilder and not QueryBuilder.

I will create a test model with datatables and test it.

[rappasoft]

@eelco2k I'm using eloquent throughout