[FIXED] Problems with Where Condition in Pivot Table Laravel 8

Issue

Hello I am working on a project, that in this part, to get a dependent dropdown i want to get all the «Areas de Interesse» that are attached to a specific user, getting this, by his ID, that is in the pivot table.

These are my migrations:

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->foreignId('current_team_id')->nullable();
    $table->string('profile_photo_path', 2048)->nullable();
    $table->timestamps();
});

Schema::create('area_interesse', function (Blueprint $table) {
    $table->id();
    $table->string('area_interesse');
    $table->string('area_interesse_ing');
    $table->timestamps();
});

Schema::create('utilizador_interesse', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger("id_utilizador");
    $table->foreign('id_utilizador')->references('id')->on('users')->onDelete('cascade');;
    $table->unsignedBigInteger("id_interesse");
    $table->foreign('id_interesse')->references('id')->on('area_interesse');
    $table->timestamps();
});

And my models:

class AreaInteresse extends Model
{
    protected $table = "area_interesse";

    public function users(){
            return $this->belongsToMany(User::class, 'utilizador_interesse', 'id_interesse' , 'id_utilizador');
        
    }
}

class User extends Authenticatable
{
    public function interesses(){
        return $this->belongsToMany(AreaInteresse::class, 'utilizador_interesse', 'id_utilizador', 'id_interesse');
    }
}

In my controller i tried to do this, base on every example i saw, has for testing, i am trying to get all «Areas de Interesse» that are related to the user with id 11

$interesses = AreaInteresse::with('users')
    ->whereHas('users', function($q){
        $q->wherePivot('id_utilizador', 11); 
    })
    ->get();

However i am getting this error and i have no idea why it's happening, and why it is assuming i am trying to call a column «pivot»

Illuminate\Database\QueryException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pivot' in 'where clause' (SQL: select * from `area_interesse` where exists (select * from `users` inner join `utilizador_interesse` on `users`.`id` = `utilizador_interesse`.`id_utilizador` where `area_interesse`.`id` = `utilizador_interesse`.`id_interesse` and `pivot` = id_utilizador))

Solution

"I am trying to get all «Areas de Interesse» that are related to the user with id 11"

This is exactly why relationships are defined between models. Just instantiate the user and then get the related models:

$user = User::findOrFail(11);
$interesses = $user->interesses;

As mentioned in comments, and worth repeating here, you should call your class Utilizadore instead of User, or change table and column names to match the name of the class.

Answered By - miken32