mysql - 如果使用两个表，列搜索在数据表中不起作用

Question

我有两个表连接表 1 和表 2，我使用的数据表工作正常，但搜索列无法正常工作

"Exception Message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pengelolaan_surat.nama_status' in 'where clause' (SQL: select count(*) as aggregate from (select '1' as row_count from pengelolaan_surat inner join status_surat on pengelolaan_surat.id_status = status_surat.id_status where (@rownum + 1 like %j% or LOWER(pengelolaan_surat.no_surat) LIKE %j% or LOWER(pengelolaan_surat.judul_surat) LIKE %j% or LOWER(pengelolaan_surat.tgl_masuk) LIKE %j% or LOWER(pengelolaan_surat.nama_status) LIKE %j% or LOWER(pengelolaan_surat.tgl_dispo) LIKE %j% or LOWER(pengelolaan_surat.pelaksana) LIKE %j%)) count_row_table)"

这是我的数据表

public function data(Request $req)
    {
        DB::statement(DB::raw('set @rownum=0'));
        $pengelolaan = PengelolaanSurat::select([
            DB::raw('@rownum  := @rownum  + 1 AS rownum'),
            'pengelolaan_surat.id_surat', 
            'pengelolaan_surat.no_surat',
            'pengelolaan_surat.judul_surat',
            'pengelolaan_surat.tgl_masuk',
            'pengelolaan_surat.id_status',
            'pengelolaan_surat.tgl_dispo',
            'pengelolaan_surat.pelaksana',
            'status_surat.nama_status'
        ])->join('status_surat', 'pengelolaan_surat.id_status', '=', 'status_surat.id_status');

        $pengelolaantables = Datatables::of($pengelolaan)
            ->addColumn('action', function ($pengelolaans) {
                return '
                <a href="'. route("pengelolaan-surat.view", $pengelolaans->id_surat) .'"><i class="fa fa-eye"></i></a>
                <a href="'. route("pengelolaan-surat.edit", $pengelolaans->id_surat) .'"><i class="fa fa-edit"></i></a> 
                <a href="'. route("pengelolaan-surat.delete", $pengelolaans->id_surat) .'"><i class="fa fa-trash"></i></a>';
            })
            ->editColumn('id_surat', '{{$id_surat}}');

        if ($keyword = $req->get('search')['value']) {
            $pengelolaantables->filterColumn('rownum', 'whereRaw', '@rownum  + 1 like ?', ["%{$keyword}%"]);
        }

        return $pengelolaantables->make(true);
    }

如何解决这个问题？

Answer 1

您的 nama_status 表格似乎有误。您应该使用:

status_surat.nama_status

相反