Laravel eloquent datatables SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sector.location.location_name' in 'where clause'

Question

I'm doing a project with Laravel and Datatables. I'm using eloquent. Datatables populates table correctly but when I do (datatables) search I receive error:

Next Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sector.location.location_name' in 'where clause' ...

Can you please recommend a solution to this?

Tables structure:

--change_logs table

id                
username          
sector_id         
operation_id     
layer_category_id 
object_name

--sectors table

id         
sector_name 
location_id

--locations table

id
location_name

In datatables Ajax I use

{data: 'sector.location.location_name', name: 'sector.location.location_name'}, and I receive error "Column not found: 1054 Unknown column 'sector.location.location_name' in 'where clause'"

If I use name of the tables and not the objects like this:

{data: 'sector.location.location_name', name: '**sectors.locations.location_name**'},

I still receive an error:

Column not found: 1054 Unknown column 'sectors.locations.location_name' in 'where clause'

Data is loaded correctly in datatables table but search is not working

//changelogs.blade.php

     var table = $('.data-table').DataTable({
            processing: true,
            serverSide: true,
            ajax: "{{ route('change_logs.index') }}",
            columns: [
                //{data: 'DT_RowIndex', name: 'DT_RowIndex'},
                {data: 'id', name: 'change_logs.id'},
                {data: 'user.name', name: 'user.name'},
                {data: 'user.username', name: 'user.username'},
                {data: 'sector.location.location_name', name: 'sector.location.location_name'},
                {data: 'sector.sector_name', name: 'sector.sector_name'},
                {data: 'layer_category.layer.layer_name', name: 'layer_category.layer.layer_name'},
                {data: 'layer_category.category_name', name: 'layer_category.category_name'},
                {data: 'object_name', name: 'object_name'},
                {data: 'operation.operation_name', name: 'operation.operation_name'},
                {data: 'action', name: 'action', orderable: false, searchable: false},
            ]
        });




//controller

    $data = ChangeLog::with('user','sector','operation','layer_category' )->select('change_logs.*');
    return Datatables::of($data)


//class ChangeLog
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class ChangeLog extends Model
{
      protected $fillable = [
        'username', 'sector_id','operation_id', 'layer_category_id', 'object_name'
    ];

    protected $hidden = [
    ];  

    public function location()
    {
        return $this->belongsTo('App\Location');
    }

     public function user()
    {
        //return $this->belongsTo('App\User');
        return $this->belongsTo('App\User', 'username', 'username');

    }

    public function sector()
    {
        return $this->belongsTo('App\Sector')->with('location');
    }

    public function operation()
    {
        return $this->belongsTo('App\Operation');
    }

    public function layer_category()
    {
        return $this->belongsTo('App\LayerCategory')->with('layer');
    }


}

//class Sector
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Sector extends Model
{
    protected $fillable = [
        'sector_name','location_id',
    ];

    protected $hidden = [

    ];
public function location()
    {
        return $this->belongsTo('App\Location');
    }
}

//class Location
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Location extends Model
{
    protected $fillable = [
        'location_name',
    ];

    protected $hidden = [
    ];

public function sectors()
    {
        return $this->hasMany('App\Sector');
    }
}

zonecc zonecc · Accepted Answer · 2019-09-11T20:22:07

Have you defined a relationship that maps the locations to the sector? From your table structure, a location can have multiple sectors thats why we have the location_id in the sectors. So it is expected you define this relationship in the respective models.

app\Sector.php

...

public function location() {
    return $this->belongsTo('App\Location');
}

app\Location.php

...

public function location() {
    return $this->hasMany('App\Sector');
}

Laravel eloquent datatables SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sector.location.location_name' in 'where clause'

1 Answers