Laravel-datatables: Search error Column not found

[mohamedshuaau]

I am trying to search my table but there is no result and there is an error popup.

Error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'maintenances.area' in 'where clause' (SQL: select count(*) as aggregate from (select '1' asrow_countfrommaintenanceswherestatusis null and (LOWER(maintenances.id) LIKE %1% or LOWER(maintenances.area) LIKE %1% or LOWER(maintenances.maintenancedepartment) LIKE %1% or LOWER(maintenances.department) LIKE %1% or LOWER(maintenances.ticket) LIKE %1% or LOWER(maintenances.detail) LIKE %1% or LOWER(maintenances.status) LIKE %1% or LOWER(maintenances.attended) LIKE %1% or LOWER(maintenances.delayed) LIKE %1% or LOWER(maintenances.urgent) LIKE %1%) andmaintenances.deleted_atis null) count_row_table)

My Table:

$('#data-table-all').DataTable({
                responsive: true,
                order:[[0,"dec"]],
                processing: true,
                serverSide: true,
                ajax: '{{ route('get.all.maintenance.report') }}',
                columns: [
                    {data: 'id', name: 'id'},
                    {data: 'user.name', name: 'user'},
                    {data: 'area.name', name: 'area'},
                    {data: 'maintenancedepartment.name', name: 'maintenancedepartment'},
                    {data: 'department.name', name: 'department'},
                    {data: 'ticket', name: 'ticket'},
                    {data: 'detail', name: 'detail'},
                    {data: 'status', name: 'status'},
                    {data: 'attended', name: 'attended'},
                    {data: 'delayed', name: 'delayed'},
                    {data: 'delayed_reason', name: 'delayed_reason'},
                    {data: 'urgent', name: 'urgent', defaultContent: "Not Urgent"},
                    {data: 'created_date', name: 'created_date'},
                    {data: 'created_time', name: 'created_time'},

                ],
                "createdRow": function (row, data) {

                    $('td', row).eq('7').html($('<span>').attr({
                        class:  ((data.status == 1) ? 'text-success': 'text-warning' )
                    }).html((( (data.status == 1) ? 'Completed': 'Pending' ))));

                    $('td', row).eq('8').html($('<span>').attr({
                        class:  ((data.attended == 1) ? 'text-success': 'text-warning' )
                    }).html((( (data.attended == 1) ? 'Attended': 'Not Attended' ))));

                    $('td', row).eq('9').html($('<span>').attr({
                        class:  ((data.delayed == 1) ? 'text-warning': 'text-success' )
                    }).html((( (data.delayed == 1) ? 'Delayed': 'Not Delayed' ))));

                    $('td', row).eq('11').html($('<span>').attr({
                        class:  ((data.urgent == 1) ? 'text-warning': 'text-success' )
                    }).html((( (data.urgent == 1) ? 'Urgent': 'Not Urgent' ))));

                }
            });
        });

And Finally the Controller:

$complete = Maintenance::with('user')->with('department')->with('maintenancedetails')->with('area')->with('maintenancedepartment')->where('maintenancedepartment_id', $auth->maintenancedepartment_id)->where('status', 1);
            return datatables()->of($complete)
                ->addColumn('created_date', function ($completed) {
                    $parse = $completed->created_at;
                    $parsed = Carbon::parse($parse);
                    return $parsed->toFormattedDateString();
                })
                ->addColumn('created_time', function ($completed) {
                    $parse = $completed->created_at;
                    $parsed = Carbon::parse($parse);
                    return $parsed->toTimeString();
                })
                ->addColumn('view', function ($completed) {
                    return '<a href="'.route('manage.edit', $completed->id).'"><i class="fa fa-wrench text-info"></i></a>';
                })->rawColumns(['view', 'action'])
                ->toJson();

I am Using "with()" instead of Join. I have seen some of the issues reported with almost the same error but their methods did not work for me. One mentioned to use data: area.name but I am already using this.

[yajra]

Set the name along with data.

{data: 'area.name', name: 'area.name'},

[gwachhamit]

what about the many-to-many relation data? How can we do it with
{data: 'area.name', name: 'area.name'},

[tsulatsitamim]

I have error too, even I've add name property. But the error only happend if related table has underscore, it works fine when i order customer column, but error when order by sample_type column

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sample_type.name' in 'order clause' (SQL: select * from `lims_orders` order by `sample_type`.`name` asc limit 10 offset 0)

Oder model:

    public function customer()
    {
        return $this->belongsTo(Customer::class);
    }

    public function sampleType()
    {
        return $this->belongsTo(SampleType::class);
    }

Column definition:

    {
      title: 'Jenis Sample',
      data: 'sample_type.name',
      name: 'sample_type.name',
    },
    {
      title: 'Pelanggan',
      data: 'customer.name',
      name: 'customer.name',
    },

Controller:

    public function index()
    {
        $query = Order::with(['customer', 'sampleType']);

        return datatables()->eloquent($query)->toJson();
    }

[importjeremy]

@tsulatsitamim I ran into that too, for your related table you might have to use:
{data: 'relation_name.column', name: 'relationName.column'}
See here