Illegal offset type in isset or empty - Object used as Array Index?

[JoshKoberstein]

With this query:

    $clients = DB::table('clients')
        ->select(
            'clients.id',
            'clients.first_name',
            'clients.last_name',
            'clients.last_visit',
            'clients.age',
            'clients.race',
            'clients.sex',
            'clients.kids',
            'clients.disability',
            'clients.notes',
            DB::raw('
                IF(
                    is_banned = 1,
                    "Banned",
                    IF(
                        DATEDIFF(CURDATE(), last_visit) < 30,
                        CONCAT("- ", 30 - DATEDIFF(CURDATE(), last_visit), " days"),
                        "Approved"
                    )
                )')
        );

If I do not alias the DB::raw, I get a "Illegal offset type in isset or empty" error on Line 520 (when filtering). It is my suspicion that an object of type '\Illuminate\Database\Query\Expression' is being used as an array index.

If I change the code...

    $clients = DB::table('clients')
        ->select(
            'clients.id',
            'clients.first_name',
            'clients.last_name',
            'clients.last_visit',
            'clients.age',
            'clients.race',
            'clients.sex',
            'clients.kids',
            'clients.disability',
            'clients.notes',
            DB::raw('
                IF(
                    is_banned = 1,
                    "Banned",
                    IF(
                        DATEDIFF(CURDATE(), last_visit) < 30,
                        CONCAT("- ", 30 - DATEDIFF(CURDATE(), last_visit), " days"),
                        "Approved"
                    )
                ) AS status')
        );

Everything works perfectly.

Side questions I had ( not sure if this is the right venue for these but they have been on my mind ): I am not 100% familiar with the library but I noticed that the DB::raw columns are not being converted to strings and remain of object type '\Illuminate\Database\Query\Expression' when being saved to class properties ( like $this->last_columns). However, I did notice that the regular expressions seemed to be working fine despite this. Just curious - how is this working? and... would it make sense to get the query strings from each DB::raw column instead of letting them remain an object. Hope that makes sense :)

Anyways, thanks for all the hard work on this library. I use it frequently and love it!

[ktunkiewicz]

Hi Josh

You are right about the error but that is expected behaviour. If you do not specify an alias for DB::raw query it will try to put whatever you have in column name (DB::raw in this case) This a consequence of how the columns definitions works.

Take a look on the syntax of the library methods: (from Readme.md)

place = Place::left_join('owner','places.author_id','=','owner.id')
   ->select(array('places.id','places.name','places.created_at','owner.name as ownername','places.status'));

return Datatables::of($place)
  ->edit_column('status', '{{ $status ? 'Active' : 'Passive' }}')
  ->edit_column('ownername', function($row) {
        return "The author of this post is {$row->ownername}";
    })
  ->make();

You may notice that the first parameter for edit_column is a string that is either a column name or column alias.

So what would be the column name for DB::raw column in your fist code sample then? If you use DB::raw you are required to use alias because this is the only way to assign a name for the DB::raw column for edit_column, filter_column and other internal processing of these columns.

So remember: Every column must have its name and everything will be just fine :)

I may fix that by automatically adding some random alias for DB::raw columns that do not have its oiwn aliases but that will take some time for me to figure out how to do this and not to break anything else that depends on column alias.

the DB::raw columns are not being converted to strings and remain of object type '\Illuminate\Database\Query\Expression' when being saved to class properties ( like $this->last_columns). However, I did notice that the regular expressions seemed to be working fine despite this. Just curious - how is this working?

I think the DB::raw must stay DB::raw all the way down to the Query Builder methods because it will not work otherwise. If you put a string CONCAT(name," ",surname)" into QueryBuilder it will put something like this into SQL query: CONCAT(name,"` `",surname)" and result in sql error "unknown column CONCAT(name," ". So if you want some more compilcated SQL query it must be put into QueryBuilder as DB::raw type.

The preg_match and other string functions work fine on DB::raw because DB::raw has a magic method __toString() that converts it into simple string type when casted to string.

[JoshKoberstein]

Understood :)

Thanks for the excellent explanation.

Cheers! Josh