Laravel Datatables search on nested Relationship

Question

I'm currently using datatabels 7.0 and Laravel 5.4 data in my grid is showing correctly but when I want to search on nested relationship column I got below error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'course_semester.semester.name' in 'where clause' (SQL: select count(*) as aggregate from (select '1' as `row_count` from `videos` where LOWER(`videos`.`id`) LIKE %%te%% and LOWER(`videos`.`name`) LIKE %%te%% and LOWER(`course_semester`.`semester`.`name`) LIKE %%te%%) count_row_table)

Here is my code

Controller

$videos = Video::with(['course_semester' => function ($query) {
            return $query->with('course', 'semester');
        }])->select('videos.*');
        return Datatables::of($videos)
                ->addColumn('check', '<input type="checkbox" name="selected-videos" value="{{$id}}">')
                ->escapeColumns([])
                ->make(true);

Javascript

columns: [
                 { data: 'check' , name: 'check',orderable: false, searchable: false },
                 { data: 'id', name: 'videos.id' },
                 { data: 'name', name: 'videos.name' },
                 { data: 'course_semester.semester.name', name: 'course_semester.semester.name'},
                 { data: 'course_semester.course.name', name: 'course_semester.course.name'},
                 { data: 'status', name: 'videos.status' },
                 { data: 'comment', name: 'videos.comment' },
                 { data: 'video_date', name: 'videos.video_date' },
            ]

Can anyone help me and notice my problem?

Thanks in advance.

Answer 1

Because of using select() here, you do not load the relationship. Also, use dot syntax for nested eager loading:

$videos = Video::with(['course_semester', 'course_semester.course'])

Answer 2

1- dataTable function

public function dataTable($query)
{
    return datatables()
        ->eloquent($query)
        ->addColumn('BulkSelection', function ($category) 
        {
        ->addColumn('company', function ($category) 
        {
            return $category->company ? $category->company->name : 'No Company';
        })
        ->rawColumns(['action', 'company', 'createdAt','BulkSelection']);
}

2- query function

    public function query(Category $model)
{
        return $model->with('company:id,name')->select('categories.*')->newQuery();
}

3- getColumns function

$getCols = [
     
        'id',
        'name',
        'company'=> [
            'data'=>'company',
            'name'=>'company.name'
        ],
        'createdAt' => [
            'name'=>'created_at',
            'searchable' => false
        ],
        'action' => [
            'exportable' => false,
            'searchable'=>false,
            'orderable' => false
        ]
    ];

    return $getCols;

Answer 3

If you just need to load the data from relation tables, use the below code

$videos = Video::with('course_semester','course', 'semester')->select('videos.*');
        return Datatables::eloquent($videos)
                ->addColumn('check', function(Video $v){ 
                  return '<input type="checkbox" name="selected-videos" value="{{$v->id}}">'
                  })
                ->rawColumns(['check'])
                ->make(true);

And in script, you can call relation values as ,

          {data: 'course_semester.id'},
          {data: 'course.name'},
          {data: 'semester.name'},