SQLSTATE[42S22]：未找到列：'where子句‘中的1054个未知列'sector.location.location_name’

Question

我在和Laravel和Datatable一起做一个项目。我用的是口才。数据表填充正确，但当我进行(数据存储)搜索时，我会收到错误：

下一步说明\数据库\QueryException: SQLSTATE42S22:列未找到: 1054未知列'sector.location.location_name‘在'where子句’.

你能给我推荐一个解决办法吗？

表格结构：

--更改日志表

id                
username          
sector_id         
operation_id     
layer_category_id 
object_name

-扇区表

id         
sector_name 
location_id

-地点表

id
location_name

在可数据的Ajax中，我使用

{数据：‘sector.location.Locationname’，名称：'sector.location.location_name'}，和I接收错误“列未找到：'where子句‘中的1054个未知列'sector.location.location_name’”

如果我使用表的名称而不是像这样的对象：

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

我仍然收到一个错误：

列未找到：'where子句‘中的1054个未知列'sectors.locations.location_name’

数据在可数据表中正确加载，但搜索不起作用。

//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');
    }
}

Answer 1

您是否定义了将位置映射到扇区的关系？从您的表结构中，一个位置可以有多个扇区，这就是为什么我们在扇区中有location_id。因此，您应该在各自的模型中定义这种关系。

app\Sector.php

...

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

app\Location.php

...

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