lundi 22 février 2016

Laravel 5 - Performing a join with database tables

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