Laravel-datatables: Eager loading column search

[cesarcruzc]

Hi Yajra,

Cant search by column with eager loading:

I got this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'workerData.numero_documento' in 'where clause' (SQL: select count(*) as aggregate from (select '1' as `row_count` from `bdc_asignacion` where LOWER(`workerData`.`numero_documento`) LIKE %1%) count_row_table)

Model:

$model = $this->getModel();
return $model->with('workerData', 'contractor', 'project');

Controller:

$workerList = $this->assignmentRepository->getWorkerList();
return Datatables::of($workerList)->make(true);

View:

$('#worker-list').DataTable({
                processing: true,
                serverSide: true,
                ajax: {
                    url: '{{ route('ajax.query.control-register') }}',
                    method: 'POST'
                },
                columns: [
                    {data: 'worker_data.nombres', name: 'workerData.nombres'},
                    {data: 'worker_data.apellidos', name: 'workerData.apellidos'},
                    {data: 'worker_data.numero_documento', name: 'workerData.numero_documento'},
                    {data: 'contractor.nombre'},
                    {data: 'project.nombre'},
                ],
                initComplete: function () {
                    this.api().columns().every(function () {
                        var column = this;
                        var input = document.createElement("input");
                        $(input).appendTo($(column.footer()).empty())
                                .on('change', function () {
                                    column.search($(this).val(), false, false, true).draw();
                                });
                    });
                }
            });

Thanks for this package.

[yajra]

I see. Eager loading for column search is not yet supported. Thanks for reporting. Will try to implement this as soon as I can.

Ping @ikerasLT, eager loading master. Thanks!

[mauroartizzu]

Same to me, the problem does not occur if you do not order the columns first though.

[yajra]

Guys, I just submitted a PR to fix this issue. Can you please verify if it works for you? Thanks!

[mauroartizzu]

Nope. Same as before.

Models:

class Team extends Model
{
    public function user()
    {
        return $this->belongsTo('App\User');
    }
}

class User extends Model
{
public function teams()
    {
        return $this->hasMany('App\Team');
    }
}

Controller:

$teams = Team::with('user');
return Datatables::of($teams)->make(true);

View:

$('#mytable').DataTable({
    processing: true,
    serverSide: true,
    ajax: '/teams/datatables',
    columns: [
      {data: 'team', name: 'team'},
      {data: 'info', name: 'info'},
      {data: 'user.name', name: 'user.name'}
    ]
  });

I still get
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user.name' in 'where clause' (SQL: select count(*) as aggregate from (select '1' asrow_countfromteamswhere LOWER(user.name) LIKE %a%) count_row_table)

[mauroartizzu]

EDIT: https://github.com/yajra/laravel-datatables/pull/469 does fix. Thanks!

[cesarcruzc]

@mauroartizzu Works for you? I still get

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'workerData.nombres' in 'where clause' (SQL: select count(*) as aggregate from (select '1' as `row_count` from `bdc_asignacion` where LOWER(`workerData`.`nombres`) LIKE %abelardo%) count_row_table)

[mauroartizzu]

Yep. I had to manually copy the PR code but it works, I hope it will be integrated soon.

[yajra]

@mauroartizzu, patch was merged and released on v6.10.1.

@cesarcruzc, maybe your relation uses belongsToMany and is a known bug on issue #461. Let's continue the discussion there since this issue should be fixed now.

Thanks a lot!

[diorgesl]

I dont know if someone said, but I have found a bug, if you sort a relational column where the column name from the relational table have the same column name from original table, the result from relational table will overwrite others columns with the same name.

[tmuks]

I am facing same issue unknown column when using chained rule in with clause.
Ex. $q->with('user.role')
It works with single eager loading but when chained, fails

[yajra]

@tmuks nested eager loading search was just recently supported on v6.22.0. Try updating to the latest version of package.