CODEWITHSUNDEEP
phpmysqldatabaselaraveleloquent
Q8root
Q8root

Reputation: 1355

Using whereHas eloquent relationship query

I have the following tables : Users

Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('username');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
            $table->integer('role_id')->unsigned();
            $table->foreign('role_id')->references('id')->on('roles');

Areas

Schema::create('areas', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->integer('location_id')->unsigned();
            $table->foreign('location_id')->references('id')->on('locations')->onDelete('cascade');
            $table->timestamps();

area_user 

Schema::create('area_user', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->unsigned();
            $table->foreign('user_id')->references('id')->on('users');
            $table->integer('area_id')->unsigned();
            $table->foreign('area_id')->references('id')->on('areas');
        });

Buildings

Schema::create('buildings', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('symbol')->nullable();
            $table->integer('area_id')->unsigned();
            $table->foreign('area_id')->references('id')->on('areas')->onDelete('cascade');
            $table->integer('building_type')->unsigned();
            $table->foreign('building_type')->references('id')->on('building_types');
            $table->timestamps();
        });

User Model:

public function areas()
    {
        return $this->belongsToMany('App\Area');
    }

Area Model:

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

Building Model:

public function area(){
       return $this->belongsTo('App\Area','area_id');
    }

How can i write a query in User model that return all Buildings assigned to a user through his assigned areas (area_user table) I tried this , but it returning an error 

Building::whereHas('area', function ($q) {
            $q->whereHas('users', function ($q) {
                $q->where('id', auth()->id());
            });
        })->get();

Error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select * from `buildings` where exists (select * from `areas` where `buildings`.`area_id` = `areas`.`id` and exists (select * from `users` inner join `area_user` on `users`.`id` = `area_user`.`user_id` where `areas`.`id` = `area_user`.`area_id` and `id` = 11))) (View: C:\xampp\htdocs\hse\resources\views\observations\form_observation.blade.php) (View: C:\xampp\htdocs\hse\resources\views\observations\form_observation.blade.php)

Upvotes: 2

Views: 1123

Answers (1)

Mathew Tinsley
Mathew Tinsley

Reputation: 6976

You can resolve the ambiguity by specifying the table in your where clause:

Change

$q->where('id', auth()->id());

To

$q->where('users.id', auth()->id());

Upvotes: 4

Related Questions