I have a couple of schemas like so
Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('userName')->default('');
$table->string('userEmail')->default('');
$table->tinyInteger('active')->default(1);
$table->integer('departmentId')->unsigned()->default(0);
$table->foreign('departmentId')->references('departmentId')->on('departments')->onDelete('cascade');
$table->timestamps();
});
Schema::create('departments', function (Blueprint $table) {
$table->primary('departmentId');
$table->integer('departmentId')->unsigned()->default(0);
$table->string('departmentName')->default('');
$table->tinyInteger('active')->default(1);
$table->timestamps();
});
So a User belongs to a Department, and a Department can have many Users.
In one of my controllers, I use to do the following to get a list of users for a specific department
$users = User::select('userName', 'id')->where('departmentId', 2)->get();
Because I am obtaining departments from Active Directory, I have since discovered that this is not a good way because the ids for the departments change. As such, I now need to get the users via the departmentName instead. At the moment I am trying something like this
$test = DB::table('users')
->join('departments', 'users.departmentId', '=', 'departmentId')
->select('users.userName', 'users.id')
->get();
However, this returns the following error
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'departmentId' in on clause is ambiguous (SQL: select `users`.`userName`, `users`.`id` from `users` inner join `departments` on `users`.`departmentId` = `departmentId`)
How would it be possible to get all users for a particular departmentName? So say I wanted to get all users who belong to the Technology department, how could I go about doing this?
Thanks
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire