jinsonjose
1 month ago
288
5
Laravel

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous

Posted 1 month ago by jinsonjose

how to solve this errror query below sort and search have this occure this error

public function index()
{
    $search  = request('search_text');
    if ($search != NULL ) {
        // check any search word match 
        $users = User::where('name', 'LIKE', '%' . $search . '%')
                    ->orWhere('user_name', 'LIKE', '%' . $search . '%')
                    ->orWhere('email', 'LIKE', '%' . $search . '%')
                    // check roles (belongs relation to roles table) name in roles table
                    ->orWhereHas('roles', function (Builder $query) use($search) {
                        $query->where('name', 'LIKE', '%' . $search . '%');
                    })
                    // check base_types (belongs relation to base_types table) name in base_types table
                    ->orWhereHas('base_types', function (Builder $query) use($search) {
                        $query->where('name', 'LIKE', '%' . $search . '%');
                    })
                    ->sortable('name')
                    ->paginate(15);
                    
    } else {
        $users = User::sortable('name')->paginate(15);
    }
    return view('admin.users.index',compact('users', 'search'));
}

error message

Illuminate\Database\QueryException SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous (SQL: select count(*) as aggregate from users left join base_types on users.base_type = base_types.id where (name LIKE %uk% or user_name LIKE %uk% or email LIKE %uk% or exists (select * from roles where users.role_id = roles.id and name LIKE %uk%) or exists (select * from base_types where users.base_type = base_types.id and name LIKE %uk%)) and users.deleted_at is null)

                                <th>@sortablelink('name', 'Name')</th>
                                <th>@sortablelink('user_name', 'User Name')</th>
                                <th>@sortablelink('email', 'Email')</th>
                                <th>@sortablelink('roles.name', 'Role')</th>
                                <th>@sortablelink('base_types.name', 'Base Type')</th>
                                <th>@sortablelink('status', 'Status')</th>
                                <th>@sortablelink('updated_at', 'Modified')</th>
                                <th>@sortablelink('', 'Actions')</th>

in model relation

/**
 * get role name from Role Table
 * based on role_id
 */
public function roles()
{
    return $this->belongsTo('App\Role','role_id');
}

/**
 * get base type name from BaseType Table
 * based on base_type
 */
public function base_types()
{
    return $this->belongsTo('App\BaseType','base_type');
}

Please sign in or create an account to participate in this conversation.