Jquery datatable Invalid Argument exception - message - Trailing data on Laravel eloquent raw query

Question

I have setup Laravel Yajra datatable backend package to handle the work for the ajax query calls to view via a jquery datatable. Everything worked great until I changed the eloquent query from:

static function for_table($farm_ids) 
    {
            return ChemProgramUser::select('chemprogramuser.id', 'chemprogramuser.farms_id as fid', 
            'chemprogramuser.description as head', 'chemprogramuser.hectares as ha', 
            'chemprogramapp.updated_at',
            'farms.name as fname', 'users.name', 'users.id as uid', 'chemprogramuser.type')
                ->leftJoin('farms', 'farms.id', '=', 'chemprogramuser.farms_id')
                ->leftJoin('users', 'users.id', '=', 'chemprogramuser.manager_id')
                ->groupBy('chemprogramuser.id')
                ->orderBy('chemprogramuser.id', 'desc')
                ->where('chemprogramuser.program', '=', 1)
                ->whereIn('chemprogramuser.farms_id', $farm_ids)
                ->get();

to

static function for_table($farm_ids) 
    {
            return ChemProgramUser::select('chemprogramuser.id', 'chemprogramuser.farms_id as fid', 
            'chemprogramuser.description as head', 'chemprogramuser.hectares as ha', 
            DB::raw('GREATEST(chemprogramuser.updated_at, COALESCE(chemprogramapp.updated_at, \'1000-01-01\')) AS updated_at'), // this raw query caused the error
            'farms.name as fname', 'users.name', 'users.id as uid', 'chemprogramuser.type')
                ->leftJoin('farms', 'farms.id', '=', 'chemprogramuser.farms_id')
                ->leftJoin('users', 'users.id', '=', 'chemprogramuser.manager_id')
                ->leftJoin('chemprogramapp', 'chemprogramapp.programuser_id', '=', 'chemprogramuser.id')
                ->groupBy('chemprogramuser.id')
                ->orderBy('chemprogramuser.id', 'desc')
                ->where('chemprogramuser.program', '=', 1)
                ->whereIn('chemprogramuser.farms_id', $farm_ids)
                ->get();

I am selecting the GREATEST date from two tables using the DB facade's raw method. As soon I changed the query I received a jquery InvalidArgumentException with message Trailing data. But this is just on the production server, not on my localhost.

Can someone please give an idea why this error will occur?

Laravel version: 5.6.39

score 0 · Answer 1 · answered Jun 04 '21 at 12:11

So I found my answer, so I am posting it. It seems it is the way I called the DB::raw() function on the query that caused the error. The raw() function should incapsulate the complete select query string i.e.

select(
    DB::raw(
        ' chemprogramuser.id, chemprogramuser.farms_id as fid, ' .
        ' chemprogramuser.description as head, chemprogramuser.hectares as ha, ' .
        ' GREATEST(chemprogramuser.updated_at, COALESCE(chemprogramapp.updated_at, \'1000-01-01\')) AS updated_at, ' .
        ' farms.name as fname, users.name, users.id as uid, chemprogramuser.type '
        )
    )

The query then for some reason causes no error, even if the error is something obscure as InvalidArgumentException with message Trailing data.

Jquery datatable Invalid Argument exception - message - Trailing data on Laravel eloquent raw query

1 Answers1