Laravel eloquent search functionality

Question

I'm currently creating a search functionality on my index blade file.

I have different users that have different stores per area.

my User model:

    function area() {
    return $this->hasOne('App\Area');        
}

function role() {
    return $this->belongsTo('App\Role');
}

function reports() {
    return $this->hasMany('App\Report');
}

function stores() {
    return $this->hasManyThrough('App\Store', 'App\Area');
}

}

Area model:

   function reports() {
    return $this->hasMany('App\Report');
}

function stores() {
    return $this->hasMany('App\Store');
}

 function user() {
    return $this->belongsTo('App\User');
}

Store model:

    function district() {
    return $this->belongsTo('App\District');
}


function cluster() {
    return $this->belongsTo('App\Cluster');
}
function city() {
    return $this->belongsTo('App\City');
}

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

I have managed to make the functionality work on admin account (where in you can see all stores) by:

function index(Request $request) {
    if($request->has('search')) {
        $stores = Store::whereHas('city', function($query) use($request) {
        $query->where('name', 'like', '%' . $request->search . '%');
    })->orWhereHas('cluster', function($query) use($request) {
        $query->where('name', 'like', '%' .$request->search. '%');
    })->orWhere('name', 'like', '%' .$request->search. '%')
      ->orWhere('store_no', 'like', '%' .$request->search. '%')->paginate(10);  

    } else {
        $stores = Store::orderBy('created_at', 'desc')->paginate(10);
    }

    return view('/stores.store_list', compact('stores'));

what I want to do on user accounts is:

function index(Request $request) {
    if($request->has('search')) {
        $id = Auth::user()->id;
        $query = $request->search;
        $user = User::find($id)->stores()->where('name', 'like', '%' . $query . '%')->paginate(10);


    } else {

        $id = Auth::user()->id;
       $user = User::find($id)->stores()->orderBy('created_at', 'desc')->paginate(10);
    }

    return view('/stores.store_list', compact('user'));

}

I'm having an error when searching:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous (SQL: select count(*) as aggregate from stores inner join areas on areas.id = stores.area_id where areas.user_id = 6 and name like %houston% and stores.deleted_at is null)

can anyone point me on the right direction and let me know what i'm missing here? Thank you!

Answer 1

When you have error such is

Column 'some_column_name_here' in where clause is ambiguous...

it means that there is complex query associated with more tables of which at least two those have same table name in their structure and that table name is used in complex query (in some ON, WHERE...) in this case WHERE as error states. You always can use table_name.column_name {table name dot column name} syntax to point syntax at exact table meant (ambiguous).