mikebronner
commented
6 years ago If you install Laravel Debugbar, can you see which query caused it? I am completely unfamiliar with the data tables package.
Closed rappasoft closed 6 years ago
mikebronner
commented
6 years ago If you install Laravel Debugbar, can you see which query caused it? I am completely unfamiliar with the data tables package.
eelco2k
commented
6 years ago 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
commented
6 years ago 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
commented
6 years ago @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.....@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
commented
6 years ago 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' asrow_countfromuserswhereusers.deleted_atis 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:2I 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
commented
6 years ago @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));
})@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
commented
6 years ago @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;@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
commented
6 years ago @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
commented
6 years ago @eelco2k I'm using eloquent throughout
eelco2k
commented
6 years ago @rappasoft ok, i switched to queries because some lazyloading didn't work with whereHaving and related tables.
but I tested with a simple Eloquent model. and it also works.
i'm using: "yajra/laravel-datatables-oracle": "8.*" in my composer.json and DataTables 1.10.16 javascript version.
namespace App\Models;
use Yajra\DataTables\Facades\DataTables;
use GeneaLabs\LaravelModelCaching\Traits\Cachable;
class DataTableQueries {
use Cachable;
public function getTestModel() {
$model = \App\Models\TestModel::with('related_table')
->ModelJoin('related_table')
->select([DB::raw("CONCAT(firstname , lastname)"), 'related_table.prefix', 'related_table.suffix' ])
->where() //etc. etc.
return Datatables::of($model)
->filter()
->filterColumn()
->toJson();
}
}
@eelco2k thanks for testing. I have the latest version of everything. I'm honestly stumped there's not much to go by. I hope I can figure it out they're both great plugins and great together.
eelco2k
commented
6 years ago @rappasoft could you send the ajax request which is send by the datatables plugin to the server when you do a global search. because it think that a column on the frontside may be hidden or not coupled correctly to a table column on backend side.
eelco2k
commented
6 years ago @rappasoft you could also check if ->smart(false/true)->toJson() and regex true/false makes any difference
$('#example').dataTable({
processing: true,
serverSide: true,
ajax: '',
columns: [
{data: 'id', name: 'id'},
{data: 'name', name: 'name'},
{data: 'email', name: 'email'},
{data: 'created_at', name: 'created_at'},
{data: 'updated_at', name: 'updated_at'}
],
search: {
"regex": true
}
}); my request URL looks like this when i search in a datatable:
draw: 14
columns[0][data]: klager_id
columns[0][name]:
columns[0][searchable]: true
columns[0][orderable]: true
columns[0][search][value]:
columns[0][search][regex]: false
columns[1][data]: achternaam
columns[1][name]:
columns[1][searchable]: true
columns[1][orderable]: true
columns[1][search][value]:
columns[1][search][regex]: false
columns[2][data]: straat
columns[2][name]:
columns[2][searchable]: true
columns[2][orderable]: true
columns[2][search][value]:
columns[2][search][regex]: false
columns[3][data]: postcode
columns[3][name]:
columns[3][searchable]: true
columns[3][orderable]: true
columns[3][search][value]:
columns[3][search][regex]: false
columns[4][data]: plaats
columns[4][name]:
columns[4][searchable]: true
columns[4][orderable]: true
columns[4][search][value]:
columns[4][search][regex]: false
columns[5][data]: tel
columns[5][name]:
columns[5][searchable]: true
columns[5][orderable]: true
columns[5][search][value]:
columns[5][search][regex]: false
columns[6][data]: tijdstip
columns[6][name]: date_added
columns[6][searchable]: true
columns[6][orderable]: true
columns[6][search][value]:
columns[6][search][regex]: false
columns[7][data]: aanpassen
columns[7][name]:
columns[7][searchable]: true
columns[7][orderable]: true
columns[7][search][value]:
columns[7][search][regex]: false
columns[8][data]: function
columns[8][name]: date_added
columns[8][searchable]: false
columns[8][orderable]: true
columns[8][search][value]:
columns[8][search][regex]: false
columns[9][data]: straat
columns[9][name]:
columns[9][searchable]: false
columns[9][orderable]: true
columns[9][search][value]:
columns[9][search][regex]: false
columns[10][data]: huisnr
columns[10][name]:
columns[10][searchable]: false
columns[10][orderable]: true
columns[10][search][value]:
columns[10][search][regex]: false
columns[11][data]: toevoeging
columns[11][name]:
columns[11][searchable]: false
columns[11][orderable]: true
columns[11][search][value]:
columns[11][search][regex]: false
columns[12][data]: klager_prev_id
columns[12][name]:
columns[12][searchable]: false
columns[12][orderable]: true
columns[12][search][value]:
columns[12][search][regex]: false
columns[13][data]: klager_id
columns[13][name]:
columns[13][searchable]: false
columns[13][orderable]: true
columns[13][search][value]:
columns[13][search][regex]: false
columns[14][data]: achternaam1
columns[14][name]:
columns[14][searchable]: false
columns[14][orderable]: true
columns[14][search][value]:
columns[14][search][regex]: false
columns[15][data]: achternaam2
columns[15][name]:
columns[15][searchable]: false
columns[15][orderable]: true
columns[15][search][value]:
columns[15][search][regex]: false
columns[16][data]: active
columns[16][name]:
columns[16][searchable]: false
columns[16][orderable]: true
columns[16][search][value]:
columns[16][search][regex]: false
columns[17][data]: aanhef
columns[17][name]:
columns[17][searchable]: false
columns[17][orderable]: true
columns[17][search][value]:
columns[17][search][regex]: false
columns[18][data]: voorletters
columns[18][name]:
columns[18][searchable]: false
columns[18][orderable]: true
columns[18][search][value]:
columns[18][search][regex]: false
columns[19][data]: tussenvoegsel
columns[19][name]:
columns[19][searchable]: false
columns[19][orderable]: true
columns[19][search][value]:
columns[19][search][regex]: false
order[0][column]: 6
order[0][dir]: desc
start: 0
length: 10
search[value]: van der
search[regex]: true
_: 1528304746137my search value is: "van der"
rappasoft
commented
6 years ago It's very possible I have something messed up, I find the whole data mapping part confusing with data vs. name etc.
Here is my request preview:
draw: 3
columns[0][data]: logo
columns[0][name]: logo
columns[0][searchable]: false
columns[0][orderable]: false
columns[0][search][value]:
columns[0][search][regex]: false
columns[1][data]: name
columns[1][name]: name
columns[1][searchable]: true
columns[1][orderable]: true
columns[1][search][value]:
columns[1][search][regex]: false
columns[2][data]: phone
columns[2][name]: phone
columns[2][searchable]: true
columns[2][orderable]: true
columns[2][search][value]:
columns[2][search][regex]: false
columns[3][data]: address
columns[3][name]: address
columns[3][searchable]: true
columns[3][orderable]: false
columns[3][search][value]:
columns[3][search][regex]: false
columns[4][data]: actions
columns[4][name]: actions
columns[4][searchable]: false
columns[4][orderable]: false
columns[4][search][value]:
columns[4][search][regex]: false
order[0][column]: 1
order[0][dir]: asc
start: 0
length: 50
search[value]: test
search[regex]: falseHere is the jquery call:
$(function() {
$('#agencies').DataTable({
processing: true,
serverSide: true,
ajax: {
url: '{!! route('admin.organization.practice.get') !!}',
method: 'POST'
},
order: [[ 1, "asc" ]],
columns: [
{ data: 'logo', name: 'logo', orderable: false, searchable: false },
{ data: 'name', name: 'name' },
{ data: 'phone', name: 'phone' },
{ data: 'address', name: 'address', orderable: false },
{ data: 'actions', name: 'actions', orderable: false, searchable: false }
],
language: {
emptyTable: "There are no agencies to show",
zeroRecords: "There are no agencies to show that match the search query",
},
}).columns().every(function () {
var that = this;
$('input', this.footer()).on( 'keyup change', function() {
if (that.search() !== this.value) {
that.search(this.value).draw();
}
});
});
});Here is the server side:
return DataTables::of(Practice::select(['id', 'name', 'logo', 'phone', 'address']))
->editColumn('logo', function($practice) {
if ($practice->logo) {
return '<img src="'.storage_asset($practice->logo).'" style="max-width:100px" />';
} else {
return 'N/A';
}
})
->addColumn('actions', function($practice) {
return $practice->actions;
})
->rawColumns(['logo', 'actions'])
->toJson();There are no relationships with this particular table, the db cols map the datatable call for the most part minus extra added cols that aren't searchable.
eelco2k
commented
6 years ago @rappasoft i'm guessing it has to do with the actions column. and the
search[value]: test
search[regex]: falseyou define in the frontend that it's not searchable " columns[4][searchable]: false" but maybe on backend part it ignores it for some reason... (maybe because of the addcolumn) could you override the global filter and loop through the columns??:
->filter(function ($query) use ($request) { }, true)
@eelco2k I removed that added column completely with same result:
draw: 8
columns[0][data]: logo
columns[0][name]: logo
columns[0][searchable]: false
columns[0][orderable]: false
columns[0][search][value]:
columns[0][search][regex]: false
columns[1][data]: name
columns[1][name]: name
columns[1][searchable]: true
columns[1][orderable]: true
columns[1][search][value]:
columns[1][search][regex]: false
columns[2][data]: phone
columns[2][name]: phone
columns[2][searchable]: true
columns[2][orderable]: true
columns[2][search][value]:
columns[2][search][regex]: false
columns[3][data]: address
columns[3][name]: address
columns[3][searchable]: true
columns[3][orderable]: false
columns[3][search][value]:
columns[3][search][regex]: false
order[0][column]: 1
order[0][dir]: asc
start: 0
length: 50
search[value]: test
search[regex]: false@rappasoft hmm...
I was looking at the global filter of dataTables here (globalSearch function ):
and the columnSearch() filter: https://github.com/yajra/laravel-datatables/blob/5dbba7b6b8debe5143a1d922957afb08d2410c39/src/CollectionDataTable.php#L92
the columnSearch checks if "$this->request->isColumnSearchable($i)" and globalfilter is not.
rappasoft
commented
6 years ago @eelco2k Interesting, I do have separate filtering by columns, and If I use that it works, but if I search via the top right bar of the datatable is when it throws the error. What do you suggest the fix is?
eelco2k
commented
6 years ago try in your datatables jquery object:
search: {
"regex": true
}for global search with regex maybe it helps because i have it enabled.
rappasoft
commented
6 years ago @eelco2k That doesn't seem to make a difference, nor that or any combination of that and safe search on/off.
eelco2k
commented
6 years ago @rappasoft i remember that i changed data: actions, name: actions to data: null, name: actions
but i have a defaultContent in that column. so client side:
defaultContent: '<a href="" class="btn btn-small btn-orange lsf editor_edit">edit</a> <a href="" class="btn btn-small btn-black lsf editor_eye">eye</a> <a href="" class="btn btn-small btn-red lsf editor_remove">remove</a>'
eelco2k
commented
6 years ago this is my last column for edit and delete buttons in jquery datatables init :
columns([
{
targets: 7,
title: "Actions",
width: "100px",
data: null,
className: "center",
defaultContent: '<a href="" class="btn btn-small btn-orange lsf editor_edit">edit</a> <a href="" class="btn btn-small btn-black lsf editor_eye">eye</a> <a href="" class="btn btn-small btn-red lsf editor_remove">remove</a>'
},
])@eelco2k That does not seem to work either. It's weird because I have about a dozen different datatables. They all have an added actions column and some error and some don't. :-/
eelco2k
commented
6 years ago @rappasoft
also i have defined this:
columnDefs: [
{ //this prevents errors if the data is null
targets: "_all",
defaultContent: ""
}
]@rappasoft well that's weird... but i don't think it only has to do with the Cachable plugin then... Good luck hopefully you can find what's causing the error. I can say it does work with Cacheble package. Unless it doesn't cache at all.... where can i check if it has cached in my redis cache?
rappasoft
commented
6 years ago @eelco2k It's all good you're probably right it's not a problem with the cache plugin, there's something wrong with something in my project that i'll have to figure out on my own. Thanks for all the help.
eelco2k
commented
6 years ago @rappasoft their still could be some conflics between what has been cached and what is queries and cached. but i only have redis so i don't know much how laravel-model-caching is behaving and saving what in the cache. (redis is not very clear where to find the key: values which are cached.) maybe temporary i will set it back to file cache to debug further.
eelco2k
commented
6 years ago @rappasoft btw i made a mistake: you have to set "APP_DEBUG=true" in you .env file for datatables query logging
rappasoft
commented
6 years ago Yes @eelco2k It was always set to true, I can see the queries when the request completes successfully but not when it errors.
eelco2k
commented
6 years ago @rappasoft you could set cache library to file/database and see if the cached object has the error in the cached file / database column.
cached files are stored in "storage/framework/cache/" and then sort latest edited.
it's serialized so you should unserialize it, here's mine as an example
9999999999a:2:{s:3:"key";s:96:"genealabs:laravel-model-caching:mysql:database_name:appmodelsormklager-active_=_1-postcd-limit_10";s:5:"value";O:39:"Illuminate\Database\Eloquent\Collection":1:{s:8:" * items";a:10:{i:0;O:21:"App\Models\Orm\Klager":32:{s:8:" * table";s:6:"klager";s:13:" * increments";b:1;s:13:" * primaryKey";s:2:"id";s:11:" * fillable";a:12:{i:0;s:8:"klagernr";i:1;s:6:"aanhef";i:2;s:11:"voorletters";i:3;s:13:"tussenvoegsel";i:4;s:10:"achternaam";i:5;s:9:"postcd_id";i:6;s:3:"tel";i:7;s:3:"bbg";i:8;s:10:"date_added";i:9;s:8:"response";i:10;s:20:"preferred_airport_id";i:11;s:6:"active";}s:10:" * guarded";a:4:{i:0;s:2:"id";i:1;s:10:"created_at";i:2;s:10:"updated_at";i:3;s:10:"deleted_at";}s:13:" * filterable";a:10:{s:2:"id";s:33:"Gency\Filterable\Type\IntegerType";s:6:"active";s:33:"Gency\Filterable\Type\BooleanType";s:8:"klagernr";s:33:"Gency\Filterable\Type\IntegerType";s:10:"achternaam";s:32:"Gency\Filterable\Type\StringType";s:11:"achternaam2";s:32:"Gency\Filterable\Type\StringType";s:8:"response";s:33:"Gency\Filterable\Type\BooleanType";s:7:"checked";s:33:"Gency\Filterable\Type\BooleanType";s:10:"created_at";s:30:"Gency\Filterable\Type\DateType";s:10:"updated_at";s:30:"Gency\Filterable\Type\DateType";s:10:"deleted_at";s:30:"Gency\Filterable\Type\DateType";}s:13:" * connection";s:5:"mysql";s:10:" * keyType";s:3:"int";s:12:"incrementing";b:1;s:7:" * with";a:0:{}s:12:" * withCount";a:0:{}s:10:" * perPage";i:15;s:6:"exists";b:1;s:18:"wasRecentlyCreated";b:0;s:13:" * attributes";a:31
@eelco2k I don't see anything of interest, i'm probably going to end up just removing the caching plugin in favor of the datatables.
I just tried doing a custom global search and I can get it to work for the most part but then it seems to break column filtering. I think it'll be much less headache for me to just to manual caching.
Thanks for the help!
P.S. I narrowed the query and datatable down to a single column called name. Same error, the undefined offset index just moves with the amount of columns. If I turn caching off it works. I have literally no idea.
eelco2k
commented
6 years ago @rappasoft I also have columnfilter and global filter in my datatables. both work fine with cached models.
but there is a difference i don't do this:
.columns().every(function () {
var that = this;
$('input', this.footer()).on( 'keyup change', function() {
if (that.search() !== this.value) {
that.search(this.value).draw();
}
});
});
but i'm using this plugin: http://yadcf-showcase.appspot.com/
rappasoft
commented
6 years ago @eelco2k That plugin looks nice but probably overkill for what I need. I took that code right from the datatables website.
eelco2k
commented
6 years ago @rappasoft you could try it. because i have really complex datatables queries (not eloquent) and complex column filters/ global filter and it works. so it should work somehow...
eelco2k
commented
6 years ago @rappasoft good luck and hopefully you can find a solution. it was nice trying to help you!
rappasoft
commented
6 years ago @eelco2k The column filters work, its the global search that doesn't work with caching on.
Thanks for all the help, I definitely learned some stuff along the way.
ikerasLT
commented
6 years ago @mikebronner Have the same issue while using with yajra/laravel-datatables package (latest versions), which generates nested whereRaws while performing global search.
I believe $this->currentBinding++; in getNestedClauses is causing the issue as it's advancing bindings when there is none.
mikebronner
commented
6 years ago @ikerasLT Unfortunately I am unable to support any issues with third-party packages due to time and budget limitations. If you could reproduce the problem in a unit test (without the other plugin being installed) and submit that as a PR, I will be happy to work on a fix. :)
mikebronner
commented
6 years ago I am locking this issue, as it is has been discussed at length with no resolution yet. Anyone experiencing this problem is welcome to submit a PR with a unit/integration test that replicates the issue, and I will work on a fix. Thanks!
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: https://github.com/GeneaLabs/laravel-model-caching/issues/111
Possible clue:
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