Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have 3 tables, i.e. User, Learner and Teacher tables joined together
User table
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('last_name');
$table->string('email')->unique()->nullable();
$table->timestamp('email_verified_at')->nullable();
$table->string('phone')->unique()->nullable();
$table->string('types');
$table->string('avatar')->default('default.jpg');
$table->string('password');
$table->rememberToken();
$table->timestamps();
});
Learner Table
Schema::create('learners', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('user_id');
$table->string('gender')->nullable();
$table->string('identity')->nullable();
$table->string('school')->nullable();
$table->string('grade')->nullable();
$table->string('subjects')->nullable();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->timestamps();
});
Teacher Table
Schema::create('teachers', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('user_id');
$table->string('gender')->nullable();
$table->string('identity')->nullable();
$table->string('classes')->nullable();
$table->string('suburb')->nullable();
$table->string('city')->nullable();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->timestamps();
});
and here is my Show method in the UserController
public function show($id)
{
$userId = User::find($id);
if($userId->types == 'Learner')
{
$user = DB::table('users')
->join('learners', 'users.id', '=', 'learners.user_id')
->select('users.*', 'learners.*')
->where('users.id', '=', $id)
->first();
return view('backend.user.show.learner', compact('user'));
}
else {
$user = DB::table('users')
->join('teachers', 'users.id', '=', 'teachers.user_id')
->select('users.*', 'teachers.*')
->where('users.id', '=', $id)
->first();
return view('backend.user.show.teacher', compact('user'));
}
}
why am I getting the wrong Id but other information is correct?... what am I doing wrong?
{#1280 ▼
+"id": 2
+"name": "Bhubhu"
+"last_name": "Bali"
+"email": "[email protected]"
+"email_verified_at": null
+"phone": "1234566677"
+"types": "Learner"
+"avatar": "default.jpg"
+"password": "y$n4ykqDKXcVTVVw9NaThd8uHuZISEltOwSN5IzIqqJ8dOOk90nQape"
+"remember_token": null
+"created_at": "2020-10-01 17:06:24"
+"updated_at": "2020-10-01 17:06:24"
+"user_id": 3
+"gender": null
+"identity": null
+"street": null
+"suburb": null
+"city": null
+"province": null
+"code": null
+"school": null
+"grade": null
+"subjects": null
}
the id: 2 but user_id: 3... and when I run the edit method it brings the information of id 2
You have an id column on each table, so the id property of the User is being overridden by the id from the joined table. Given how you have designed your schema, your learners and teachers table actually do not need an id primary key, so you could drop that column. Otherwise, be explicit about the selected columns from the learners and teachers tables whenever you join, by aliasing the id column or excluding id column
@vusumzi if you have the following Eloquent relationships set up in your User model.
class User
{
public function teacher()
{
return $this->hasOne(Teacher::class);
}
public function learner()
{
return $this->hasOne(Learner::class)
}
}
then you can reduce your controller method to:
public function show($id)
{
$user = User::find($id);
if ($user->types == 'Learner') {
$learner = $user->learner()->get();
return view('backend.user.show.learner', compact('learner'));
};
$teacher = $user->teacher()->get();
return view('backend.user.show.teacher', compact('teacher'));
}
or even:
public function show($id)
{
$user = User::find($id);
if ($user->types == 'Learner') {
return view('backend.user.show.learner', compact('user'));
};
return view('backend.user.show.teacher', compact('user'));
}
in your blade, you can reference parts of the learner object chaining the relationship and then you can get any fields on the related model.
eg
{{ $user->learner->school }}
or
{{ $user->teacher->city }}
Yes... but I keep on getting the wrong Id, but everything else is correct
The id is selected from both tables whenever you join - you are getting the id of the joined table because the data effectively has two id keys (not in reality - the second id overwrites the first id).
If you are explicitly excluding the joined id then the correct users.id will be seeing the expected result:
$user = DB::table('users')
->join('learners', 'users.id', '=', 'learners.user_id')
->selectRaw('users.*, learners.gender, learners.identity, learners.school, learners.grade, learners.subjects')
->where('users.id', '=', $id)
->first();
Now there is no collision with the attribute keys.
@vusumzi although not essential, its worth getting familiar with Eloquent as it makes get data out of Laravel much easier without having to do raw DB joins.
Please or to participate in this conversation.