CODEWITHSUNDEEP
phplaraveleloquent
Lijesh Shakya
Lijesh Shakya

Reputation: 2540

Laravel Eloquent: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous

I am building a website where many members can join many groups.

//User.php
public function groups()
    {
        return $this->belongsToMany(Group::class, 'group_member', 'member_id', 'group_id');
    }

The function to retrieve related members from the group id

//UserRepository.php
public function getRelatedMembersByGroupIds(array $groupIds)
    {
        $members = User::whereHas('groups', function ($query) use ($groupIds) {
            return $query->whereIn('groups.id', $groupIds);
        });
        return $members->get();
    }

The repository is being called from the UserController

//UserController.php
public function getRelatedMembersByGroupIds(Request $request)
    {
        $groupIds = $request->get('group_ids');

        $members = $this->userRepository->getRelatedMembersByGroupIds($groupIds);

        $responses = [
            'status' => 'success',
            'groupRelatedMembers' => $members
        ];

        return response()->json($responses);
    }

It returns

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select users.* from users where exists (select * from groups inner join group_member on groups.id = group_member.group_id where users.id = group_member.member_id and groups.id in (1) and groups.deleted_at is null and id = 1))

Upvotes: 7

Views: 12678

Answers (5)

Hassan Shahzad Aheer
Hassan Shahzad Aheer

Reputation: 75

I had an issue with the multi-tenant scope this change works for me.

change from this

$builder->where('tenant_id', session()->get('tenant_id'));

to this

$builder->where($model->getTable() . '.tenant_id', session()->get('tenant_id'));



<?php

namespace App\Scopes;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Scope;
use Illuminate\Notifications\Notifiable;

class TenantScope implements Scope
{
    /**
     * Apply the scope to a given Eloquent query builder.
     * @param  \Illuminate\Database\Eloquent\Builder  $builder
     * @param  \Illuminate\Database\Eloquent\Model  $model
     * @return void
     */
    use Notifiable;

    public function apply(Builder $builder, Model $model)
    {
        if (session()->has('tenant_id')) {
            $builder->where($model->getTable() . '.tenant_id', session()->get('tenant_id'));
        }
    }
}

thank you all so much.

Upvotes: 0

Ammar Alrefai
Ammar Alrefai

Reputation: 278

In my case, it was due to multi-tenant TenantScope scope.

The fix was to prefix the field with the table name - which was generic in my case - as the scope is applied to all models.

My solution was to use $model->getTable() as below:

<?php

namespace App\Scopes;

use App\Helpers\AuthenticationHelper;
use Illuminate\Database\Eloquent\Scope;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;

class GroupScope implements Scope
{
    /**
     * Apply the scope to a given Eloquent query builder.
     *
     * @param \Illuminate\Database\Eloquent\Builder $builder
     * @param \Illuminate\Database\Eloquent\Model $model
     * @return void
     */
    public function apply(Builder $builder, Model $model)
    {
        if (AuthenticationHelper::isEmployee())
        {
            //Problem was I did not added groups table below.
            $builder->where($model->getTable() . '.id', auth()->user()->branch_id);
        }
    }
}

Upvotes: 5

Lijesh Shakya
Lijesh Shakya

Reputation: 2540

I forgot I had implemented a global scope for Group model. After adding groups.id in the GroupScope, It worked fine.

<?php

namespace App\Scopes;

use App\Helpers\AuthenticationHelper;
use Illuminate\Database\Eloquent\Scope;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;

class GroupScope implements Scope
{
    /**
     * Apply the scope to a given Eloquent query builder.
     *
     * @param \Illuminate\Database\Eloquent\Builder $builder
     * @param \Illuminate\Database\Eloquent\Model $model
     * @return void
     */
    public function apply(Builder $builder, Model $model)
    {
        if (AuthenticationHelper::isEmployee())
        {
            //Problem was I did not added groups table below.
            $builder->where('groups.id', auth()->user()->branch_id);
        }
    }
}

Upvotes: 6

ManojKiran
ManojKiran

Reputation: 6341

i am not daam sure but it may work

Your Code

public function getRelatedMembersByGroupIds(array $groupIds)
    {
        $members = User::whereHas('groups', function ($query) use ($groupIds) {
            return $query->whereIn('groups.id', $groupIds);
        });
        return $members->get();
    }

Can You dd($members) and share the result so i will dig deep into the issue

Upvotes: 6

Ravindu_Dilshan
Ravindu_Dilshan

Reputation: 171

In last where clause, there is "id=1". You need to express it as "table_name.id=1".

Upvotes: 9

Related Questions