sqlstate[42s22]:找不到列:1054包含datatables的未知列

plupiseo  于 2021-06-25  发布在  Mysql
关注(0)|答案(3)|浏览(335)

我用的是拉维5.3
我有2个表(users和jadwals),当我想用datatables显示表jadwals时,我得到以下错误消息:

sqlstate[42s22]:未找到列:“on子句”中的1054未知列“jadwals.users\u id”(sql:select*from jadwals left join users on jadwals.users\u id=users.id order by users.name asc limit 10 offset 0)

表用户

Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->bigInteger('nimnip')->unique();
        $table->string('email')->unique();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();

表jadwals

Schema::create('jadwals', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('user_id')->unsigned();
        $table->string('kegiatan');
        $table->integer('ruangan_id')->unsigned();
        $table->integer('jam_mulai_id')->unsigned();
        $table->integer('jam_selesai_id')->unsigned();
        $table->timestamps();

        $table->foreign('user_id')->references('id')->on('users')->onUpdate('cascade')->onDelete('cascade');
        $table->foreign('ruangan_id')->references('id')->on('ruangans')->onUpdate('cascade')->onDelete('cascade');
        $table->foreign('jam_mulai_id')->references('id')->on('jams')->onUpdate('cascade')->onDelete('cascade');
        $table->foreign('jam_selesai_id')->references('id')->on('jams')->onUpdate('cascade')->onDelete('cascade');

模型用户

/**
 * The attributes that are mass assignable.
 *
 * @var array
 */
protected $fillable = [
    'name', 'nimnip', 'email', 'password',
];

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

/**
 * The attributes that should be hidden for arrays.
 *
 * @var array
 */
protected $hidden = [
    'password', 'remember_token',
];

jadwal型号

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

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

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

Jadwals控制器

class JadwalsController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request, Builder $htmlBuilder)
    {
        if($request->ajax())
        {
            $jadwals = Jadwal::with('users', 'ruangans', 'jams');
            return Datatables::of($jadwals)
                ->addColumn('action', function($jadwal){
                    return view('datatable._action', [
                        'model'             =>  $jadwal,
                        'form_url'          =>  route('jadwals.destroy', $jadwal->id),
                        'edit_url'          =>  route('jadwals.edit', $jadwal->id),
                        'confirm_message'   =>  'yakin mau menghapus ' . $jadwal->kegiatan . '?'
                        ]);
                })->make(true);
        }

        $html = $htmlBuilder
            ->addColumn(['data' => 'users.name',        'name' => 'users.name',         'title' => 'Nama Peminjam'])
            ->addColumn(['data' => 'kegiatan',          'name' => 'kegiatan',           'title' => 'Kegiatan'])
            ->addColumn(['data' => 'ruangans.ruangan',  'name' => 'ruangan.ruangan',   'title' => 'Ruangan'])
            ->addColumn(['data' => 'jams.jam',          'name' => 'jam.jam',           'title' => 'Jam Mulai'])
            ->addColumn(['data' => 'jams.jam',          'name' => 'jam.jam',           'title' => 'Jam Selesai'])
            ->addColumn(['data' => 'action', 'name' => 'action', 'title'=>'', 'orderable'=>false, 'searchable'=>false]);

            return view('jadwals.index')->with(compact('html'));
    }

这里有什么问题?

4zcjmb1e

4zcjmb1e1#

您的一组用户的名称为“users”。laravel搜索具有users\u id name的列,因为表名是“users”。更改此函数:jawals模型上的用户和用户模型上的jadwals。使用:
关于用户模型:

public function jadwals()
{
    return $this->hasMany('App\Jadwal', 'user_id', 'id');
}

关于jadwals模型:

public function users()
{
    return $this->belongsTo('App\User', 'user_id', 'id');
}
8ljdwjyq

8ljdwjyq2#

如前所述,jadwals表没有列用户,它有列用户。所以这应该行得通:

SELECT * FROM jadwals 
LEFT JOIN users 
  ON jadwals.user_id = users.id 
ORDER BY users.name ASC 
LIMIT 10 
OFFSET  0
gajydyqb

gajydyqb3#

这些行表示表包含列名 user_id ```
$table->integer('user_id')->unsigned();
^^^^^^^

$table->foreign('user_id')->references('id')->on('users')->onUpdate('cascade')->onDelete('cascade');
^^^^^^^

正在执行的sql语句正在引用列名 `users_id` 表定义中未显示的:

select *
from jadwals
left
join users
on jadwals.users_id = users.id
^^^^^^^^
order by users.name asc

相关问题